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:
None 
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
Description:
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.

Thanks,

Joe

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
mysql>
[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:

https://bugs.mysql.com/bug.php?id=99293

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.