MySQL: #126 - Incorrect key file for table

Question

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?

Answer 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.

Answer 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.

Answer 3

Following these instructions allowed me to recreate my tmp directory and fix the issue:

Display all file systems and their disk usage in human readable form:

df -h

Find the processes that have files open in /tmp

sudo lsof /tmp/**/*

Then umount /tmp and /var/tmp:

umount -l /tmp
umount -l /var/tmp

Then remove the corrupt partition file:

rm -fv /usr/tmpDSK

Then create a nice new one:

/scripts/securetmp

Note that by editing the securetmp Perl script you can manually set the size of the tmp directory yourself, however just running the script increased the size of the tmp directory on our server from roughly 450MB to 4.0GB.

Answer 4

Error #126 usually occurs when you got a corrupt table. The best way to solve this is to perform repair. This article might help:

http://dev.mysql.com/doc/refman/5.0/en/repair-table.html

Answer 5

I got this error when I set ft_min_word_len = 2 in my.cnf, which lowers the minimum word length in a full text index to 2, from the default of 4.

Repairing the table fixed the problem.

Answer 6

I know that this is an old topic but none of the solution mentioned worked for me. I have done something else that worked:

You need to:

1. stop the MySQL service:
2. Open mysql\data
3. Remove both ib_logfile0 and ib_logfile1.
4. Restart the service

Answer 7

Try to use limit in your query. It's because of full disk as said by @Monsters X.

I have also faced this problem and solved by limit in query, because the thousands of records were there. Now working good :)

Answer 8

repair table myschema.mytable;

Answer 9

I fixed this issue with:

ALTER TABLE table ENGINE MyISAM;
ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field);
ALTER TABLE table ENGINE InnoDB;

May helps

• Refs: MySQL: ALTER IGNORE TABLE gives "Integrity constraint violation"

Answer 10

Go to /etc/mysql/my.cnf and comment out tmpfs

#tmpdir=/var/tmpfs

This fixes the problem.

I ran the command suggested in another answer and while the directory is small, it was empty, so space was not the issue.

/var/tmp$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/vzfs              60G   51G  9.5G  85% /
none                  1.5G  4.0K  1.5G   1% /dev
tmpfs                 200M     0  200M   0% /var/tmpfs
/var/tmpfs$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/vzfs              60G   51G  9.5G  85% /
none                  1.5G  4.0K  1.5G   1% /dev
tmpfs                 200M     0  200M   0% /var/tmpfs

Answer 11

Now of the other answers solved it for me. Turns out that renaming a column and an index in the same query caused the error.

Not working:

-- rename column and rename index
ALTER TABLE `client_types`
    CHANGE `template_path` `path` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    DROP INDEX client_types_template_path_unique,
    ADD UNIQUE INDEX `client_types_path_unique` (`path` ASC);

Works (2 statements):

-- rename column
ALTER TABLE `client_types`
    CHANGE `template_path` `path` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
-- rename index
ALTER TABLE `client_types`
    DROP INDEX client_types_template_path_unique,
    ADD UNIQUE INDEX `client_types_path_unique` (`path` ASC);

This was on MariaDB 10.0.20. There were no errors with the same query on MySQL 5.5.48.

Answer 12

Try to run a repair command for each one of the tables involved in the query.

Use MySQL administrator, go to Catalog -> Select your Catalog -> Select a table -> Click the Maintenance button -> Repair -> Use FRM.

Answer 13

mysql> set global sql_slave_skip_counter=1; start slave; show slave status\G

Then got error exists :

 Error 'Table './openx/f_scraper_banner_details' is marked as crashed and should be repaired' on query. Default database: 'openx'. Query: 'INSERT INTO f_scraper_banner_details(job_details_id, ad_id, client_id, zone_id, affiliateid, comments, pct_to_report, publisher_currency, sanity_check_enabled, status, error_code, report_date) VALUES (10274859, 321264, 0, 31926, 0, '', -1, 'USD', 1, 'FAILURE', 'INACTIVE_BANNER', '2016-06-28 04:00:00')'

 mysql> repair table f_scraper_banner_details;

This worked for me

Answer 14

My issue came from a bad query. I referenced a table in FROM the was not referenced in SELECT.

example:

   SELECT t.*,s.ticket_status as `ticket_status`
   FROM tickets_new t, ticket_status s, users u

, users u is what was causing the issue for me. Removing that solved the issue.

For reference this was in a CodeIgniter dev environment.

Answer 15

I got this message when writing to a table after reducing the ft_min_word_len (full text min word length). To solve it, re-create the index by repairing the table.

Answer 16

mysqlcheck -r -f  -uroot -p   --use_frm db_name

will normally do the trick

Answer 17

Came here searching for - "#1034 - Incorrect key file for table 'test'; try to repair it"

Seeing this caused by added a charset to an indexed Enum (might be the same with other fields) with Mysql 8.0.21.

CREATE TABLE `test` (
`enumVal` ENUM( 'val1' ) NOT NULL
) ENGINE = MYISAM;
ALTER TABLE `test` ADD INDEX ( `enumVal` );

ALTER TABLE  `test` CHANGE  `enumVal`  `enumVal` ENUM(  'val1') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

Solution using is to drop the index before the alter.

ALTER TABLE `test` ADD INDEX ( `enumVal` );