Ошибка incorrect key file for table

First of all, you should know that keys and indices are synonyms in MySQL. If you look at the documentation about the CREATE TABLE Syntax, you can read:

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

Now, the kind of error you are getting can be due to two things:

• Disk issues on the MySQL server
• Corrupted keys/tables

In the first case, you will see that adding a limit to your query might solve the problem temporarily. If that does it for you, you probably have a tmp folder that is too small for the size of the queries you are trying to do. You can then decide or to make tmp bigger, or to make your queries smaller!

Sometimes, tmp is big enough but still gets full, you’ll need to do some manual cleanup in these situations.

In the second case, there are actual issues with MySQL’s data. If you can re-insert the data easily, I would advice to just drop/re-create the table, and re-insert the data. If you can’t you can try repairing the table in place with REPAIR table. It is a generally lengthy process which might very well fail.

Look at the complete error message you get:

Incorrect key file for table ‘FILEPATH.MYI’; try to repair it

It mentions in the message that you can try to repair it. Also, if you look at the actual FILEPATH you get, you can find out more:

• if it is something like /tmp/#sql_ab34_23f it means that MySQL needs to create a temporary table because of the query size. It stores it in /tmp, and that there is not enough space in your /tmp for that temporary table.

• if it contains the name of an actual table instead, it means that this table is very likely corrupted and you should repair it.

If you identify that your issue is with the size of /tmp, just read this answer to a similar question for the fix: MySQL, Error 126: Incorrect key file for table.

You may encounter the MySQL error ‘Incorrect Key File for Table’ when running a large MySQL query. For instance, some users have reported receiving the error while executing a query to fetch thousands of records from a table using more than one JOIN.

Why Does MySQL Return ‘Incorrect Key File for Table’ Error When Running a Query?

The complete MySQL error message “Incorrect key file for table ‘FILEPATH.MYI’; try to repair it” suggests that you can try to repair the key. Looking at your table FILEPATH can help you find more details about the error:

• If the FILEPATH looks similar to ‘/tmp/#sql_xxxx.MYI‘, it means that your query is returning a large result set. The/tmp folder used for storing a temporary table containing the larger result set is too small for the query size. Essentially, there is not sufficient space in your /tmp folder for the temporary table.

• If your FILEPATH contains the database table’s name, it’s likely that your table is corrupt.

Possible Causes Behind MySQL Error’ Incorrect Key File for Table’ and Their Solutions

You may be getting the error because of a disk space issue or corruption in MySQL table. Read on to know how you can resolve these two issues.

Cause 1 – Insufficient Disk Space in the Temporary Folder (/Tmp)

Note: You won’t get the error on executing individual queries, but you may encounter the error when running large queries simultaneously on the same MySQL Server.

The primary reason that results in MySQL ‘Incorrect key file’ error is that your query is fetching data greater than the size of your /tmp folder (i.e., the folder used for storing temporary tables).

Solution – Change the location of ‘/temp’ folder

First of all, you must determine if the disk partition is full by looking at your /tmp folder size. Run the df command with ‘-h‘ option to check disk usage of the file system in human-readable form:

df –h

If the partition is full, then change the location of the /tmp folder to a disk with ample free space. Change the file location in the MySQL configuration file (.cnf).

Sometimes, the /tmp folder is big enough, but it might still get full. In that case, you will need to clean up some space in the folder manually.

Cause 2 – Corrupted Keys or Table

Table corruption is another reason that may lead to the ‘Incorrect Key File for Table’ error.

Solution – Re-create or Repair the Corrupted Table

To resolve the issue, drop/re-create the corrupt table and then re-insert the data.

For example, in the following query ‘DELETE’ operation is used for dropping the table, and ‘OPTIMIZE’ TABLE command is used afterward to free disk space.

mysql> DELETE tbl_name WHERE id < 200000;

mysql> OPTIMIZE TABLE tbl_name;

If you cannot re-insert the data, try repairing the table. For MyISAM tables, you can use the ‘REPAIR table’ command to fix the corruption:

REPAIR TABLE tbl_name USE_FRM;

This command will work only on MyISAM tables. For repairing tables with InnoDB database engine, check out the best ways to repair InnoDB table corruption in MySQL. But, running a repair command for each of the tables in the query can be a lengthy and time-consuming process.

What Else You Can Do to Repair Corrupt Tables?

Use Stellar Repair for MySQL software to repair corrupt tables. The software helps repair MySQL database and all its objects quickly in just a few clicks. It recovers all data from the corrupted database without data loss.

Conclusion

When you encounter the MySQL error ‘Incorrect Key File for Table’, the first thing you should do is to check the location of /tmp folder to ensure that it has enough disk space to store temporary tables. If not, then change the folder location to a disk having ample free space. If there is no issue with disk space, chances are that your database table is corrupt and needs to be repaired. If you fail to repair the table manually, using MySQL database repair software can help.

What does it mean if MySQL returns the ‘Incorrect key file for table’ error for one of my queries? The answer is complicated and depends on which storage engine is returning the error. We have debugged two cases which we describe here.

File system out of space

When running the random query generator, one of the queries failed.

Since this query requires a sort, MySQL creates a hidden temporary table called ‘#sql_6b8_17.MYI’ to hold the intermediate results. While the query was executing, some operation performed on the MyISAM table returned an error. What could it be?

MySQL maps the HA_ERROR_CRASHED error received from the storage engine to the ER_NOT_KEYFILE error that the MySQL client sees. We need to track down where MyISAM returns the HA_ERROR_CRASHED error. Unfortunately, the MyISAM storage engine has lots of places where it returns HA_ERROR_CRASHED, so the cause can be almost anything.

In our case, we found that the MyISAM mi_write function eventually got an ENOSPC (no space) error when trying to write data to one of its files. The file system ran out of space. In this case, MyISAM returns the HA_ERROR_CRASHED error, the MySQL client gets the ER_NOT_KEYFILE error, and the random query generator notes the failed query. Simple, right?

Race in secondary index query

TokuDB returns the HA_ERROR_CRASHED error when it can not read a row from the primary index using a primary key that it read from a secondary index. What does that mean?

Each key defined for a TokuDB table is stored in a fractal tree dictionary. The fractal tree for the primary key is stored in the ‘main’ fractal tree dictionary. If the table does not define a primary key, then TokuDB manufactures a hidden primary key and uses it as the primary key. This hidden primary key is never seen outside of the TokuDB storage engine. Each secondary key is stored in its ‘key’ fractal tree dictionary. The key into these dictionaries is composed of the columns of the secondary key and the columns of the primary key appended to it.

Given this mapping, non-covering queries read the secondary index first and then read the primary index using the primary key read from the secondary index.

Lets suppose that we have a simple table.

We insert a single row into the table

On another MySQL client, we will run a query and force it to use the secondary key for ‘x’. Since it is not a covering key for the query, TokuDB will read a row from the secondary key and use the primary key that was retrieved to read a row in the primary hidden dictionary. We are going to put a delay between these two events and see what happens.

We use read uncommitted isolation level.

We set a debug variable that forces a delay between reading a row from the index on ‘k’ and then using the retrieved primary key to read the full row from the hidden primary index.

Now, we start the query. It should stall the thread in the read_full_row method for the delay time.

On the other MySQL client, we delete the row.

Eventually, the query client resumes after the sleep completes and can no longer find the primary key in the primary fractal tree, so it returns the HA_ERR_CRASHED error.

This problem does not occur for any other transaction isolation levels as far as we know. This is because TokuDB uses snapshot reads when reading MVCC leaf entries for all transaction isolation levels other than read uncommitted. For read uncommitted transaction isolation level, TokuDB just returns the latest MVCC entries from the leaf entry, which may be inconsistent with the rest of the table.

Maybe, TokuDB should silently eat this error for read uncommitted queries. What do you think?

Skip to content