MySQL Bugs: #27872: Incorrect error message when disk fills up running a large query against a view

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

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.

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>

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.

Bug #28363 was marked as duplicate of this one.

this one is a pain in the ass! please upgrade severity!

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.

This bug has a duplicate in the following one:

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