| Bug #11199 | ERROR 1034 (HY000): Incorrect key file for table 'blah'; try to repair it | ||
|---|---|---|---|
| Submitted: | 9 Jun 2005 11:59 | Modified: | 9 Jul 2005 19:26 |
| Reporter: | Adam Reynolds | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 4.1.12-standard | OS: | Linux (RedHat Enterprise 3.4) |
| Assigned to: | CPU Architecture: | Any | |
[9 Jun 2005 16:33]
Adam Reynolds
I managed to work around this problem by breating a new table using the create table like syntax, and then filling that table using the insert into table setect * from syntax. I then stopped the database, moved the offending tables files out of the way (but I still have them) restarted mysql and renemaed the new table back to the original name.
[9 Jun 2005 19:25]
Jorge del Conde
Hi! I was able to insert, update, select and delete from that table structure without problems. Is there any way you can reproduce the table corruption ? Thanks!
[9 Jul 2005 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[3 May 2009 16:55]
yogesh tiwari
Hi, Me too got the same type of error while DELETEing records from database. Snap cop: mysql> DELETE FROM FIN_SALES_REPORT_DETAILS WHERE SALES_REPORT_ID IN (SELECT SALES_REPORT_ID FROM FIN_SALES_REPORT WHERE MASTER_LICENSE_ID = 476757527); ERROR 1034 (HY000): Incorrect key file for table 'FIN_SALES_REPORT_DETAILS'; try to repair it mysql> DELETE FROM FIN_SALES_REPORT_DETAILS WHERE SALES_REPORT_ID IN (SELECT SALES_REPORT_ID FROM FIN_SALES_REPORT WHERE MASTER_LICENSE_ID=476757527); ERROR 1034 (HY000): Incorrect key file for table 'FIN_SALES_REPORT_DETAILS'; try to repair it mysql> SELECT COUNT(*) FROM FIN_SALES_REPORT_DETAILS WHERE SALES_REPORT_ID IN (SELECT SALES_REPORT_ID FROM FIN_SALES_REPORT WHERE MASTER_LICENSE_ID = 476757527); +----------+ | COUNT(*) | +----------+ | 880348 | +----------+ 1 row in set (0.00 sec)
Description: When attempting to update a table on our new database we get the error, ERROR 1034 (HY000): Incorrect key file for table 'blah'; try to repair it I apparently successfully repair the table but see exactly the same error when trying to update the table again. I am the only person using this system at the moment. How to repeat: When attempting to update a table on our new database we get the error, ERROR 1034 (HY000): Incorrect key file for table 'blah'; try to repair it I ran a myisamchk -e -v blah.MYI which showed, Checking MyISAM file: blah.MYI Data records: 2068296 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed myisamchk: warning: 1 client is using or hasn't closed the table properly - check file-size myisamchk: error: Size of indexfile is: 76377088 Should be: 109154304 - check record delete-chain No recordlinks - check key delete-chain block_size 1024: 12697600284296382159978519 myisamchk: error: key delete-link-chain corrupted - check records and index references myisamchk: error: Record at: 0 Can't find key for index: 1 myisamchk: error: Record at: 0 Can't find key for index: 2 myisamchk: error: Record at: 0 Can't find key for index: 3 myisamchk: error: Record at: 1607 Can't find key for index: 1 myisamchk: error: Record at: 1607 Can't find key for index: 2 myisamchk: error: Record at: 1607 Can't find key for index: 3 myisamchk: error: Record at: 3214 Can't find key for index: 1 myisamchk: error: Record at: 3214 Can't find key for index: 2 myisamchk: error: Record at: 3214 Can't find key for index: 3 myisamchk: error: Record at: 4821 Can't find key for index: 1 myisamchk: error: Record at: 4821 Can't find key for index: 2 myisamchk: error: Record at: 4821 Can't find key for index: 3 myisamchk: error: Record at: 6428 Can't find key for index: 1 myisamchk: error: Record at: 6428 Can't find key for index: 2 myisamchk: error: Record at: 6428 Can't find key for index: 3 myisamchk: error: Record at: 8035 Can't find key for index: 1 myisamchk: error: Record at: 8035 Can't find key for index: 2 myisamchk: error: Record at: 8035 Can't find key for index: 3 myisamchk: error: Record at: 9642 Can't find key for index: 1 myisamchk: error: Record at: 9642 Can't find key for index: 2 myisamchk: error: Record at: 9642 Can't find key for index: 3 myisamchk: error: Record at: 11249 Can't find key for index: 1 MyISAM-table 'blah.MYI' is corrupted Fix it using switch "-r" or "-o" I then ran myisamchk -r customers.MYI which gave the output, - recovering (with sort) MyISAM-table 'blah.MYI' Data records: 2068296 - Fixing index 1 - Fixing index 2 - Fixing index 3 When I now try and update the table I see exactly the same error and subsequently the same report when I check the table again. I have had to change the column names to submit it here but here is the table structure, CREATE TABLE `blah` ( `foo1` int(11) unsigned NOT NULL auto_increment, `bar1` char(64) NOT NULL default '', `foo2` char(255) NOT NULL default '', `bar2` char(32) NOT NULL default '', `foo3` char(16) NOT NULL default '', `bar3` char(255) NOT NULL default '', `foo4` char(64) NOT NULL default '', `bar4` char(64) NOT NULL default '', `foo5` char(64) NOT NULL default 'AB', `bar5` char(64) NOT NULL default '', `foo6` char(8) NOT NULL default '', `bar6` char(128) NOT NULL default '', `foo7` char(64) NOT NULL default '', `bar7` enum('Y','N') NOT NULL default 'Y', `foo8` char(255) NOT NULL default '', `bar8` char(255) NOT NULL default '', `foo9` enum('Y','N') NOT NULL default 'Y', `bar9` enum('Y','N') NOT NULL default 'Y', `foo10` bigint(20) default NULL, `bar10` char(2) default 'en', `foo11` enum('Y','N') NOT NULL default 'N', PRIMARY KEY (`foo1`), KEY `index_1` (`bar1`), KEY `index_2` (`bar6`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 Suggested fix: I have been unable to find a fix for this problem