Bug #9336 REPAIR TABLE on an unrepairable table may end up tmpdir's space
Submitted: 22 Mar 2005 12:46 Modified: 24 Mar 2015 18:26
Reporter: Anders Henke Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.24 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[22 Mar 2005 12:46] Anders Henke
Description:
I do have a specific table (~60MB uncompressed, 15MB tar.gz) which fails in 
REPAIR TABLE:
-the table is marked as "crashed and last repair failed"
-even "myisamcheck -vef" also won't fix it.

"REPAIR TABLE" results in the MySQL server eats up tmpdir's space using 
a likely then-deleted file.

Symptoms are: "df" reports /tmp to be full while "du -s /tmp"shows that the tmpdir isn't;
also the table isn't successfully repaired. 
From my experience, the disk space situation normally appears when a process opens 
a file, unlinks that file and writes data into that file rather than closing the file before
unlinking it. The only way to reclaim the space from is to make the using process
close that file - in this example, to shut down the mysql server (which in that certain
situation may take a minute).

During the REPAIR TABLE, in "SHOW PROCESSLIST" the corresponding MySQL 
thread locks up virtually forever  like this:
Query          | 717  | Repair by sorting  | REPAIR TABLE `tom_entries_index`

"myisamcheck -vrf" and "myisamcheck -ve" also don't successfully recover that table,
but they also don't throw an error message.

How to repeat:
I have a copy of that affected table available, however the data not for public disclosure.
Please drop me a note where to upload this ~15 MB .tar.gz to ...

Afterwards, create a database, untar the archive into the database's directory and
feel free to run your checks against this.

A note: the database user does only have mysql-access to that server and the
server didn't experience any outages or hardware failures during the last 177 days.
The server is also running on ECC memory and a RAID1, so I doubt that the
table is result of flawky hardware. On the other hand, I haven't yet seen a similar broken table, so either it's a "new" bug in 4.0.24 or otherwise an extremely rare one.
[22 Mar 2005 14:08] MySQL Verification Team
You can upload the file at:

ftp://ftp.mysql.com/pub/mysql/upload/

with a file name identifying this bug report.
Let us know here when done.

Thanks
[23 Mar 2005 8:48] Anders Henke
Upload complete:
2.3M Mar 22 13:49 bug9336.dump.gz
16M Mar 22 13:30 bug9336.tar.gz

The table's dump has been taken from the backup previous to the repair attempts, 
so you've some way to how the table should look like.
[23 Apr 2005 23:00] Bugs System
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".
[25 Apr 2005 8:19] Anders Henke
Ehem, feedback has been provided on Mar 23rd ...
[3 Dec 2014 2:17] David Bergeron
I am stuck with a table that I cannot repair, and the problem seems to be related.
[24 Feb 2015 18:26] Sveta Smirnova
David,

thank you for the feedback. Could you please upload your table's MYD, MYI and frm files?
[25 Mar 2015 1:00] Bugs System
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".