Some days ago I have a problem with my server and all my innodb tables were locked. And now I want to tell, how I tried to repair these tables.
First of all, I tried to do force recovery. In mysql config file enable force recovery. Set innodb_force_recovery = 1 and try to do mysqldump. If it not work, try to increase innodb_force_recovery value to 6. Default innodb_force_recovery value is 0. After you change innodb_force_recovery value, restart mysql.
But this method did not help me. Other method is simulate InnoDB tables. You will need .ibd file and the CREATE TABLE statement for each table you want to recover using this method. I recommend to do this method on your local computer (use wamp or xamp). Copy all mysql files include b_logfile0, ib_logfile1 and ibdata1 from your server.
Simulate the internal InnoDB table counter. That is, create work tables (with innodb_file_per_table enabled) until you have the internal pointer of table id equal to (1 – id_of_ibd_table_you_need_to_restore).
- Start up clean/fresh instance of MySQL with innodb_file_per_table enabled (delete ib_logfile0, ib_logfile1 and ibdata1 and restart mysql).
- Now, we need to find the table id that MySQL is currently set at, as well as the table id for the table we need to recover.
Note:
Step 2 (2a – 2f) is simply to find the table id that is stored inside of the .ibd file. I’ve written a PHP script to determine this, so using the script can save a bunch of time.
2a. Create a test database:
mysql> CREATE DATABASE test1; mysql> USE test1;
2b. Issue the create table command for the table (table structure must be like your crashed table):
mysql> CREATE TABLE `product` ( `PRODUCT_ID` bigint(20) unsigned NOT NULL auto_increment, `BRAND_ID` int(10) unsigned default NULL, `PRODUCT_TYPE_ID` int(10) unsigned default NULL, `GROUP_ID` int(10) unsigned default NULL, `PRODUCT_NAME` varchar(500) NOT NULL, `DEFAULT_EMAIL_ID` varchar(48) default NULL, `PRODUCT_STATUS` tinyint(1) NOT NULL, `CLIENT_ID` bigint(20) unsigned default NULL, `LAST_MODIFIED_BY` varchar(45) NOT NULL, `LAST_MODIFIED_DATE` datetime NOT NULL, PRIMARY KEY (`PRODUCT_ID`) ) ENGINE=InnoDB;
2c. Discard the tablespace, which will delete the newly created .ibd file:
mysql> ALTER TABLE product DISCARD TABLESPACE;
2d. Copy the pre-existing .ibd file to the datadir/test1 folder from you backuped database files from your server.
2e. Import this tablespace:
mysql> ALTER TABLE product IMPORT TABLESPACE;
This should produce the following error (at least this is most likely). The only way it would not is if MySQL’s current table id matched that of the preexisting ibd table id. In which case, you can now dump your table.
ERROR 1030 (HY000): Got error -1 from storage engine
2f. So, now to check the error log (manually). Look for the following entry:
081010 11:47:40 InnoDB: Error: tablespace id in file '.test1product.ibd' is 1193, but in the InnoDB InnoDB: data dictionary it is 1.
So, now we know the internal table id is at 1, and that of the ibd table is 1193.
3. Clean up working database:
3a. Manually move the ibd file from the $datadir to a safe location (as you will need this file again).
3b. Drop this table.
mysql> DROP TABLE product;
Note this does not re-set the internal table counter.
4. You’ll need to create the number of tables you need to increase the internal table id value.
In this case, you’d create 1191 test InnoDB tables (already at 1, and need to leave 1 for the actual table, so 1193-2=1191). Run below in a loop.
for ($1=1; $i<=1191; $1++) {
CREATE TABLE test1# (id int) ENGINE=InnoDB;
}
I accomplished this via a simple php script.
<div id="_mcePaste">$dbhost = "localhost";</div>
<div id="_mcePaste">$dbname = "test1";</div>
<div id="_mcePaste">$dbuser = "root";</div>
<div id="_mcePaste">$dbpwd = "";</div>
<div id="_mcePaste">ini_set("max_execution_time", "3600");</div>
<div id="_mcePaste">$con = mysql_connect($dbhost,$dbuser,$dbpwd) or die(mysql_error());</div>
<div id="_mcePaste">mysql_select_db($dbname , $con) or die("Select DB Error: ".mysql_error());</div>
<div id="_mcePaste">for ($i = 1; $i <= 1191; $i++) {</div>
<div id="_mcePaste">$dbquery = "CREATE TABLE test1.product" . $i . " (id int) ENGINE=InnoDB";</div>
<div id="_mcePaste">echo "" . $dbquery . "";</div>
<div id="_mcePaste">$result = mysql_query($dbquery, $con) or die(mysql_error());</div>
<div id="_mcePaste">print_r($result);</div>
<div id="_mcePaste">$j = 0;</div>
<div id="_mcePaste">while($row = mysql_fetch_array($result)) {</div>
<div id="_mcePaste">$j++;</div>
<div id="_mcePaste">echo $row[0];</div>
<div id="_mcePaste">}</div>
<div id="_mcePaste">}</div>
<div id="_mcePaste">mysql_close();</div>
5. After these are created, go ahead and drop this database and all tables (as they are not needed).
DROP DB test1;
6. Now, re-perform steps 2a through 2e.
mysql> CREATE DATABASE test1; mysql> USE test1; mysql> CREATE TABLE `product` ( ... ) ENGINE=InnoDB; mysql> ALTER TABLE product DISCARD TABLESPACE; <-- Here is where you copy back the original ibd file to /$datadir/test1/ --> mysql> ALTER TABLE product IMPORT TABLESPACE;
Success!
mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | product | +-----------------+ 1 row in set (0.00 sec)
7. Now, dump the table using mysqldump, and then you can import this to any MySQL instance. Note, you must dump this and re-import it, or you’ll run into problems.
However, it’s possible to encounter crashes and/or reports of corruption in the logs.
If this happens, try to force innodb recovery (which is most likely), and then dump the table.
Start by setting innodb_force_recovery=1 (and try 2,3,4,5,6) until the dump works.
For this example table, I had to set innodb_force_recovery=5 before the dump would succeed.
The # in the output file name is the value I had innodb_force_recovery set to when trying to perform the dump:
C:Program FilesMySQLmysql-5.0.68bin> mysqldump -uroot -P3385 test1 product > product_dump1.txt mysqldump: Couldn't execute 'show table status like 'product'': Lost connection to MySQL server during query (2013) C:Program FilesMySQLmysql-5.0.68bin> mysqldump -uroot -P3385 test1 product > product_dump2.txt mysqldump: Couldn't execute 'show table status like 'product'': Lost connection to MySQL server during query (2013) C:Program FilesMySQLmysql-5.0.68bin> mysqldump -uroot -P3385 test1 product > product_dump3.txt mysqldump: Couldn't execute 'show table status like 'product'': Lost connection to MySQL server during query (2013) C:Program FilesMySQLmysql-5.0.68bin> mysqldump -uroot -P3385 test1 product > product_dump4.txt mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `product`': Lost connection to MySQL server during query (2013) C:Program FilesMySQLmysql-5.0.68bin> mysqldump -uroot -P3385 test1 product > product_dump5.txt C:Program FilesMySQLmysql-5.0.68bin> mysqladmin -u root -P 3385 shutdown C:Program FilesMySQLmysql-5.0.68bin> mysqldump -uroot -P3385 test1 product > product_dump6.txt
In fact, in this case, I could have simply started with 5. This is because the error log stated this:
InnoDB: Error: trying to access update undo rec field 19 in index PRIMARY of table test1/product InnoDB: but index has only 12 fields
So, I knew there was a problem trying to look at the undo logs, and from the manual, a setting of 5 says this:
“Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed”
However, it’s best to start at 1 and work your way forward so as to prevent as much data loss as possible.
After you success table recover, again remove ib_logfile0, ib_logfile1 and ibdata1 and restart mysql. Now you again have fresh mysql and table id will start from 0. Do not forget to set innodb_force_recovery = 0 before restart mysql.
I hope it will help for someone. Please comment if you have any questions.