I recently messsed up a migration to table-per-file for my dbs. In the end I (thought) I dropped all dbs, deleted any left over files, and then started over from scratch, importing only dbs I needed as I went.
However, today I find loads of these in my log:
InnoDB: Error: table 'db/table'
InnoDB: in InnoDB data dictionary has tablespace id 384,
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.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
Going to the URL, I find near the end.
If this occurs, try the following procedure to resolve the problem:
Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.
Issue DROP TABLE for the original table. That should successfully drop the table and InnoDB should print a warning to the error log that the .ibd file was missing.
What does "Create a matching .frm file" mean? Since it directly speaks of creating a "file", I would assume this is something like touch [file]
, but then it doesn't make any sense that I should copy it from one directory to another.
I tried it anyway and mysql complained about the table not existing, so that's not it.