Schrödinger’s Mysql table – does it exist?

Recently I ran in to a particularly odd database problem – a mysql table that both existed and didn’t at the same time. Queries were failing, reporting that the table didn’t exist. Yet when I tried to recreate this Schrödinger’s table, mysql insisted that it already existed, and wouldn’t recreate it.

So what gives?

The error

When trying to query the table, the console returned the following:

mysql> SELECT * FROM mytable;
ERROR 1051 (42S02): Table 'mytable' already exists

mysql> DROP TABLE mytable;
ERROR 1051 (42S02): Unknown table 'mytable'

The mysql error log was a bit more verbose:

140221  8:28:03  InnoDB: Error: table `mydatabase/mytable` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html

The Solution

Digging in to it a bit more, it appeared that this database had it’s data dictionary copied from elsewhere. Apparently with InnoDB tables in particular, there’s a risk of InnoDB’s own copy of the data dictionary getting out of sync with that kept by mysql in .frm files.

This appears to be what happened in this case – at some point, corruption took place, leaving an orphaned data file existing in the data dictionary, but the table definition file was missing.

After going to the data directory and deleting the “.frm” file for the table concerned, order was restored, and the table could be recreated without issue.

Related Articles


Attack of the clones - removing copied websites from the internet

It's an increasingly-common experience for online publishers to discover that their content has been stolen wholesale, and posted on a different domain without permission. What can you do if this happens to your site?

Font Subsetting - shrink down font files to speed up page loads

Fonts are one of the largest resources on any page after images, and can have a big impact on CLS when they vary in size from the underlying system font. Font subsetting allows us to radically shrink font file sizes, speed up initial page loads, and improve our page speed scores.

Avoiding The Google Ads Two-Click Penalty

Google's Two-Click Penalty is intended to protect users and advertisers from accidental clicks on ads. When does Google apply the penalty, and how can we avoid it impacting our sites?

Minimising Cumulative Layout Shift (CLS) When Loading Responsive Ads

Responsive ads are a great way to maximise publisher revenue from display ads. Not knowing the size of the ad to be served in advance can have a big impact on Cumulative Layout Shift (CLS), and, ultimately, Google rankings. How do we maximise revenue while minimising CLS impact?

More