Bug #27872 Incorrect error message when disk fills up running a large query against a view
Submitted: 17 Apr 2007 6:26 Modified: 27 Sep 2007 8:36
Reporter: Joseph Libson Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.37-community-log OS:Linux
Assigned to: CPU Architecture:Any

[17 Apr 2007 6:26] Joseph Libson
I have a view that is defined against a couple of very large tables.

When I run a query against the view, mysql is using /tmp/ as a temporary storage area to build the result.

When /tmp/ runs out of space, you get the following error:

mysql> select * from v_payment_log limit 2;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_7f6_0.MYI'; try to repair it

As you can see from "df -h" /tmp/ was just about the run out of space when we got this error.

mysql> \! df -h /tmp
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda8             1.1G 1023M  4.7M 100% /tmp

And then as soon as we get the error it gets all cleaned up.

mysql> \! df -h /tmp
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda8             1.1G  477M  551M  47% /tmp

Obviously we should configure mysql to not use /tmp/.  But I thought that perhaps the error message should say something like: "out of disk space" or something a little more helpful.



How to repeat:

Have mysql configured to use /tmp/ (or some other smallish partition)

in your cnf file set:

tmpdir          = /tmp

Run a big query.  (it might have to be against a view, I am not sure).

Get a cup of coffee while your disk fills up.

Suggested fix:

Change the error message.
[17 Apr 2007 11:59] MySQL Verification Team
Thank you for the bug report.

mysql> insert into tbg (name) select name from tbg;
ERROR 126 (HY000): Incorrect key file for table 'e:\tmp\#sql_8e0_0.MYI'; try to repair it
[17 Apr 2007 13:07] Joseph Libson
And just to be clear.  The table that I was selecting against was an Innodb table. So the whole "repair" thing is not applicable anyway.
[9 Aug 2007 22:22] Sveta Smirnova
Bug #28363 was marked as duplicate of this one.
[10 Jan 2008 2:48] aaron brick
this one is a pain in the ass! please upgrade severity!
[11 Mar 2011 9:16] Daniƫl van Eeden
I can confirm that it's still an issue with 5.5.8.

Creating an index on a large InnoDB table returns this error:
ERROR 1034 (HY000): Incorrect key file for table 'test001'; try to repair it

You could see that it's an space issue if you watch /tmp.

I would suggest to change the severity to S2 as this could break replication easily.

1. The error should indicate possible issues with tmpdir.
2. For InnoDB it shouldn't suggest a repair.
[24 Apr 2020 13:10] MySQL Verification Team
This bug has a duplicate in the following one: