MySQL InnoDB 表损坏，显示错误： “MySQL is trying to open a table handle but the .ibd file for table ### does not exist”

适用于：

MySQL服务器版本4.0及以上

本文信息适用于所有平台。

 

症状

当尝试访问表test.t1时，出现以下错误：

 

150512 16:30:01 [ERROR] MySQL is trying to open a table handle but the .ibd
file for
table test/t1 does not exist.
Have you deleted the .ibd fil e from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See http://dev.mysql.com/doc/refman/5.1/en/innodb‐troubl eshooting.html
how you can resolve the problem.

 

原因

这个问题的原因是缺少相关表t1中的.ibd文件。这表示没有任何语句可以对表T1运行。

由错误本身以及检查该文件是否存在确认：

 

 

shell> ls ‐lahR /var/lib/mysql/
/var/lib/mysql/test:
total 21G
drwx‐‐‐‐‐‐ 2 mysql mysql 32K may 12 03:55 .
drwxr‐xr‐x 7 mysql mysql 4,0K nov 10 2014 ..
...
‐rw‐rw‐‐‐‐ 1 mysql mysql 8,6K may 12 03:16 t.frm
‐rw‐rw‐‐‐‐ 1 mysql mysql 20M may 12 13:37 t.ibd
‐rw‐rw‐‐‐‐ 1 mysql mysql 8,5K ago 10 2014 t1.frm
‐rw‐rw‐‐‐‐ 1 mysql mysql 8,5K may 12 03:16 t2.frm
‐rw‐rw‐‐‐‐ 1 mysql mysql 128K may 12 03:16 t2.ibd

 

 

进一步查看MySQL错误日志，当错误开始出现，即truncate table在运行时，就能看到。该语句无法重建各自的.ibd文件，所以文件丢失：

 

 

140810 5:05:03 InnoDB: TRUNCATE TABLE test/t1 failed to create a new tablespace

 

解决方案

重建表：

 

Drop table:

mysql> DROP TABLE test.t1;
The following error may be seen in the error log afterwards, but it's basically saying the
tablespace was still present in InnoDB Data Dictionary and that eventually was removed:
150513 14:49:08 InnoDB: Error: table 'test/t1'
InnoDB: in InnoDB data dictionary has tables pace id 607381,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but t he
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql .com/doc/refman/5.1/en/innodb‐troubleshootingdatadict.html
InnoDB: for how to resolve the issue.
InnoDB: We removed now the InnoDB int ernal data dictionary entry
InnoDB: of table `test`.`t1`.

 

2. 再次Create table
3. 测试表的访问来确认现在能运行 (ex: optimize table)

 

mysql> OPTIMIZE TABLE test.t1;

 

参考

https://dev.mysql.com/doc/en/glossary.html#glos_ibd_file

https://dev.mysql.com/doc/en/truncatetable.html

https://dev.mysql.com/doc/en/droptable.html

https://dev.mysql.com/doc/en/createtable.html

https://dev.mysql.com/doc/en/innodbtroubleshooting.html

Keywords

DATA DICTIONARY; DISCARD; ERROR; FILES; FRM; MISSING; MYSQL; TABLESPACE; TRUNCATE TABLE