This last week I was working a lot with MySQL databases… Trying to upgrade Confluence and JIRA. In the process of doing so I ended up having to backup and restore the MySQL databases a number of times to try and get the upgrades to work correctly (in my dev environment of course). However, I ran into an odd issue which (for the longest while) stumped both Atlassian Support and myself…
What was happening, was that whenever I would attempt to upgrade Confluence or JIRA, it would fail saying it could not create a table. The error from the MySQL driver was not very specific at first and I could NOT figure out why the error was occurring. Eventually I found some logs saying that the table already existed. Of course, I restored the database and performed a “show tables” and did see the table in the list.
What I finally realized was that the MySQL instance must have been keeping track of some information in between my database restores. It suddenly dawned on me that there was a “mysql” database which keeps track of other “metadata” type information about the databases installed in the MySQL instance.
The problem ended up being that I was performing the backup and restore incorrectly. I was copying the .MDF files from the “confluence” data directory on the production machine and placing them directly in the confluence data directory on the dev machine. That might have worked out OK the first time, but after attempting the upgrade, removing the database data files and re-copying the database data files, it failed.
What I believe happened, was that when I copied the data files the first time, MySQL considered it a new database and gathered all metadata information necessary about the database based on the data files at that time. Then, when I attempted (and failed) my confluence upgrade, the upgrade had successfully created tables that got stored in the MySQL metadata. When I removed the data files and restored the original ones, the MDF files for the newly created tables were gone (thus not showing up in “show tables”) but metadata information still existed in the “mysql” database for those tables and caused problems when confluence attempted to create the new tables in my next attempts at upgrading.
In summary
The lessen learned is to always use the mysqldump command-line for backing up database data, and drop the database with “drop database XXX” and re-create the database with “create database XXX” prior to restoring; and do this every time. Simply copying the database data files is not sufficient.