How to resolve “InnoDB data dictionary has tablespace id N” [duplicate]

Question

This question already has an answer here:

• How to Recover an InnoDB table whose files were moved around 2 answers

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.

Answer 1

Creating a table in a new database with the same name as the "missing" table, and copying the corresponding file was the solution.

Hopefully someone can post a better solution, but here's the hack I put together to sort this out for myself. Really ugly, but still might benefit someone in the future.

grep "InnoDB: Error: table " /var/log/mysql/error.log
  | grep 141028 # Today's date
  | grep [problematic-database]
  | sort -u
  | awk '{ print $6 }'
  | awk -F/ '{ print $2 }'
  | sed "s/'$//"
  | while read table;
  do
    echo "use temp; create table if not exists $table (random varchar(2))"
    | mysql
    cp -v temp/"$table".frm [problematic-database]/;
    echo "drop table temp.$table; drop table [problematic-database].$table"
    | mysql
  done