MySQL: #126 - Incorrect key file for table

I got the following error from a MySQL query.

#126 - Incorrect key file for table

I have not even declared a key for this table, but I do have indices. Does anyone know what could be the problem?

Solution 1:

Every Time this has happened, it's been a full disk in my experience.

EDIT

It is also worth noting that this can be caused by a full ramdisk when doing things like altering a large table if you have a ramdisk configured. You can temporarily comment out the ramdisk line to allow such operations if you can't increase the size of it.

Solution 2:

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.