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

User · Answer

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

User · Answer

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    stop the MySQL service  Open mysql data Remove both ib logfile0 and ib logfile1  Restart the service

User · Answer

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

User · Answer

mysql gt  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 gt  repair table f scraper banner details   This worked for me

User · Answer

Came here searching for -  quot  1034 - Incorrect key file for table  test   try to repair it quot  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

User · Answer

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

User · Answer

repair table myschema mytable

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

mysqlcheck -r -f  -uroot -p   --use frm db name  will normally do the trick

User · Answer

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

User · Answer

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

User · Answer

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  quot Integrity constraint violation quot

User · Answer

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

User · Answer

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

[mysql] MySQL: #126 - Incorrect key file for table

Examples related to mysql

Examples related to mysql-error-126