Hi All, InnoDB tables as the solution is incorrect. I've been running some fairly large InnoDB databases, and crashes using InnoDB are probably ALOT worse than with MyIsam tables.
InnoDB tables tend to corrupt very easily on such things as power outages, with corrupted page data error which means that mysql doesn't start at all, and the only option is to start InnoDB in recovery mode, dump and hope for the best. How big is your database? If it can fit in RAM, I'd suggest using a ramdisk to store your database with snapshots taken every X minutes and stored to disk (or using NVRAM to store the database). Other than that, there's no quick way to check for corruption that I know of. Cheers, Mark Steele Implementation Director CDT Inc. -----Original Message----- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: November 8, 2004 12:43 PM To: Tim Murtaugh Cc: '[EMAIL PROTECTED]' Subject: Re: Fast method needed to determine if a table is corrupt In the last episode (Nov 08), Tim Murtaugh said: > I'm using MySQL server version 4.0.15a in an embedded envirionment (as > a standalone server, I'm not using the embedded server library). I > have 128 MB of memory and disk space is tight. I'm using MyISAM > tables. > > If my system loses power, some tables are left in a corrupt state. As > stated in the MySQL documentation, I think the data tables are OK, its > just that the tables were not closed properly and are considered > corrupt by MySQL. > > I need a FAST way to determine if a table is corrupt. I've tried > myisamcheck --fast and --check-only-changed options, and increased the > buffer sizes (-O key_buffer_size and -O sort_buffer_size), as > mentioned in the documentation. The fastest time I can achieve is > 6:55. > > I've also tried "CHECK TABLE tablename FAST QUICK" on a table I know > is marked as corrupt, and the fastest time I can achieve is 6:58. > > I need to detemine if a table is corrupt within a few SECONDS, not > minutes. How can I do this? Make your tables smaller? :) You have to check each record to see that it's okay. If your tables are big, you have to spend time reading them. > The documentation says there is a flag in myisam tables that indicates > when a table is corrupt. Is there a way I can quickly check this flag? If mysql tries to read a record or index and can't, it sets this flag to keep you from accessing the table until you repair it. You may be better off using InnoDB tables and taking the space/performance hit. InnoDB uses a logfile to allow it to roll back partially-commited transactions after a crash, so you never have to check or repair your tables. -- Dan Nelson [EMAIL PROTECTED]
smime.p7s
Description: S/MIME cryptographic signature