We have a script that deletes records from a database that are over then a certain date. We had no problem running the delete on 3.x, but on 4.0.14, we are having a problem.
Here is the command that does the delete: $EXECPATH/mysql --host=hostname --user=blah --password=blah --execute="delete from MESSAGES where to_days(now()) - to_days(timestamp) > 2 and sent = 'Y'" test After running this, the index is hopelessly corrupted and we need to run "myisamchk -ro" on the index file to fix the problem. I also noticed that the index is corrupt even before the delete is happening. This database receives about 9 million records a week and is constantly updated 24/7. Here is the output of "myisamchk -ai *.MYI": bash-2.05$ bin/myisamchk -ai ./data/test/*.MYI Checking MyISAM file: ./data/test/MESSAGES.MYI Data records: 108469 Deleted blocks: 0 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 3 - check data record references index: 2 Key: 2: Keyblocks used: 62% Packed: 69% Max levels: 3 - check data record references index: 3 myisamchk: error: Found key at page 7577600 that points to record outside datafile - check record links myisamchk: error: Keypointers and record positions doesn't match Record blocks: 108469 Delete blocks: 0 Record data: 92107260 Deleted data: 0 Lost space: 81555 Linkdata: 406685 MyISAM-table './data/test/MESSAGES.MYI' is corrupted Fix it using switch "-r" or "-o" User time 4.47, System time 1.51 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 0, Physical pagefaults 88, Swaps 0 Blocks in 2524 out 4, Messages in 0 out 0, Signals 0 Voluntary context switches 387, Involuntary context switches 773 Could this corruption cause the delete to totally fubar the indexes? If so, is there a good way to manage the indexes on the database that has so many changes? Should I run the "-r" or "-o" on the file before I delete? Thanks, Ben Ricker Wellinx, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
