What can be the solution for frequently occuring #126 - Incorrect key file for table '/tmp/#sql_4a05_0.MYI'; try to repair it in mysql?

Question

I have a database table named answer when I insert data into in it, the data get inserted but it give the following problem to another table '#126 - Incorrect key file for table '/tmp/#sql_4a05_0.MYI'; try to repair it ' Then I solve this problem by using the solution given in the this link But this problem occurs when I again insert data to the answer table.

And the another thing when I export that database and use somewhere(place B) from place A, it was working so again I drop the database from the Place A and import database to Place A from place B(where there are some more data that I have inserted in place B). But while importing data to place A it does not import all data of table 'answer' and gives the following error ERROR 1114 (HY000): The table is full I used the sollution in the following link but I am unable to solve this problem. Can anybody have this solution for this problem.

Answer 1

The error might not be caused by your table, but instead a temporary table that is created during filesort or other query operations. These temporary tables are stored in MyISAM format, in your tmpdir. If there's not enough space in tmpdir to hold the temporary table, you can get this error.

Since it's a problem with a too-large temporary table, not your permanent table, running REPAIR TABLE against your permanent table doesn't fix the problem.

The solution is to change tmpdir to a location that has more free space. Note also that if you have many queries running concurrently, and all of them are using some part of the space in tmpdir, the free space could still be exhausted.

You could also try to improve query optimization, so fewer queries use filesort or other temporary table usage. Or you could redesign your queries so they process fewer rows of data.