MySQL Bugs: #28363: internal temp tables gets immedialy corupted for huge numbers of rows

Bug #28363	internal temp tables gets immedialy corupted for huge numbers of rows
Submitted:	10 May 2007 18:23	Modified:	9 Aug 2007 22:21
Reporter:	Henrik Andersson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	temp table

Description:
When dealing with extremely big temporary tables that Mysql internally creates for some queries, the queries fails with the error:
"#126 - Incorrect key file for table '/tmp/#sql_38a5_0.MYI'; try to repair it"

The table storage engine does not matter.

How to repeat:
1. Create a huge table
1.1. run the query: "CREATE TABLE mrbig (
  `filler` varchar(50) NOT NULL default 'aaa'
);"
1.2. run the query: "insert into mrbig values()"
1.3. run the query: "insert into mrbig select * from mrbig union all select * from mrbig" until the error fires

Suggested fix:
Error out gracefully explaining that the generated temporary table could not be generated due to it's size.

Thank you for a problem report. What exact version of MySQL server you had used? 

Can you give more details on how to repeat? I tried with latest 5.0.44-BK on Linux:

...
mysql> insert into mrbig select * from mrbig union all select * from mrbig;
Query OK, 354294 rows affected (1.67 sec)
Records: 354294  Duplicates: 0  Warnings: 0

mysql> insert into mrbig select * from mrbig union all select * from mrbig;
Query OK, 1062882 rows affected (9.70 sec)
Records: 1062882  Duplicates: 0  Warnings: 0

mysql> insert into mrbig select * from mrbig union all select * from mrbig;
Query OK, 3188646 rows affected (37.37 sec)
Records: 3188646  Duplicates: 0  Warnings: 0

mysql> insert into mrbig select * from mrbig union all select * from mrbig;

the last statement hangs for almost an hour already, and, in other shell:

openxs@suse:~> df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda2              7871560   7871560         0 100% /
tmpfs                   128148         0    128148   0% /dev/shm

So, I already has no free disk space. Please, send your my.cnf also.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I used the phpmyadmin stable Demo server, I just redid the test on it, same error (save the random filename).
According to it, the server version is "5.0.41-Debian_1-log".

Please, try to repeat with a newer version, 5.0.45, already available. Inform about any results.

I think the issue is triggered if MySQL runs out of space on the temp drive for the temp table. I would try to confirm if it is like this, but I lack a suitable setup, maybe you can setup a server with a very small temp partion?
I also suspect it might be due to os settings preventing a single user from having too large files, preventing the file access for the user if the user goes over it.

Please, send the results of:

df -k
ulimit -a

and my.cnf from the system that is affected.

I sadly do not currently have such access to any host that has a mysql server and can not assist by doing that.
I wish I could help more, but I simply lack a suitable computer for a test.

Thank you for the report.

This seems to be duplicate of Bug #27872.