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

[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`.