How do you fix a MySQL Incorrect key file error when you can t repair the table

Question

I m trying to run a rather large query that is supposed to run nightly to populate a table   I m getting an error saying Incorrect key file for table   var tmp  sql 201e 0 MYI   try to repair it but the storage engine I m using  whatever the default is  I guess   doesn t support repairing tables   how do I fix this so I can run the query

User · Answer

In my case  there was a disc space issue  I deleted some unwanted war files from my server and it worked after that

User · Answer

You ll need to run this command from the MySQL prompt   REPAIR TABLE tbl name USE FRM    From MySQL s documentation on the Repair command      The USE FRM option is available for use if the  MYI index file is missing or if its header is corrupted  This option tells MySQL not to trust the information in the  MYI file header and to re-create it using information from the  frm file  This kind of repair cannot be done with myisamchk

User · Answer

The storage engine  MyISAM  DOES support repair table  You should be able to repair it   If the repair fails then it s a sign that the table is very corrupted  you have no choice but to restore it from backups   If you have other systems  e g  non-production with same software versions and schema  with an identical table then you might be able to fix it with some hackery  copying the frm an MYI files  followed by a repair    In essence  the trick is to avoid getting broken tables in the first place  This means always shutting your db down cleanly  never having it crash and never having hardware or power problems  In practice this isn t very likely  so if durability matters you may want to consider a more crash-safe storage engine

User · Answer

Apply proper charset and collation to database  table and columns fields   I creates database and table structure using sql queries from one server to another   it creates database structure as follows    database with charset of  utf8   collation of  utf8 general ci  tables with charset of  utf8  and collation of  utf8 bin   table columns   fields have charset  utf8  and collation of  utf8 bin     I change collation of table and column to utf8 general ci  and it resolves the error

User · Answer

this issue is because of low storage space availability of a particular drive c   or d   etc     release some memory then it will work   Thanks Saikumar P

User · Answer

REPAIR TABLE tbl name USE FRM   Command only run when MySQL  Storage Engine  type should be  MyISAM   Hope this helps

User · Answer

Simple  REPAIR the table  from PHPMYADMIN solved this problem for me    go to phpmyadmin open problematic table go to Operations tab  in my version of PMA  at the bottom you will find  Repair table  link

User · Answer

You must change the location of MySQL s temporary folder which is   tmp  in most cases to a location with a bigger disk space  Change it in MySQL s config file   Basically your server is running out of disk space where  tmp is located

User · Answer

Change to MyISAM engine and run this command REPAIR TABLE tbl name USE FRM

User · Answer

This happenes might be because you ran out of disk storage and the mysql files and starting files got corrupted  The solution to be tried as below  First we will move the tmp file to somewhere with larger space  Step 1  Copy your existing  etc my cnf file to make a backup  cp  etc my cnf   back- date   Y m d     Step 2  Create your new directory  and set the correct permissions  mkdir  home mysqltmpdir chmod 1777  home mysqltmpdir   Step 3  Open your  etc my cnf file  nano  etc my cnf   Step 4  Add below line under the  mysqld  section and save the file  tmpdir  home mysqltmpdir   Secondly you need to remove or error files and logs from the  var lib mysql ib   that means to remove anything that starts by  ib   rm  var lib mysql ibdata1 and rm  var lib mysql ibda     and so on  Thirdly you will need to make sure that there is a pid file available to have the database to write in  Step 1 you need to edit  etc my cnf  pid-file   var run mysqld mysqld pid    Step 2 create the directory with the file to point to   mkdir  var run mysqld  touch  var run mysqld mysqld pid  chown -R mysql mysql  var run mysqld   Last step restart mysql server   etc init d mysql restart

User · Answer

Your query is generating a result set so large that it needs to build a temporary table either to hold some of the results or some intermediate product used in generating the result   The temporary table is being generated in  var tmp   This temporary table would appear to have been corrupted   Perhaps the device the temporary table was being built on ran out of space   However  usually this would normally result in an  out of space  error   Perhaps something else running on your machine has clobbered the temporary table      Try reworking your query to use less space  or try reconfiguring your database so that a larger or safer partition is used for temporary tables   MySQL Manual - B 5 4 4  Where MySQL Stores Temporary Files

[mysql] How do you fix a MySQL "Incorrect key file" error when you can't repair the table?

Examples related to mysql

Examples related to mysql-error-126