Bug #3574 optimize table causes indexfile file-size corruption
Submitted: 27 Apr 2004 6:18 Modified: 27 Apr 2004 11:47
Reporter: Jeff Armstrong Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:3.23.49 (debian stable) OS:Linux (Linux 2.4.24 i686 debian)
Assigned to: CPU Architecture:Any

[27 Apr 2004 6:18] Jeff Armstrong
I have a number of databases, and tables with high daily delete counts, so I run delete, optimise and check/repair daily. There are no other database update processes occuring at this time.

I issue the following SQL via a perl script:

FLUSH TABLE item_billing
-> returns OK

FLUSH TABLE item_billing
OPTIMIZE TABLE item_billing
-> returns OK

FLUSH TABLE item_billing
-> returns Corrupt

I now run 
> myisamchk -e  -i /var/lib/mysql/sbld_db001/item_billing.MYI

Data records:   47316   Deleted blocks:       0
/usr/bin/myisamchk: warning: Table is marked as crashed
- check file-size
/usr/bin/myisamchk: error: Size of indexfile is: 1248256         Should be: 1259520
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  89%  Packed:    0%  Max levels:  3
- check data record references index: 2
Key:  2:  Keyblocks used:  76%  Packed:   79%  Max levels:  3
Total:    Keyblocks used:  85%  Packed:   52%

- check records and index references
Record blocks:       47316    Delete blocks:         0
Record data:      21386832    Deleted data:          0
Lost space:              0    Linkdata:              0
MyISAM-table '/var/lib/mysql/sbld_db001/item_billing.MYI' is corrupted
Fix it using switch "-r" or "-o"

User time 4.39, System time 0.13
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 226, Physical pagefaults 233, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 0

How to repeat:
I run the check / optimise / check routine on 59 databases, containing 40 tables each night, after all data processing and deletions have been completed.

The first two or three tables that are Optimised in each database become corrupt. Tables where Optimise says 'Table is already up to date' are not affected.

It seems that the first two or three OPTIMIZE TABLE x in a database cause file-size corruption of indexfile? Strangely, myisamchk says that the indexfile is too small?

Hardware monitoring does not report any disk or memory faults on the server. I have issued a /etc/init.d/mysql restart, but the corruption continues.

Suggested fix:
Please let me know if you have any specific questions about the server config etc, or if there are any additional diags you require.

Regards & thanks,

[27 Apr 2004 11:47] Alexander Keremidarski
Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

3.23.49 is two years old. There are numerous of bugs fixed between 3.23.49 and current in this three 3.23.58