A fix for “Incorrect key file for table ‘/tmp/#sql_xxxx_x.MYI'” in MySQL

The Most Likely Cause: You’re Out Of Space

Chances are, that if you’re seeing this error, you’ve run out of space in /tmp or wherever tmpdir points to. If only for the time it takes MySQL to create a temporary file (filling the parition) and delete it in disgust.

In Ever So Slightly More Detail

 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.