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 http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html 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.

Example:

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: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
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 …)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.05 sec)

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


Share this article



One Response to “How to recreate an InnoDB table after the tablespace has been removed”

  • Kasi says:

    .ibd file is missing for one of my table. When i am trying to execute discard tablespace for that table ,mysql is getting restarted. Any idea about this?

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=""> <s> <strike> <strong>