How To – Resolve MySQL Error Incorrect Key File for Table

Background Knowledge

I using PHP v5.3.3-7 PDO running a MySQL v14.14 Distrib 5.1.49 on Debian v6.0.4 64-bit and executing a SQL load data infile statement.

I received “PHP Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 126 Incorrect key file for table ‘/tmp/#sql_66f_0.MYI’; try to repair it”. My database table in this instance is using the storage engine of InnoDB and therefore one can not use the “repair table”.

From my experience I’ve found that this error can mean one of two issues however I have not found information from MySQL confirming this.

Solution – Repair Table

The error message may mean the database table is corrupted and requires a repair.

  1. Run repair table on the associated database table.
  2. Re-run the previous query.
  3. The issue will now be resolved.

Solution – Free Disk Space

The error message may mean the system does not have enough free disk space.

  1. Verify the free disk space. On Linux execute at the console “df -h”. On Windows open “My Computer” to see free space available.
  2. If low or out of disk space, free up some space or follow the next steps to change MySQL server setting to point to a new temporary path.
  3. Edit the MySQL server configuration file, my.cnf (on Debian, /etc/mysql/my.cnf).
  4. Change the “tmpdir” path variable to a new location with more available free disk space (ex. /var/tmp or D:\temp).
  5. Stop MySQL daemon/service and then start MySQL daemon/service for the configuration changes to take affect.