OperationalError: Table [some table] already exists when running migration to create it

When

2020-10-29

Context

Trying to fix an edxapp database in my local mysql container.

This page may help you if you see errors along the lines of

1 django.db.utils.OperationalError: (1050, "Table 'enterprise_licensedenterprisecourseenrollment' already exists")

when running migrations. That is, running a migration to create a new table raises an error saying that the table already exists, even though you can’t see it from the mysql client.

What went wrong

I did something like the following -

  • Ran make dev.backup at some recent time, like 2020-10-28

  • <something possibly destructive, maybe???>

  • Ran make dev.restore, and then encountered this symptom when running enterprise migrations:

    1 django.db.utils.ProgrammingError: (1146, "Table 'edxapp.enterprise_historicalenterpriseanalyticsuser' doesn't exist")

(it was a migration to create the historical table and it’s non-historical counterpart).

  • Eventually got this slightly more informative error:

    1 django.db.utils.OperationalError: (1813, "Tablespace for table '`edxapp`.`enterprise_historicalenterpriseanalyticsuser`' exists. Please DISCARD the tablespace before IMPORT.")

…which seems to have to do with errorneous/stray .idb files in /var/lib/mysql/edxapp. But manually deleting didn’t resolve the issue.

Things we did to fix it

I had a “clean” (no dev data) edxapp database when we first started investigating. We found this solution for getting around the stray .idb files problem:

  • Get a SQL dump of the database, from the mysql container, and kill the edxapp database with fire:

    1 2 3 4 mysqldump --databases edxapp > /var/lib/mysql/edxapp-backup.sql rm -rf /var/lib/mysql/edxapp/ # in the mysql client DROP DATABASE edxapp; # this may no longer exist, which is fine.
  • Stop/remove/restart the mysql container, and then

    1 2 make mysql-shell $ mysql < /var/lib/mysql/edxapp-backup.sql
  • And then we could successfully run the new enterprise migration. So our loose hypothesis was that the mysql server got in some weird state (due to hidden files or something) about the edxapp database. Dumping the contents of it as pure SQL, removing the datafiles and the logical database, then restoring from pure SQL… seemed to fix it.

So now I had a clean edxapp DB with no useful dev data. But!

…Lael had a good mysql.tar.gz backup file from the prior day. We tried running make dev.restore, which succeeded in restoring the mysql data directory (dev.restore just copies /var/lib/mysql wholesale from the backup archive into the container/volume). It failed to restore mysql57 and quit. This got us a semi-good database, with two problems:

  • it thought that all enterprise migrations were applied. They weren’t, and…

  • none of the enterprise app tables existed in the database.

Manually examining the mysql.tar.gz files (after tar extracting/uncompressing) revealed the presence of the enterprise table files, so we tried again, only this time, we commented out everything in dev.restore except for the command that restores the mysql container. This worked, and we had enterprise tables again, but ran back into the original problem of stale files that “tricked” mysql into thinking that some tables already existed in the edxapp DB, even though they didn’t (when trying to apply migrations again).

But! Now we know how to fix that, using the mysqldump → wipe the edxapp DB with fire → restore it from the pure SQL backup.

After that, we still had a DB full of useful dev data, and running the enterprise migrations worked!

Other things we tried that did not work:

  • the tables weren’t in INFORMATION_SCHEMA.tables

  • nor was anything in INFORMATION_SCHEMA.tablespaces

  • nor did FLUSH TABLES help

  • nor simply restarting the server