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

User · Answer

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

User · Answer

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

User · Answer

stop your application   or stop your slave so no new rows are being added  create table  lt new table gt  like  lt old table gt   insert  lt new table gt  select   from  lt old table gt   truncate table   lt old table gt   insert  lt old table gt  select   from  lt new table gt     restart your server or slave

User · Answer

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

User · Answer

Here is the solution provided by MySQL  http   dev mysql com doc refman 5 5 en forcing-innodb-recovery html

User · Answer

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

User · Answer

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     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   CREATE TABLE broken repair LIKE broken  INSERT broken repair SELECT   FROM broken WHERE id NOT IN  SELECT id FROM broken repair  LIMIT 1  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   See if you have everything  you can compare SELECT MAX id  FROM broken with the number of rows in broken repair   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     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   CREATE TABLE broken repair LIKE broken  INSERT broken repair SELECT   FROM broken WHERE id NOT IN  SELECT id FROM broken repair  LIMIT 1  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   See if you have everything  you can compare SELECT MAX id  FROM broken with the number of rows in broken repair   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

User · Answer

Here is the solution provided by MySQL  http   dev mysql com doc refman 5 5 en forcing-innodb-recovery html

User · Answer

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

User · Answer

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

User · Answer

stop your application   or stop your slave so no new rows are being added  create table  lt new table gt  like  lt old table gt   insert  lt new table gt  select   from  lt old table gt   truncate table   lt old table gt   insert  lt old table gt  select   from  lt new table gt     restart your server or slave

[mysql] How do I repair an InnoDB table?

Examples related to mysql

Examples related to innodb

Examples related to repair