I'm trying to find out what exactly is happening here. I've been intentionally corrupting index pages in InnoDB tables, changing bytes in user record areas that actually contain information (largest changed section being 40 bytes so far), then confirming checksum failure with innochecksum.

I go into the mysql command line, execute a few queries to observe that it's crashing, attempt an 'ALTER TABLE ... ENGINE=InnoDB" and observe the failure, then I go back to the shell and confirm one more time that innochecksum still shows a checksum failure on the table.

After all of that, I run "mysqldump --single-transaction db table > dump". It fails of course, with an error like "mysqldump: Couldn't execute 'show table status like 'tablename'': Lost connection to MySQL server during query (2013) ", but then, I run innochecksum again, and the table no longer shows corruption. I jump into MySQL, and I can execute queries on the table without issue. For all intents and purposes, it seems to be completely repaired.

In a test environment, I've done this numerous times (about 20-25 times so far), and where other methods fail, this seems to succeed, and I have no idea why that is. This only seems to occur when I use the "single-transaction" flag. I know that mysqldump is not actually fixing anything (right?), and that InnoDB is likely just recovering the data from the doublewrite buffer or the logs, but WHY is it only doing this when mysqldump --single-transaction is run on the table?

  • Are you corrupting the pages while the server is running? I am asking this because having the pages on the buffer pool (something that mysqldump will produce) may have something to do. The other thing I can think of is --single-transaction executing a COMMIT, which writes to the transaction logs, which in turn modifies something about the recovery process. (I suppose there are no writes at the same time?) – jynus Sep 11 '14 at 14:42
  • Definitely no writes at the same time - I did run into the buffer pool issue, where the pages I corrupted already had been copied to the buffer pool, making it look like nothing had changed. To get around those situations, I would just restart MySQL first to reload the pages, then re-confirm that innochecksum still failed on the table file just in case it did a recovery during the restart. – user1496947 Sep 11 '14 at 14:46
  • So I enabled the general_log query log to see how much information I could get on what mysqldump was doing. It never appears to reach the point where a COMMIT is actually performed, though that could simply be where the actual crash occurred. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ START TRANSACTION UNLOCK TABLES SELECT LOGFILE_GROUP_NAME, ... FROM INFORMATION_SCHEMA.FILES ... 1 Init DB testdb 1 Query SHOW TABLES LIKE 'customer\_corrupt' 1 Query show table status like 'customer\_corrupt' ... – user1496947 Sep 11 '14 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.