I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and restarting replication, I'm getting InnoDB crashes with the error message "Invalid pointer …"
These errors have happened across 2 servers with different hardware and O/S. After running:
ALTER TABLE .... COALESCE PARTION n;
the problem goes away for that table.
My question is larger in scope, though, and that is "How do you identify InnoDB table corruption?" or rephrased "How do you assess InnoDB table health?" Is "CHECK TABLE" the only tool available to identify problems pre-crash?
Not sure if it matters, but the crashes occurred running:
Version: '5.5.15-55-log' socket: '/opt/mysql.sock' port: 3306 Percona Server (GPL), Release rel21.0, Revision 158
Best Answer
Morgan gives a hint in his comment that InnoDB is constantly checking for corrupted pages by doing checksums on the pages it reads. If InnoDB finds a checksum mismatch, it will
crashstop the server.If you want to speed that process up (instead of waiting for InnoDB to read the corrupted page), you can use
innochecksum
:An interesting caveat:
So yes, for an online table
CHECK TABLE
is probably the tool (or as pointed out in another answermysqlcheck
if you want to do more than a single database at a time.)If you can shut down your database you can force it the checksums using
innochecksum
Anecdotal: On a innodb tablespace of 29GB (with
innodb_file_per_table=1
), this script took about 2 MinutesAs a bonus, though, since you are running Percona, they implemented a new method for fast innodb checksum. I've never used it, but it might speed up the process.