I’m sure most of you have seen the following error, and in many cases it’s quite easy to diagnose, as the error message is clear:
111111 11:11:11 [ERROR] MySQL is trying to open a table handle but the .ibd file for table test/t does not exist. Have you deleted the .ibd file 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-troubleshooting.html how you can resolve the problem.
From the client side, you may see something like:
ERROR 1146 (42S02): Table 'test.t' doesn't exist
However, if you know you (or some backup/archive script) did not delete the file, nor did you run ALTER TABLE table DISCARD TABLESPACE, then what else could cause this?
Well, I recently found a case where it could occur when a truncate table (on an InnoDB table using the plugin with no foreign keys – so fast truncate method is used, and innodb_file_per_table is enabled) is issued on a table.
Then, if a crash occurs in the middle of this (after the .ibd file has been deleted, but before the re-creation process completes), then you can end up with this error (after a restart and a subsequent SELECT on this table).
I expect this is quite rare, but can happen nonetheless, so I wanted to add this for those searching for more possibilities of missing .ibd files.
Fwiw, I did add this as a user comment in the manual where this topic is discussed:
Tags: ALTER TABLE DISCARD TABLESPACE, chris calender, deleted the .ibd, ERROR 1146 (42S02): Table doesn't exist, Have you deleted the .ibd file from the database directory, have you used DISCARD TABLESPACE?, innodb, innodb data dictionary, MySQL, skysql, troubleshooting data dictionary errors, troubleshooting-datadict, trying to open a table handle