How to recreate an InnoDB table after the tablespace has been removed

Nov 2, 2011 / By Sheeri Cabral

Tags: ,

Does your error log ever get flooded with errors like this one?

[ERROR] MySQL is trying to open a table handle but the .ibd file for
table my_schema/my_logging_table doesnot exist.
Have you deleted the .ibd file from thedatabase directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See how you can resolve the problem.

No? That is great!

We had a case where, in order to quickly solve a disk space issue, a SysAdmin decided to remove the biggest file in the filesystem, and of course this was an InnoDB table used for logging.

That is, he ran:

shell> rm /var/lib/mysql/my_schema/my_logging_table.ibd

He could have run TRUNCATE TABLE, but that’s another story.

The results were not ideal:

The table did not exist anymore.

Errors in the application while trying to write to the table.

MySQL flooding the error log.

The solution for this problem is to:

run DISCARD TABLESPACE ( InnoDB will remove insert buffer entries for that tablespace);

run DROP TABLE ( InnoDB will complaint that the .ibd file doesn’t exist, but it will remove it from the internal data dictionary );

recover the CREATE TABLE statement from the latest backup ( you have backups, right? );

issue the CREATE TABLE statement to recreate the table.


mysql> ALTER TABLE my_logging_table DISCARD TABLESPACE;

Query OK, 0 rows affected (0.05 sec)

In the error log you will see something like:

InnoDB: Error: cannot delete tablespace 251
InnoDB: because it is not found in the tablespace memory cache.
InnoDB: Warning: cannot delete tablespace 251 in DISCARD TABLESPACE.
InnoDB: But let us remove the insert buffer entries for this tablespace.

mysql> DROP TABLE my_logging_table;

Query OK, 0 rows affected (0.16 sec)

In the error log you will see something like:

InnoDB: Error: table ‘my_schema/my_logging_table’
InnoDB: in InnoDB data dictionary has tablespace id 251,
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 the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: for how to resolve the issue.
InnoDB: We removed now the InnoDB internal data dictionary entry
InnoDB: of table `my_schema/my_logging_table`.

And finally:

mysql> CREATE TABLE `my_logging_table` (
(… omitted …)

Query OK, 0 rows affected (0.05 sec)

Of course, the final step – is a stern talking to with the SysAdmin.

Share this article

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>