Bug #4252 Table DELETEs lead to table corruption
Submitted: 22 Jun 2004 20:36 Modified: 25 Jun 2004 21:02
Reporter: Gregert Johnson Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.0.17-nt/4.0.20a OS:Windows (Windows XP/Suse 9.0)
Assigned to: Sergei Golubchik CPU Architecture:Any

[22 Jun 2004 20:36] Gregert Johnson
Description:
After a series of row deletions, a table was found to be corrupted.  I prepared a test table from the original data to isolate the probelm.  The table was checked with myisamchk and the command line utility REPAIR TABLE command in ensure that it was initially valid.  After removing all data from the table with a DELETE command, executing the REPAIR TABLE command produced a series of messages indicating that corruption existed, and left the previously empty table with 2 new spurious rows.

The behavior I've observed is very similar to that reported in "Bug #197 table corruption".  I am attaching the test table I used in the test described above.

How to repeat:
Using the attached test data (table 'sip'), execute:

select count(*) from sip;
delete from sip where start_time > 0;
select count(*) from sip;
repair table sip use_frm;
select count(*) from sip;

The first count should be 100000.
The second count should be 0.
The third count should be 0, but is actually 2.  The two rows introduced contain spurious data.

Suggested fix:
I have no suggestions.

This is a problem which may seriously impact my company, since we are close to releasing a produce which depends heavily on MySQL.
[22 Jun 2004 20:42] Gregert Johnson
Note - I was not able to include the test data, since it was in excess of 7.5 MB (the limit here is 200KB).  I will be very happy to send this data (zip file format) to anyone who intends to investigate this bug.
[22 Jun 2004 20:50] Gregert Johnson
The output of the REPAIR TABLE command was as follows:

mysql> repair table sip use_frm;
+--------------------+--------+----------+-------------------------------------------------------+
| Table              | Op     | Msg_type | Msg_text                                              |
+--------------------+--------+----------+-------------------------------------------------------+
| hammer_monitor.sip | repair | info     | Delete link points outside datafile at 0              |
| hammer_monitor.sip | repair | info     | Wrong aligned block at 16948261                       |
| hammer_monitor.sip | repair | info     | Wrong bytesec: 51-3-0 at 16948261; Skipped            |
| hammer_monitor.sip | repair | info     | Wrong aligned block at 17062279                       |
| hammer_monitor.sip | repair | info     | Wrong bytesec: 103-61-56 at 17062279; Skipped         |
| hammer_monitor.sip | repair | info     | Found block that points outside data file at 24799748 |
| hammer_monitor.sip | repair | info     | Found block that points outside data file at 24799996 |
| hammer_monitor.sip | repair | warning  | Number of rows changed from 0 to 2                    |
| hammer_monitor.sip | repair | status   | OK                                                    |
+--------------------+--------+----------+-------------------------------------------------------+
9 rows in set (0.25 sec)
[22 Jun 2004 21:12] MySQL Verification Team
Please upload the file at:

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

and let me know when done.

thanks
[22 Jun 2004 21:59] Gregert Johnson
File sip_test.zip has been uploaded.
[23 Jun 2004 0:15] MySQL Verification Team
Tested also against version 4.0.21 on Suse.
[25 Jun 2004 14:13] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

It is not really a bug. REPAIR ... USE_FRM should be used as a last resort - when MYI header is corrupted. It tries hard to recover every possible bit of data, and can result in spurious rows.

What happens is that REPAIR ... USE_FRM discards MYI header and recreates it anew. But it is in the MYI header where MyISAM stores information about what blocks in MYD - data - file are free after delete and can be reused. When you ignore this information table looks corrupted, indeed.

The solution is to use REPAIR without USE_FRM whenever possible.
[25 Jun 2004 17:27] Gregert Johnson
This resolution makes me very uncomfortable.  For it implies that if in fact no corruption was introduced by the DELETE, then a documented MySQL utility command (REPAIR) itself corrupted a clean table.  The documentation makes no mention of possible dangers associated with the USE_FRM option; it simply indicates that the repair will be done using the .frm file, without the .MYI.  The only warning given is that REPAIR should be done again if the server went down during a previous REPAIR.  What could be in the .frm that would cause the introduction of spurious data?

I'd like to suggest that you examine this problem further.  My own suspicion is that the delete chain was corrupted by the DELETE, and that the REPAIR did not handle this situation properly.  I really cannot understand why an empty table, if it were in fact sound, should have bad data inserted into it by the REPAIR.  At the very least, I submit that the REPAIR utility is itself in need of repair!
[25 Jun 2004 21:02] Sergei Golubchik
Yes, DELETE introduced no corruption. Try CHECK TABLE to see.
REPAIR ... USE_FRM did not introduce any corruption either - try CHECK TABLE to see.
or try REPAIR without USE_FRM - you'll see the table is not corrupted.

MYI header contains vital informartion about the table. When it is lost, REPAIR ... USE_FRM makes its best efforts to recreate it - but it can only recreate static information, like table structure, but not what can be changed when you access the table, like deleted link or current auto_increment value.