tablespace with that id or name does not exist

All, while doing some cleanup of disk space recently on a server, I removed a file that seemed to be an old remnant of an alter table that had failed and was left behind. The file was named like “#sql-1fa3_dc3.ibd” and hadn’t been modified according to Linux timestamp for many months. Once the xtrabackup was run on that database, the following error occured during the prepare phase:

121217 4:18:40 InnoDB: Error: table 'db_name/#sql-1fa3_dc3’InnoDB: in InnoDB data dictionary has tablespace id 814,InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.

This is fair enough to me, but I want full confidence this database backup is recoverable, so I want to clear up this problem. I was hoping a simple restart of the database would do the same as xtrabackup prepare would (removed from data dictionary), but alas, it only complains.

130122 18:31:45 InnoDB: Operating system error number 2 in a file operation.InnoDB: The error means the system cannot find the path specified.InnoDB: If you are installing InnoDB, remember that you must createInnoDB: directories yourself, InnoDB does not create them.130122 18:31:45 InnoDB: Error: trying to open a table, but could notInnoDB: open the tablespace file ‘./db_name/#sql-1fa3_dc3.ibd’!InnoDB: Have you moved InnoDB .ibd files around without using theInnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?InnoDB: It is also possible that this is a temporary table #sql…,InnoDB: and MySQL removed the .ibd file for this.InnoDB: Please refer toInnoDB: …innodb-troubleshooting-datadict.htmlInnoDB: for how to resolve the issue.

Is there a way to safely remove this tablespace from the data dictionary, like the prepare phase does, but from my actual db instance?

Hi!

What version of MySQL are you using?

I want to check that you are not affected by this bug:

http://bugs.mysql.com/bug.php?id=45976

It is about /tmp but anyway, we should double check it.

Does the db_name directory exists? And the corresponding .frm file of that table?

If the .frm file exists:

  • shutdown mysql
  • remove the .frm
  • start mysql

I am using Percona-Server-server-55-5.5.27-rel28.0.291.rhel6.x86_64

The db_name directory does exist, there are many other tables that are there getting backed up fine. The .frm file does not exist, as I “rm”-ed it at the time I removed the .ibd as well…