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

By Stephen Jayna, 8th March 2011

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.



Your Comments

You saved my DAY! Thanks a

You saved my DAY! Thanks a lot


You are very right

What you say is absolutely right.
The tmp dir size needs to be at least twice the size of the biggest table you have .

CHeck the mysql link below for more info

http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html

ALso note that mysql does mention:

To spread the load effectively, these paths should be located on different physical disks, not different partitions of the same disk.


You saved me!

I was getting crazy. After saving a wrong view that created billions of row, the server became extremely slow. We had to kill mysql, but nothing changed.
After reading your post I went in /tmp and I found 50 giga of tmp tables.
Every time the server started, user of the site began querying the db, and soon everything became slow because of mysqld writing giga and giga on /tmp, and we can't modify the query because it was nearly impossible connect, but also give commands to the shell.
The solution I found was to stop mysql, change port enter as the only user, update the view and reset the port.
This worked, but I think it's incredible that saving a wrong query can block an entire server, in a way that is very difficult restore from.


Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

Having MySQL performance issues?

We're experts at tuning MySQL and offer a MySQL performance consulting service.

LAMP stack not performing as you'd hoped?

Everita is experienced at getting the most out of your Linux, Apache, MySQL and Perl, PHP or Python setup. We're Drupal Experts.

Client Testimonials

Steve was knowledgeable and diligent in helping us identify application characteristics which were impacting MySQL's efficiency.

I would recommend him to anyone needing help optimising MySQL server and look forward to working with him in the future.

Richard Ainley
Performance Tester
WorkPlace Systems PLC

Next »

Subscribe

Enter your email address below to receive a very occasional message when something significant is published on the site.

You can unsubscribe at any time and we'll never share your address.

Contact Us

E: info@everita.com
L: Reading, United Kingdom

Linux & Mac Specialists

Images courtesy of Rowan Mersh