Mysql ошибка 126

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.

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.

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.

013-09-27T05:16:39 Query: SELECT * FROM (mysql . general_log AS table1 INNER JOIN INFORMATION_SCHEMA . INNODB_BUFFER_PAGE AS table2 ON ( table2 . SPACE = table1 . user_host ) ) ORDER BY table1 . thread_id LIMIT 168 failed: 126 Incorrect key file for table '/data/mysql7/performance_schema_vardir/tmp/#sql_6b8_17.MYI'; try to repair it

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.

mysql> show create table t;
| t     | CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL,
  `y` int(11) DEFAULT NULL,
  KEY `x` (`x`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=TOKUDB_ZLIB |

We insert a single row into the table

mysql 1> insert into t values (1,2);
Query OK, 1 row affected (0.00 sec)

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.

mysql 2> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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.

mysql 2> set tokudb_read_full_row_delay=10000000;

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

mysql 2> select * from t force index (x) where x>0;
blocked sleeping

On the other MySQL client, we delete the row.

mysql 1> delete from t where x=1;
Query OK, 1 row affected (0.00 sec)

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.

mysql 2> resumes after the sleep completes
ERROR 1034 (HY000): Incorrect key file for table 't'; try to repair it

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?

Inconsistent TokuDB Table

Suppose that MySQL is doing an index lookup on TokuDB key that is not the primary key. It may have to use the primary key from the secondary key to retrieve the values of any columns that are not covered by the secondary key. If a secondary key exists in the secondary index and the primary key associated with the secondary key does NOT exist in the primary index, then TokuDB returns the HA_ERR_CRASHED error number to MySQL. This error gets translated to the ER_NOT_KEYFILE error number which gets translated to the ‘incorrect key file for table’ error message.

So, if a secondary index is inconsistent with the primary index, then this error can occur.