Looking for the default database schemas? Download them for versions 2.6 through 3.3 here. (MySQL format.)

NOTE: Much of this content is relevant only to MySQL users. Other database users may find some semblance of an elucidation, but they’ll have to hack their own scripts!

Over time and multiple version upgrades of Atlassian Confluence, its database can get a bit “dirty” for various reasons and may cause an upgrade to fail or lead to confusion when contacting Atlassian support (see note). A few examples:

• Prior to version 2.10, the upgrade process wasn’t very “tidy”, shall we say? For example, if the newer version of Confluence didn’t need a data column that the previous version did, the upgrade would just leave the column in place — with all of the newly-unnecessary data intact — and continue on silently and successfully. (Candidly, this behavior may still exist; I haven’t verified.)
• Also prior to version 2.10, there was a fair amount of content on Atlassian’s documentation wiki that espoused user-submitted “performance tips” for the database. Generally speaking, these tweaks were additional indexes, like those documented here: Improving Database Performance (2.8), Improving Database Performance (2.9), and Improving Database Performance (2.10). Unfortunately, some of these were later reviewed by the Atlassian developers and are not included in Confluence 2.10+, but others were included and the upgrade process attempts to apply them. So, if you applied all of these (and maybe some special sauce of your own), your schema may be unsupported and a later upgrade may fail, or you may end up with multiple identical indexes with different names, or a correctly-named indexes with the wrong definitions.
• May Steve Yzerman help you if you’ve previously moved your database from a GNU/Linux to a Windows platform, or from a MSSQL to a MySQL platform (or vice versa either combo), using a tool like the MySQL Migration Toolkit or DBConvert (non-free). A side effect of this technique is that the datatypes on certain columns can be changed in a way that leaves Confluence operational after the migration, but could plague an upgrade at a later date.

All is not lost

Luckily, the procedure to fix this problem is fairly straightforward. At the highest level:

1. Create a new confluence database using the “out of the box” schema DDL for the currently installed Confluence version.
2. Migrate *only* the data from your old “dirty” database to the new “clean” one.
3. Repoint your Confluence installation at the new database in confluence.cfg.xml.
4. Grin a big grin, have a pint of beer, and know your Confluence database schema is shiny and happy, and/or …
5. Continue on with your upgrade.

Creating an “out of the box” schema

Download the Default Confluence database schemas. I’ve included the major versions from 2.6.3 to 3.3. Before using one of these scripts, create a blank schema in MySQL — I recommend the command line client and the examples below assume that’s your choice. Assuming you started the mysql client from the directory where the SQL file is located, execute:

CREATE DATABASE 'my_clean_database';
USE 'my_clean_database';
SOURCE confluence_2.10.4.sql;

OPTIONAL: To do all of this in one step, you could modify the appropriate SQL script (matching your current Confluence version) to create the database for you; just add the necessary statements immediately before the first CREATE TABLE statement and then execute the SOURCE command.

CREATE DATABASE 'my_clean_database';
USE 'my_clean_database';

--
-- Table structure for table `ATTACHMENTDATA`
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ATTACHMENTDATA` (
  `ATTACHMENTDATAID` bigint(20) NOT NULL,
[...]

Migrate your data from the “dirty” schema to the “clean” one

Again, we’ll use some mysql command line tools. Extract the data from your current installation:

$ mysqldump -h YOUR_HOSTNAME -u YOUR_UID -pYOUR_PWD -c -B YOUR_CURRENT_DB -K -e -n -t -y -q --single-transaction > confluence_data.sql

NOTE: Of course, substitute real values for any values above that begin with “YOUR_“. And if you’re curious about all the options, refer to the mysqldump documentation.

Now insert the data into your new, clean schema:

$ mysql -h YOUR_HOSTNAME -u YOUR_UID -pYOUR_PWD my_clean_database < confluence_data.sql

Admittedly, any number of things could go wrong at this point. One of the most likely is that the new, clean schema has fewer data columns than a source table (SPACEKEY column in the CONTENT_LABEL table, anyone?) An easy way around this is to manually add the column to the clean schema, import the data, then later remove the column you manually added. Google that for more info.

At this point, it’s a decent idea to check and analyze your new database.

$ mysqlcheck -h YOUR_HOSTNAME -u YOUR_UID -pYOUR_PWD -c --analyze my_clean_database

Repoint your Confluence installation at the new database

Hopefully, this is self-explanatory. Shutdown Confluence and edit your confluence.cfg.xml file to point at your new database. Start Confluence again, and everything should be just fine.

Other thoughts …

Or, more accurately: Why I had to figure this out in the first place.

The company I work for had a particularly finicky Confluence installation that suffered the consequences of all three bullets I listed at the outset of this post: we had an infestation of extra columns, rogue indexes, and incorrect datatypes in our production database. The problem, in combination with 15GB of attachment data being in our database (bad idea, I know) was significant enough that two different Atlassian partners — one listed here and the other here — failed to migrate and upgrade our Confluence installation after charges of many thousands of dollars. (If you’re interested as to who they were, drop me an email.)

So, even though my buddy, Dave, asserts that there’s a capitalistic opportunity in all of this, I’ll freely share a script that I hacked together to automate everything in this post, with a little sed magick bonus for GNU/Linux fanatics.

Those who see the world as an opportunity to get rich, charge for their knowledge. Those who see the world as an opportunity to make the world rich, share their knowledge.
        – I just made that up … after 5 beers

If you’re reasonably handy with bash shell scripts, I am certain that you can expound and embellish this to suit your own needs. Download the script here. Note that some of the variables in this script are different from those used in the examples in this post and, in general, it performs a slightly different set of tasks. From the comments:

# * Dumps only the data from whatever host/database at which you point it.
#   NOTE: can be configured to ignore attachments (DO_IGNORE_ATTACHMENTS
#   setting) and every performance-enhancing option available is hardwired.
#
# * Renames the schema to 'confluence' and fixes some casing issues that
#   arise when going from GNU/Linux > windows.
#
# * Creates the "clean" 2.10.2 schema wherever you tell it, and applies
#   the delta from our PROD Confluence installation.
#
# * Inserts the data into the new "clean" schema.
#
# * Checks and analyzes the new DB and updates statistics.

I hope this helps someone. As always, please post a comment here or drop an email if you have any questions while taking on your own adventures. Cheers.

A story about Atlassian support

Now, I really shouldn’t post this, because Sarah Maddox may get upset with me :) Hallo, Sarah! She happens to be one of the best Tech Writers at Atlassian, by the way …

However, a year or so ago while attempting to performance tune our Confluence 2.10.2 installation, Dave and I contacted Atlassian support. This is when a vexing mayhem ensued. Our office is in Boston, MA. Atlassian’s US support center is on the west/left coast of the US. Sydney is on the other side of the world.

So, after a long and protracted game of phone tag (on the order of a week or more), we eventually got on the line with the US west coast database Guru. In a nutshell, he adeptly drilled down to the fact that we were running with all of the performance tweaks that the Atlassian Confluence wiki documentation suggested. At which point, he said, “We don’t support those tweaks, so I won’t support your installation.” The ticket was then closed.

True story. Fix yer database schema.