1
SHOW TABLES

shows table: foo

DESCRIBE foo

Gives me:

ERROR 1146 (42S02): Table 'foo' doesn't exist

At one point, table foo did exist, but it was dropped because it wasn't needed.

Seems the schema still thinks it's there. How do I fix that?

DrDamnit
  • 203
  • 1
  • 4
  • 12

2 Answers2

2

Here is the quick and dirty answer

Running SHOW TABLES; will go to the OS and check for the existence of .frm files only. I mentioned this 5.5 years ago (MySQL tables do not show up in the phpMyAdmin and MySQLWorkbench)

In your case, there is a file called foo.frm. That file exists in the database folder of your database.

When you try doing things to the table at the storage engine level, that's when you get that message because there are no storage engine level files for the foo table ( no foo.ibd file if the storage engine was InnoDB or no foo.MYD/foo.MYI if the table was MyISAM).

My guess is that someone deleted the storage engine level files for the table but forgot to delete the corresponding .frm.

The solution would be to find foo.frm in the OS and delete it. The information_schema will quickly disavow any knowledge of the foo table.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

I had an issue like this and it turned out to be a Foreign Key issue.

A table was deleted and another was renamed on top of it. The old table still had an FK reference from an existing table. Accessing the table would trigger the error ERROR 1146 (42S02): Table '...' doesn't exist

After dropping the relationships that remained to that table and re-creating them properly things went back to working.

Xealot
  • 101