1

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?

1

This sounds like all your .ibd file have been detached. Why ?

Please look at the InnoDB Architecture from Percona CTO Vadim Tkachenko

InnoDB Plumbing

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

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

  • Your datadir is /var/lib/mysql
  • Your have a database called mydb
  • You have a table called mytable

If you create an InnoDB table called mytable in the mydb database

  • You get two files
    • /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:

  • You make a backup of the database folder /var/lib/mysql/mydb with the tablespace_id of 27 on that one table
  • You drop the table and create it (TRUNCATE TABLE internally does the same thing), ending up with tablespace_id of 28
  • You restore the backup of the database folder /var/lib/mysql/mydb with the tablespace_id of 27 on that one table

This causes the detachment because table mydb.mytable has the old tablespace_id of 27 while the data dictionary within ibdata1 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.

0

Looking through the logs there was a mismatch between the ids and the ibdata1 in that the first letter of the database name was different case. I have the vaguest memory of late night fiddling. My bad!

After a reboot due to OSX update the server could no longer find the tables in the cache (d'oh!) and spat. Fixed the name and all is well. Thanks to those who considered my problem, but I just knew it had to be something simple.

-1

Please check error logs , if it is showing error in .frm file , your tables are corrupted. cd /var/lib/mysql/mysql .FRM => It has the table structure of your table or table definition .MYI => It has the indexes of your table .MYD => It contains your data

And also check mysql table status using mysqlcheck table option. If it is showing status error your tables are corrupted

  • 2
    .MYD and .MYI are files for MyISAM; the OP is using InnoDB and has mentioned .ibd. – Rick James Sep 30 '16 at 16:29
  • Useful to the extent of stating the often forgotten bleeding obvious "check error logs", even if the rest of the answer was off the mark. All the clues were in the logs. – Berniev Oct 1 '16 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.