I have four databases each holding several hundred tables.
Every table in one database suddenly returns error code 1146 Table xxx does not exist. The other databases are fine. I can see all the .ibd and .frm files ok and their various file sizes suggests they are not empty. There is an ibdata1 as well.
From workbench I tried Analyse Table, which indicates InnoDB: Tablespace is missing for table '…' and going on to 'Doesn't exist'.
I created new test tables and they work fine.
OSX was updated the other day, but as the other databases are fine I can't see a connection.
I haven't manually moved/copied etc any files etc.
Something has gone awry, but what?
Best Answer
This sounds like all your
.ibd
file have been detached. Why ?Please look at the InnoDB Architecture from Percona CTO Vadim Tkachenko
ibdata1
has an internal data dictionary that registers every.ibd
by a unique tablespace_id. Somewhere along the line, all the tables in the trouble database have become detach from the data dictionary.I wrote about this years ago
Apr 23, 2012
: How can extract the table schema from just the .frm file?Dec 21, 2011
: Table compression in InnoDB?To rightfully give credit where credit is due, I learned about this concept of detached
.ibd
files and trying to connect them back to the data dictionary from a blogpost written by Chris Calendar back in Feb 2009. I once helped client recover 30 tables with the information from his post (See my post ERROR Cannot find or open table? on what I did to help that client). Please read his biog and then my posts on what you need to try.As for the why the table would be come detached, here is an example
/var/lib/mysql
mydb
mytable
If you create an InnoDB table called
mytable
in themydb
database/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd
.ibd
file would be assigned a tablespace_id.If you drop that table and create it again, the table would have a different tablespace_id.
Now, picture this:
/var/lib/mysql/mydb
with the tablespace_id of 27 on that one table/var/lib/mysql/mydb
with the tablespace_id of 27 on that one tableThis causes the detachment because table
mydb.mytable
has the old tablespace_id of 27 while the data dictionary withinibdata1
has 28.Since you said you didn't move or copy anything, then you may have too many InnoDB tables. Each InnoDB table needs two file handles to be opened and accessed.
I hope I have given your the information you need to help you recover your database.