Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

After crash:

mysql> repair table lot_not_sold;
+------------------------+--------+----------+---------------------------------------------------------------+
| Table                  | Op     | Msg_type | Msg_text                                                      |
+------------------------+--------+----------+---------------------------------------------------------------+
| coinprice.lot_not_sold | repair | info     | Found block with too small length at 126188; Skipped          |
| coinprice.lot_not_sold | repair | info     | Wrong block with wrong total length starting at 147688        |
| coinprice.lot_not_sold | repair | info     | Wrong bytesec:   4-253-223 at 9784004; Skipped                |
| coinprice.lot_not_sold | repair | info     | Wrong bytesec:  48- 32-208 at 11538216; Skipped               |
| coinprice.lot_not_sold | repair | info     | Delete link points outside datafile at 4036632                |
| coinprice.lot_not_sold | repair | info     | Wrong bytesec:   0-  0-  0 at 1405636; Skipped                |
.... rows were skipped a lot ....
| coinprice.lot_not_sold | repair | warning  | Number of rows changed from 113619 to 115277                  |
| coinprice.lot_not_sold | repair | status   | OK                                                            |
+------------------------+--------+----------+---------------------------------------------------------------+
616 rows in set (1.77 sec)

More output here, however I had to skip a lot of lines too due to size limit.

SELECT * FROM coinprice.lot_not_sold  ORDER BY site DESC limit 2;
+------------+----------------------------+---------------------+--------+------+------------+------------+------+
| id         | fulltitle                  | added               | finish | site | startprice | blitzprice | url  |
+------------+----------------------------+---------------------+--------+------+------------+------------+------+
| 2924913561 | tttt 10 tttttttt 2001 ttttt| ▒338-14-08 91:97:60 | NULL   |   78 |      0.000 |      4.379 | NULL |
| 2926483072 | Zamek w Krniku - 2z - 1998 | 2013-01-06 06:40:01 | NULL   |    5 |      2.839 |      0.000 | NULL |
+------------+----------------------------+---------------------+--------+------+------------+------------+------+
2 rows in set (0.00 sec)

Please note added is obviously incorrect for the first row.

mysql> delete from lot_not_sold where site>5;
ERROR 1032 (HY000): Can't find record in 'lot_not_sold'

Try to check:

mysql> check table lot_not_sold extended;
+------------------------+-------+----------+----------+
| Table                  | Op    | Msg_type | Msg_text |
+------------------------+-------+----------+----------+
| coinprice.lot_not_sold | check | status   | OK       |
+------------------------+-------+----------+----------+
1 row in set (2.95 sec)

Next try to select again, delete, repair - and always have the same result.

mysql> show create table lot_not_sold;

 CREATE TABLE `lot_not_sold` (
  `id` int(11) unsigned NOT NULL,
  `fulltitle` varchar(120) NOT NULL,
  `added` datetime NOT NULL,
  `finish` datetime DEFAULT NULL COMMENT 'дата закрытия торгов',
  `site` tinyint(4) NOT NULL,
  `startprice` float(8,3) unsigned NOT NULL,
  `blitzprice` float(8,3) unsigned DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`site`,`added`),
  KEY `IX_lot_not_sold_added` (`added`),
  KEY `IX_lot_not_sold_finish` (`finish`),
  KEY `IX_lot_not_sold_site` (`site`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=114
/*!50100 PARTITION BY HASH (DAY(added))
PARTITIONS 4 */ 

Is it possible to fix this error? Is is possible there are way many errors because repair added ~2k rows?

I googled sone pages mentioned very old bug but my instance is not too old:

mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1

MyISAM was choosen because of performance reason. Now I'm considering switching to InnoDB - is it the right way? This table constantly modified.

Thank you.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

You can try to use the REPAIR TABLE myTable USE_FRM

"Probably" your .MYI file is missing or have corrupted headers

This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file

However before proceed check the documentation of your MySQL 5.1 version and make a backup copy of your .MYI,.frm, and .MYD file in order to avoid loss of data

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.