Schrödinger's Paradox in MySQL

If you have ever dived deeper into physics and quantum mechanics in particular, you have probably heard of this weird phenomena called the Schrödinger's cat. In a nutshell, a Schrödinger's cat is a thought experiment performed by an Austrian-Irish physicist Erwin Schrödinger in 1935 in a discussion with Albert Einstein: the experiment illustrates a hypothetical cat that may simultaneously be considered both alive and dead as a result of being linked to a random subatomic event that may or may not occur.

The Schrödinger's Paradox in MySQL

As already mentioned above, the Schrödinger's paradox illustrates something that is simultaneously considered both alive and dead, so one might wonder how might this experiment be relevant to MySQL? In general, everything is pretty simple: such an issue might appear when the power is interrupted in the middle of an operation performed by MySQL: for example, in the middle of an import operation. While relying on ACID properties might help, they might not be of much help after all: these sets of properties are indeed intended to guarantee data validity despite errors, power failures and other mishaps, but if after the failed operation you perform some sort of operations on the table in question and they fail, ACID might not be at fault here. You may be faced with a different problem: the Schrödinger's paradox in MySQL.

In order to confirm you're dealing with a Schrödinger's table in MySQL, perform these steps:

1. Issue a SHOW TABLES; statement on your MySQL instance, look for your table - the table should not be in the list.
2. Try to create the table in question (use the same name) - run a CREATE TABLE statement. MySQL should return an error that the table already exists:ERROR 1050 (42S01): Table 'demo_table' already exists
3. Try to drop the table in question - run a DROP TABLE statement. MySQL should return an error that the table does not exist:ERROR 1146 (42S02): Table 'demo_table' doesn't exist

If you cannot create the table, dropping it doesn't work either, but you are able to see the files in the /var/lib/mysql directory, you are probably dealing with a Schrödinger's table in MySQL.

That might be the case because the data file is missing in the data directory, but the table format (also called definition) .frm file exists or vice-versa. In a nutshell, it seems like InnoDB still has an entry for such a table in the tablespace (the ibdata1 file).

Getting Rid of Schrödinger's Tables in MySQL

Now that you have confirmed that you're dealing with a Schrödinger's table, it's time to get rid of it. In order to do so, perform these steps:

1. Create a table with the same name on another MySQL instance.
2. Copy the .frm file of the table to the database directory on the server where you're facing the Schrödinger's table issue. Make sure the file ownership and permissions are correct.
3. Issue a SHOW TABLES statement in MySQL: you should now see the Schrödinger's table. All operations should now work as normal (i.e you can drop the table if you wish).

Bear in mind that simply deleting the .ibd files that are present in the directory will not work because MySQL will simply recreate the files.

Another way to fix the issue would be as follows (make sure you have backups of your data before performing these steps though):

1. Delete all of your databases excluding mysql.
2. Shut down MySQL.
3. Delete the ibdata1 and ib_logfile* files.
4. Restart MySQL. The tablespace and logs should be recreated.
5. Restore your database.

In order to ensure that you no longer have to deal with Schrödinger's tables in MySQL, make sure the power doesn't get interrupted when any MySQL operations are running.

Summary

Schrödinger's tables in MySQL can be a really frustrating problem. The issue is generally caused by power interruptions when certain operations are running - this way the data file is missing from the data directory, but the table definition files - .frm - exist or vice-versa. Thankfully, these issues are pretty easy to fix and prevent from happening again.