Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
Hi Randy! I think the responses here are credible - InnoDB identified its own corruption. Maybe you should rephrase your question, why would what you doing cause InnoDB to be corrupt? –  Morgan Tocker Sep 30 '11 at 2:47
add comment

4 Answers

up vote 10 down vote accepted
+100

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 crash stop the server.

If you want to speed that process up (instead of waiting for InnoDB to read the corrupted page), you can use innochecksum:

Because checksum mismatches will cause InnoDB to deliberately shut down a running server, it can be preferable to use this tool rather than waiting for a server in production usage to encounter the damaged pages.

An interesting caveat:

innochecksum cannot be used on tablespace files that the server already has open. For such files, you should use CHECK TABLE to check tables within the tablespace.

So yes, for an online table CHECK TABLE is probably the tool (or as pointed out in another answer mysqlcheck 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 Minutes

#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
  innochecksum -v $i
done

As 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.

share|improve this answer
1  
Gonna give it a try here, seems to be the solution @randymelder is looking for +1 –  marcioAlmada Sep 30 '11 at 1:26
2  
Percona server has some other nice features. See innodb_corrupt_table_action percona.com/doc/percona-server/5.5/reliability/… (!!) –  Morgan Tocker Sep 30 '11 at 3:01
    
@DTest : innochecksum is the way to go. This one's a keeper. +1 !!! –  RolandoMySQLDBA Sep 30 '11 at 10:15
    
@DTest : Hats off to you today on this one !!!! –  RolandoMySQLDBA Sep 30 '11 at 10:16
    
@MorganTocker Interesting. Going to have to get my knowledge vacuum and do some research into percona –  Derek Downey Sep 30 '11 at 11:17
show 3 more comments

According to MySQL 5.0 Certification Study Guide, Page 443,444 Section 30.4:

You can check InnoDB tables by using the CHECK TABLE command or using a client program to issue the statement for you. However, if an InnoDB table has problems, you cannot fix it by using REPAIR TABLE because that statement applies only to MyISAM.

If a table check inidicates that an InnoDB table has problems, you should be able to restore the table to a consistent state by dumping it with mysqldump, dropping it, and recreating it from that dump.

In the event of a crash of a MySQL Server or on the host on which it runs, some InnoDB tables might need repairs. Normally, it suffices simply to restart the server because the InnoDB storage engine performs auto-recovery as part of its startup sequence. In rare cases, the server might not start up due to the failure of InnoDB auto-recovery. If that happens, use the following procedure:

  • Restart the server with the --innodb_force_recovery option set to a value in the rage from 1 to 6. These values indicate increasing levels of caution in a avoiding a crash, and increasing levels of tolerance for possible inconsistency in the recovered tables. A good value to start with is 4.

  • When you start the server with --innodb_force_recovery set to a non-zero value, InnoDB treats the tablespace as read-only. Consequently, you shouyld dump the InnoDB tables with mysqldump and then drop them while the option is in effect. Then restart the server without the --innodb_force_recovery option. When the server comes up, recover the InnoDB tables from the dump files.

  • If the preceding steps fail, it's necessaary to restore the InnoDB tables from a previous backup.

Please read MySQL Docs on InnoDB Forced Recovery

share|improve this answer
1  
FWIW, the certification guide has a very politically correct answer :) If you CHECK TABLE on an InnoDB table and it actually is corrupt, it will never return as "corrupt", it will crash the server. The statement is almost obsolete in InnoDB, because every time you read InnoDB pages it is checking for corruption (via page checksums). –  Morgan Tocker Sep 27 '11 at 21:19
    
@Morgan : Thank you for the update. –  RolandoMySQLDBA Sep 27 '11 at 23:57
add comment

WARNING: before trying any of these instructions it is strongly recommended to verify that there is a healthy backup of your database in hands, just in case. (thanks to @Nick for the warning)

Try to use the mysqlcheck command. On a terminal:

mysqlcheck -u username -p --databases database1 database2

This command will output a list of all tables and a status telling you if there was some kind of corruption:

table1  OK
table2  OK
table3  OK
tableN  OK

With that in hands you will already know which tables you have to repair. Just in case you want to repair everything at once:

mysqlcheck -u username -p --auto-repair --databases database1 database2 

More about mysqlcheck: http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html

Note: you tagged your question with . I had no clue of what was that so I googled. It seems to be a fork of MySQL, but I have no reason to believe the commands are incompatible (fingers crossed).


Somebody pointed me to this guide that has more specific instructions for InnoDB database recovery for more critical situations where the entire database does not start: http://www.softwareprojects.com/resources/programming/t-how-to-fix-mysql-database-myisam-innodb-1634.html

share|improve this answer
    
mysqlcheck is a synonym for 'check table ...' -1 –  randy melder Sep 27 '11 at 17:31
    
    
Not true. First, mysqlcheck is a command-line utility and CHECK TABLE is a SQL statement (it's like to compare orange to lemmons).Also, you CAN'T check an entire database with CHECK TABLE without including ALL the table names in the SQL statement (it would not be very productive too) –  marcioAlmada Sep 27 '11 at 19:03
    
And mysqlcheck has an option to --auto-repair the tables that are corrupted while CHECK TABLE only checks if the tables are corrupted or not, but can't make any repair. –  marcioAlmada Sep 27 '11 at 19:04
1  
@randymelder - You are wrong to say mysqlcheck is a synonym for CHECK TABLE. The documentation you linked to states: "mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed." That is not a synonym; that is a user interface to a collection of statements. –  Nick Chammas Sep 29 '11 at 21:50
show 6 more comments

I wonder what happens if anyone uses InnoDB data created via InnoDB Plugin and then switch to another version of InnoDB. That could create possible page corruption in the eyes of mysqld.

Note what the MySQL Documentation on InnoDB File Format says about this possibility :

In general, a newer version of InnoDB may create a table or index that cannot safely be read or written with a prior version of InnoDB without risk of crashes, hangs, wrong results or corruptions. The InnoDB Plugin introduces a new mechanism to guard against these conditions, and to help preserve compatibility among database files and versions of InnoDB.

I would scrap the data on the slave. In fact, I would just use brute force by getting a logical dump (mysqldump) of the data:

  • Drop all databases using InnoDB on the slave
  • Shutdown mysql on the slave
  • Delete ibdata1, ib_logfile0, and ib_logfile1 on the slave
  • Start mysql on the slave, letting ibdata1, ib_logfile0, and ib_logfile1 get recreated
  • mysqldump the data from the master into the slave

My original anwser posted is considered 'old school'. Yet, in this case, I would definitely look into the file formats being used by .ibd and/or ibdata1.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.