Not the answer you need?
Register and ask your own question!

tablespace with that id or name does not exist

knoxvilledbaknoxvilledba EntrantCurrent User Role Beginner
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.</pre>

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.</pre>

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


  • miguelangelnietomiguelangelnieto Member Inactive User Role Beginner

    What version of MySQL are you using?

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

    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
  • knoxvilledbaknoxvilledba Entrant Current User Role Beginner
    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...
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.