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.


PHPers Summit 2024 Speaker

International PHP Conference
Munich, November 2024

In November 2024, I'll be giving a talk at the International PHP Conference in Munich, Germany. I'll be talking about the page speed quick wins available for backend developers, along with the challenges of policing dangerous drivers, the impact of TV graphics on web design, and the times when it might be better to send your dev team snowboarding for 6 months instead of writing code!

Get your ticket now and I'll see you there!


Share This Article

Related Articles


Lazy loading background images to improve load time performance

Lazy loading of images helps to radically speed up initial page load. Rich site designs often call for background images, which can't be lazily loaded in the same way. How can we keep our designs, while optimising for a fast initial load?

Idempotency - what is it, and how can it help our Laravel APIs?

Idempotency is a critical concept to be aware of when building robust APIs, and is baked into the SDKs of companies like Stripe, Paypal, Shopify, and Amazon. But what exactly is idempotency? And how can we easily add support for it to our Laravel APIs?

Calculating rolling averages with Laravel Collections

Rolling averages are perfect for smoothing out time-series data, helping you to gain insight from noisy graphs and tables. This new package adds first-class support to Laravel Collections for rolling average calculation.

More