Categories

Incorrect key file for table 'mytable'; try to repair it

Yup, it’s one of those days today. A db_mysql.inc.php – based script spitted out this error:

Incorrect key file for table 'mytable'; try to repair it

Note to self: db_mysql.inc.php library has this parameter:

var $Halt_On_Error = "no"; ## "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore errror, but spit a warning)

And there should be no reason it’s set to “report” on a production system. But I digress…

table key corrupted error message in phpMyAdmin

table key corrupted error message in phpMyAdmin

An attempt to repair the table with PHPMyAdmin results in this error message. It’s time to invoke myisamchk via the ssh. First we need to find if there are other corrupted tables (they could be on a different hosting account and therefore phpMyAdmin would not show them). MySQL table files location is assumed for the standard cPanel – based setup. If your tables are kept in a different location, you will need to adjust the command accordingly. Log into your server via SSH as user root and run
# myisamchk /var/lib/mysql/*/*.MYI >> `echo $HOME`"/tmp/db_chk_"`date +%Y-%m-%d-%H:%M`".log"

This may take a while on a large server with multiple hosting accounts. But stick around, it’s just getting interesting.

I would have to add here that the error #1034 turns out to be a pretty tough one and it may require quite a bit of manual work. This repair falls, by MySQL definition, either into Stage 3: Difficult repair or Stage 4: Very difficult repair on their MyISAM table repair how-to page. The repair will involve moving files around and it is helpful to know which files are which because good people at MySQL don’t always explain that, assuming a certain level of prior knowledge. So, the table that needs to be repaired actually consists of three separate files:

  • mytable.MYD – data file – contains the actual table’s data
  • mytable.MYI – index file – contains the table’s indexes
  • mytable.frm – table definition file.

Make a copy of all three significant files belonging to the table, even though they are corrupted. Replace mydblocation with the actual directory name. It’s normally called something like myaccount_db_name on a typical cPanel setup.
STEP 1
cd /var/lib/mysql/mydblocation/
mkdir backup
cp mytable* ./backup/

Here our the repair comes to a fork in the road. You will be either happy or not – depends on whether you’ve saved any backups before today.

Scenario 1: It’s your lucky day. Only the index file has crushed.

Now TRUNCATE the table in question (delete all its content). Adjust all bold text to your actual names:
STEP 2
# mysql db_name
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE mytable;
mysql> quit

Here you have to stop MySQL service. Otherwise the next step would not work. You need to copy the data file (only the data file) with the actual table content from the backup to overwrite the truncated data file. Then use myisamchk to re-build the index. I prefer the slower way with the -o option. If you are in a hurry, you can try -r -q for quick repair.
STEP 3
# service mysql stop
# cp ./backup/*.MYD ./
# myisamchk -o mytable

Now we can start MySQL service again and check if the table is fine.
STEP 4
# service mysql start
# mysql db_name
mysql> EXPLAIN mytable;

If you don’t see any errors, the repair worked! Hint: if you’re out of the woods now, you can delete the data and the index files to save space but leave the mytable.frm file in there. Never know if and when you may encounter Scenario #2 instead.

Scenario 2: You’re screwed! The table definition file crushed, too

Yup, this is where it gets really tough (we are in the Very Difficult Repair territory) and you’re flirting with loosing your data for good at this point. Here you’ll be very glad to find any old backup of this database, regardless of how outdated the data in it would be because we need to restore the .frm file and, unless you made any changes to the table structure, it’s the same file you had on Day 1.
The instructions below assume that you already have the current data file sitting in the ./backup/ directory, saved during STEP 1.
The file we need, mytable.frm can come from several different places:

  1. You might have been super careful and at some point in the past saved the entire content of the /var/lib/mysql/mydblocation/ directory. If you haven’t made structural changes to the table, you can use mytable.frm from there
  2. You may have an SQL backup of the table (or entire database for that matter), similar to those created by mysqldump or phpMyAdmin -> Export utility. The backup must contain the SQL code for the structure of the table. Look for lines containing CREATE TABLE code for the table we need, something like
    CREATE TABLE `mytable` (
    
      `id` int(10) unsigned NOT NULL auto_increment,
    
      `num_times` int(10) unsigned NOT NULL default '0',
    
      PRIMARY KEY  (`id`)
    
    )";

    If you’ve found it, execute this SQL code on a different database. You may need to create a dummy database just for that. Then you can copy mytable.frm file from /var/lib/mysql/my_OTHER_dblocation to /var/lib/mysql/mydblocation

  3. If the table was created during installation of a script or a CMS of some sort, such as WordPress, SMF or any other system like that, look for the SQL code needed for creating the correct structure of the table (CREATE TABLE, as above) inside files with names like install.php or /install/index.php
  4. Sometimes there are “sister” tables in the database – tables with the same structure created to ease the load on the service by having to open a table with less records. Look around – maybe you can export the structure from a “sister” table into an SQL file, run it on a different database and then just rename the file to ‘mytable.frm
  5. You may be running other sites based on the same software. See if you can copy the ‘mytable.frm‘ file from that site’s account.
  6. As an absolutely crazy desperate move you can try to create a new table based on the code in your site’s software that makes queries for the table you’re fixing. It will be difficult to recreate the correct indexes that way except of the primary, which is normally just done on the “id” field. I haven’t done it myself yet, but my hunch is that you don’t need the indexes for the repair to work.

Regardless of how you got the mytable.frm, you need it to proceed further. If you don’t have it and can’t get it, it’s lights out for your data.

The further repair steps are similar to the Scenario #1 except for the hoops you had to jump through to get that mytable.frm file. STEP1 has already been completed, so we start with

STEP 2
# mysql db_name
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE mytable;
mysql> quit
# service mysql stop
# cp ./backup/*.MYD ./
# cp /wherever_it_is_now/mytable.frm ./
# myisamchk -o mytable

Drum roll and fingers crossed …
STEP 3
# service mysql start
# mysql db_name
mysql> EXPLAIN mytable;

Well, what do you know, the table IS WORKING!

Good luck with your repairs and may you never lose a bit of your data!