Thanks Mike,
I've always ignored CHECK TABLE because I always thought it was just for MyISAM.
I ran CHECK TABLE. It told me that my table was corrupt. I then dumped the table with mysqldump and recreated it. After that CHECK TABLE said it was OK (and in comparing values with the master server it appears to have all data intact as well). Unlike CHECK TABLE, REPAIR TABLE only works on MyISAM tables, so I can't use that.
Then, I decided to run CHECK TABLE on all my tables (which for the ones with 125 million rows will probably be running for a while). The problem now is that all of my larger tables are reported as being corrupt--every single table with more than say 500,000 records is reported as corrupt. Wow! Could this be true?
Yup. But it could just be that the table counts that are off.
The tables all access fine and only these two smaller tables had these count(*) mismatch problems (and were the only two smaller tables that came up corrupt).
How did this happen? I've never gotten an error in my .err file, never had a hardware access failure in the system logs, and have done very little with this server beyond initially loading it (by replaying mysqldump output in the first place) and letting it stay up to date with replication.
In my case, I can "corrupt" tables by not shutting down the server properly. For example, if the server crashes then it could cause problems with the files because the tables are not flushed prior to closing the tables so the counts can be off. Or if you Kill a process like REPAIR TABLE or OPTIMIZE TABLE can lead to a corrupt table. Or if you are using --delay-key-writes in your .cnf file can cause problems if you're not careful.
There is a Shutdown command you can execute to shut down the server that automatically flushes the tables and closes everything in an orderly fashion. http://dev.mysql.com/doc/mysql/en/Server_Shutdown.html
http://dev.mysql.com/doc/mysql/en/Crashing.html
It's going to take me a week to reload these tables if they are all corrupt. Based on what little this system has done so far, I wonder how long it will be before they're corrupt again.
A week? That must be a lot of large tables. (Of course InnoDb takes longer than MYISAM tables to load.) I think everyone needs to write a check and repair script for their MySQL database. Run the Check script daily to see if and when the error occurs or twice a day if you're really paranoid. Then have a recovery procedure written down so someone can follow it. And of course log the error report in a file so you can try and determine what caused it and how often it occurs.
Is there a faster way to fix these corruptions than to dump and reload the tables?
I'm not sure because I don't use InnoDb. I'm sure Heikki would know.
Mike
-keith
>>Hi Marc, >> >>Thanks for you response. In answer to your questions, there are no >>embedded newlines and I did look at index issues. I did not try >>rebuilding the index, which would be easy to do in this case since >>the tables are small (unlike a couple of my other tables that have >>125+ million rows and changing indexes is measured in days instead >>of minutes). >> >>I forgot to mention in my first message that the "select count(*)..." >>is the one that is wrong. Counting all returned elements isn't too >>high, the count(*) is too low. Doing the count(*) on the same exact >>table on a 4.0.17 system (the replication master) gives the correct >>count. >> >>-keith
>Keith, > Try doing a "Check Table tablename". >See http://dev.mysql.com/doc/mysql/en/CHECK_TABLE.html
>Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
