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.
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.
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
).
CREATE TABLE broken_repair LIKE broken;
INSERT broken_repair SELECT * FROM broken WHERE id NOT IN (SELECT id FROM broken_repair) LIMIT 1;
LIMIT 100000
and then use lower values, until using LIMIT 1
crashes the DB).SELECT MAX(id) FROM broken
with the number of rows in broken_repair
).OFFSET
to the LIMIT
.Good luck!
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.
Here is the solution provided by MySQL: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
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
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.
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
).
CREATE TABLE broken_repair LIKE broken;
INSERT broken_repair SELECT * FROM broken WHERE id NOT IN (SELECT id FROM broken_repair) LIMIT 1;
LIMIT 100000
and then use lower values, until using LIMIT 1
crashes the DB).SELECT MAX(id) FROM broken
with the number of rows in broken_repair
).OFFSET
to the LIMIT
.Good luck!
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.
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.
Here is the solution provided by MySQL: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
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.
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
Source: Stackoverflow.com