Hi, Thanks - the tables were converted to MyISAM a while ago, and have run with no problem. Because they have been repaired before, following a previous crash, I believe that prior to the shut down last night all was well.
I have run in to the issue of trying to repair a 'live' table, with subsequent disaster, but that is not the issue here - the issue is why does the table get corrupted in the first place. Regards Quentin Bennett Senior Analyst Infinity Solutions Ltd PO Box 3323, Auckland Ph: 09 921 8146 Fx: 09 309 4142 www.infinitytransport.net > -----Original Message----- > From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] > Sent: Thursday, 13 November 2003 9:16 a.m. > To: Quentin Bennett > Subject: RE: Repeated table corruptions > > > Are you shutting down mysql and running myisamchk -r? If not > then you can cause some major problems for your data. > > Also this was an ISAM table an old IBM format that > historically is unreliable in mySQL, thus the need for the > much more stable myISAM format. > > During the conversion process there might have been corrupted > data, which is causing some weird things to this day. Maybe > the best thing to do is to mysqldump the table, then > re-create it from the dump. > > > > - Dathan Vance Pattishall > - Sr. Programmer and mySQL DBA for FriendFinder Inc. > - http://friendfinder.com/go/p40688 > > > -->-----Original Message----- > -->From: Quentin Bennett [mailto:[EMAIL PROTECTED] > -->Sent: Wednesday, November 12, 2003 11:46 AM > -->To: [EMAIL PROTECTED] > -->Subject: Repeated table corruptions > --> > -->Hi, > --> > -->Repost - its happened again. Anyone any ideas.... PLEASE! > --> > -->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 suffered 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 > --> > --> > --> > -->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. > > > 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]
