How do I repair an InnoDB table?

Question

We (apparently) had poorly executed of our Solaris MySQL database engine last night. At least some of the InnoDB tables are corrupted, with timestamp out of order errors in the transaction log, and a specific error about the index being corrupted.

We know about the tools available for MyISAM table repairs, but cannot find anything for InnoDB.

Side note: attempting a table optimize (in my attempt to rebuild the corrupted index) causes the database server to crash.

Schifra

Restore from backups. You have backups, right? – Quadriceps

Answer 1

First of all stop the server and image the disc. There's no point only having one shot at this. Then take a look here.

Eyeleen

It seems the link has different content, doesn't it? – Darsey

Answer 2

stop your application...or stop your slave so no new rows are being added

create table <new table> like <old table>;
insert <new table> select * from <old table>;
truncate table  <old table>;
insert <old table> select * from <new table>;

restart your server or slave

Bergeman

Thanks - I found this solution quite useful because it doesn't require the user to restart the server or work outside the MySQL environment – Mcadams

not sure why, but I had to drop the old table and then recreate it using LIKE instead of running truncate in the 3rd step. Fantastic solution. – Translation

Even after truncate, my old table was corrupted. I dropped it and renamed <new table> to <old table> and it fixed everything, THANKS! Note: some rows were lost in the operation....but I guess that they were the corrupted rows, so probably not recoverable in any way – Prizefight

pretty sure you can RENAME <new table> <old table>; – Greater

Answer 3

The following solution was inspired by Sandro's tip above.

Warning: while it worked for me, but I cannot tell if it will work for you.

My problem was the following: reading some specific rows from a table (let's call this table broken) would crash MySQL. Even SELECT COUNT(*) FROM broken would kill it. I hope you have a PRIMARY KEY on this table (in the following sample, it's id).

1. Make sure you have a backup or snapshot of the broken MySQL server (just in case you want to go back to step 1 and try something else!)
2. CREATE TABLE broken_repair LIKE broken;
3. INSERT broken_repair SELECT * FROM broken WHERE id NOT IN (SELECT id FROM broken_repair) LIMIT 1;
4. Repeat step 3 until it crashes the DB (you can use LIMIT 100000 and then use lower values, until using LIMIT 1 crashes the DB).
5. See if you have everything (you can compare SELECT MAX(id) FROM broken with the number of rows in broken_repair).
6. At this point, I apparently had all my rows (except those which were probably savagely truncated by InnoDB). If you miss some rows, you could try adding an OFFSET to the LIMIT.

Good luck!

Buttonhole

Answer 4

Here is the solution provided by MySQL: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Cleromancy

Answer 5

Simply stopping and restarting the SQL server worked in my case.

Hexapla

Answer 6

See this article: http://www.unilogica.com/mysql-innodb-recovery/ (It's in portuguese)

Are explained how to use innodb_force_recovery and innodb_file_per_table. I discovered this after need to recovery a crashed database with a single ibdata1.

Using innodb_file_per_table, all tables in InnoDB will create a separated table file, like MyISAM.

Omnivorous

Answer 7

Note: If your issue is, "innodb index is marked as corrupted"! Then, the simple solution can be, just remove the indexes and add them again. That can solve pretty quickly without losing any records nor restarting or moving table contents into a temporary table and back.

Kunming

Answer 8

Alter table to myisam and the alter again to innodb, it works for me with a 13 million table (images from a PACS in a hospital)

Pancake

Answer 9

Step 1.

Stop MySQL server

Step 2.

add this line to my.cnf ( In windows it is called my.ini )

set-variable=innodb_force_recovery=6

Step 3.

delete ib_logfile0 and ib_logfile1

Step 4.

Start MySQL server

Step 5.

Run this command:

mysqlcheck --database db_name table_name -uroot -p

After you have successfully fixed the crashed innodb table, don't forget to remove #set-variable=innodb_force_recovery=6 from my.cnf and then restart MySQL server again.

Mesh

Downvoting as you clearly have no idea what you are doing. Deleting the ib_logfiles is just inviting yourself into more problems – Aerialist

In any case: innodb_force_recovery=6 is no fun and should avoided if possible - always try lower values first! – Afterwards