3

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.

Community
  • 1
  • 1
Anil Bhattarai100
  • 693
  • 7
  • 32

3 Answers3

7

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
3

Have you checked your server's free disk space yet? Especially for the temporary directory. You'll note that the answers to the question you linked mention disk space.

lmz
  • 1,560
  • 1
  • 9
  • 19
2

DBD::mysql::st execute failed: Incorrect key file for table '/tmp/#sql_4091_0.MYI'; try to repair it [for Statement "...

The idea of it being suggested that one needs to run a repair on a temporary table should raise an eyebrow. It just doesn't make much sense: it suggests it's somehow corrupted, but since it's transitory and has just been freshly created by MySQL, it should never be needed. Not to mention tricky to implement.

In my experience, limited though it is, the primary reason for seeing this error message is because your tmpdir has run out of space. Like me you'll check how much free space you have: 1Gb, 2Gb, 4Gb. It may not be enough. And here's why: MySQL can create temporary tables bigger than that in a matter of seconds, quickly filling up any free space. Depending on the nature of the query and the size of the database naturally.

Take this evening for example: I had a temporary table created that was around 3.6Gb in size, from a database that's only 5.4Gb in total, and I had only had 1.8Gb of free space. Imagine a couple of queries of that nature running in parallel.

The moment the space has been filled, the above error will be thrown, the temporary file on disk will be deleted, and you'll be none the wiser. Conclusion

Perhaps the MySQL instance could be better configured - but that's not the point - the error message is oblique at best. Make sure you've got enough temporary space, you may need more than you imagine

Ressiva Technologies Inc.
  • 111
  • 2