Hi, Advice on the cause of the following table corruption would be much appreciated.
Some background: This customer has been running MySQL for 3 or more years, and, for back up purposes, shuts MySQL down each night, takes a snapshot of the tables for backup, and restarts. We have sufferred a table corruption before, of the same table, when the table was an ISAM table under version 3.23.40. We have since upgraded to 4.0.13, and changed the table to MyISAM type. Searching the list archive for 'Wrong bytesec' on mysql.com returns no results. Google returns some, but mainly old ones. Maybe an appendix to the manual giving some information about the possible things that myisamchk can find/repair would be useful? Are there any known problems with shutting down and re-starting so regularly? Would a flush-tables followed by a check that there are no open tables achieve the same result. Is there a way of stopping new connections to a running server? mysql> REPAIR TABLE tt_tickets_bck and # myisamchk -r tt_tickets_bck both fixed the issue, but I'm being asked why the corruption happened in the first place. Myisamchk -vvv resulted in 212424 lines of information. Thanks in advance Quentin Bennett # mysqladmin version mysqladmin Ver 8.40 Distrib 4.0.13, for dec-osf5.1 on alphaev67 (-- Binaries from mysql.com --) Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-max-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /data/mysql/mysql.sock Uptime: 1 hour 21 min 23 sec Threads: 37 Questions: 42387 Slow queries: 16 Opens: 258 Flush tables: 2 Open tables: 113 Queries per second avg: 8.681 mysql> check table tt_tickets_bck; +------------------------+-------+----------+--------------------------- --------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------+-------+----------+--------------------------- --------------------------------+ | mercury.tt_tickets_bck | check | warning | Table is marked as crashed | | mercury.tt_tickets_bck | check | warning | 2 clients is using or hasn't closed the table properly | | mercury.tt_tickets_bck | check | warning | Size of datafile is: 290516700 Should be: 285712092 | | mercury.tt_tickets_bck | check | error | Unexpected byte: 5 at link: 285506400 | | mercury.tt_tickets_bck | check | error | Corrupt | +------------------------+-------+----------+--------------------------- --------------------------------+ 5 rows in set (58.28 sec) # myisamchk -vvv -r tt_tickets_bck - recovering (with sort) MyISAM-table 'tt_tickets_bck' Data records: 5641601 - Fixing index 1 - Searching for keys, allocating buffer for 46268 keys Wrong bytesec: 31- 0- 0 at 304; Skipped Wrong bytesec: 49- 55- 53 at 320; Skipped Wrong bytesec: 5- 87- 50 at 328; Skipped Wrong bytesec: 48- 53- 55 at 285506420; Skipped . . . Wrong bytesec: 4- 49- 53 at 692; Skipped Wrong bytesec: 5- 87- 50 at 700; Skipped Found block with impossible length 3289448 at 285506560; Skipped Wrong bytesec: 27- 0- 0 at 1080; Skipped Wrong bytesec: 56- 54- 48 at 1096; Skipped Wrong bytesec: 87- 50- 29 at 1108; Skipped . . . Wrong bytesec: 27- 0- 0 at 5080; Skipped Wrong bytesec: 53- 53- 55 at 5096; Skipped Wrong bytesec: 87- 50- 29 at 5108; Skipped Found block with too small length at 5116; Skipped Wrong bytesec: 27- 0- 0 at 5120; Skipped Wrong bytesec: 53- 53- 55 at 5136; Skipped . . . Wrong bytesec: 56- 50- 56 at 5762080; Skipped Wrong bytesec: 87- 50- 29 at 5762092; Skipped Delete link points outside datafile at 285512060 Wrong bytesec: 27- 0- 0 at 5762148; Skipped Wrong bytesec: 54- 57- 57 at 5762164; Skipped . . Wrong bytesec: 200- 3- 1 at 290516684; Skipped - Merging 5621599 keys - Last merge and dumping keys - Fixing index 2 - Searching for keys, allocating buffer for 139678 keys - Merging 5621599 keys - Last merge and dumping keys - Fixing index 3 - Searching for keys, allocating buffer for 104727 keys - Merging 5621599 keys - Last merge and dumping keys Data records: 5621599 # Quentin Bennett Senior Analyst Infinity Solutions Ltd PO Box 3323, Auckland Ph: 09 921 8146 Fx: 09 309 4142 www.infinitytransport.net The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
