Manually Deleting file and DISCARD TABLESPACE are not the only ways to encounter the “ibd file does not exist” Error

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?
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:


5 thoughts on “Manually Deleting file and DISCARD TABLESPACE are not the only ways to encounter the “ibd file does not exist” Error”

  1. Thanks for sharing. I have occasionally seen an issue myself in which there is a temporary tablespace file missing, and this alert pops up on call to DROP TABLE.

    111117 6:38:17 InnoDB: Operating system error number 2 in a file operation.
    InnoDB: The error means the system cannot find the path specified.
    InnoDB: File name /tmp/#sql198b_2_18.ibd
    InnoDB: File operation call: ‘delete’.

    It’s no more issue than an alert annoyance, but in looking into it haven’t figured out for certain why it’s missing other than to assume that the connection that created the temporary table had dropped off and the table was subsequently auto dropped, but the app later comes back to clean up the temp table and it’s not there. Don’t have much more details on the app than that unfortunately, so just left to assume.

  2. Another *common* case is when one does DROP TABLE for a large table.
    It takes so much time, and brings the DB to a lockdown (issues of flushing pages), that the DBA hits the Ctrl+C key.
    The query is supposedly aborted, but the DB remains at standstill.
    The DBA then kills -9 mysqld.
    MySQL starts, all is well, but the table cannot be created again (since it exists) nor destroyed (since it does not exist).

    On this common scenario I’ve *never* managed to make InnoDB drop or discard the ghost table.

  3. @Sandi and @Shlomi,

    Many thanks for sharing! It’s most appreciated. Not only by me, but by anyone looking for clues on this problem. 🙂

  4. Zardosht; I really eoyjned your talk last night at the SF MySQL Meetup. All though I am new to MySQL I was able to follow along because of your great knowledge of the subject and your ability to explain indexes. Thank you for your insights. Since I am new to MySQL is there any bits of wisdom that you could pass on to me? Are there any books or websites that you recommend that would help me to get up and running at a faster pace? Thank you once again.Gerald Kruse

  5. If it ever come down, you can either :1/ stcwih to Oracle and debug PL/SQL failures2/ ask Brian Aker to handle Drizzle support3/ head to WMF 🙂

Comments are closed.