Description:
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
CHECK TABLE item_billing MEDIUM
-> returns OK
FLUSH TABLE item_billing
OPTIMIZE TABLE item_billing
-> returns OK
FLUSH TABLE item_billing
CHECK TABLE item_billing MEDIUM
-> 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,
Jeff