It's been too long to remember how we got here accurately, but we get the following error on MySQL start (though it does still start the service with InnoDB):

[ERROR] InnoDB: Cannot open datafile for read-only: './oldschema/tablename.ibd' OS Error: 71

The table it is referring to used to exist there, but was renamed into a different schema and then dropped, or possibly just dropped (foggy memory) in order to get all references to it to follow into the other schema. Ultimately, the table was drop-swapped for one intentionally established as a copy via replication.

It doesn't appear in information schema, doesn't appear in the SHOW TABLES list, can't be queried with SELECT statements, and DROP/ALTER TABLE for that name in that schema (after getting a view out of the way) just gets us:

mysql> drop table oldschema.tablename;
ERROR 1051 (42S02): Unknown table 'oldschema.tablename'
mysql> ALTER TABLE oldschema.tablename DISCARD TABLESPACE;
ERROR 1146 (42S02): Table 'oldschema.tablename' doesn't exist

...exactly as we expect. There is, however, now a view by the same name in its place (which refers to the new table in the other schema), and it has a corresponding .frm file, and of course, no .ibd file, as we would expect.

Further, the table in the new schema works just as expected, as does the view in the old schema that aliases it. In fact, as far as we can tell, this is causing no problems...we just want to stop worrying whoever sees the log. We can even create and drop a table in its place (after moving the view and before putting it back), and still get that error.

So why does MySQL expect an ibd file by that name in the old schema?

How do we convince it to forget?



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.