How Do I Fix a Broken Confluence Database after a Failed Upgrade Attempt to Confluence 5.2 and above
Symptoms
After a failed upgrade to Confluence 5.2 and above (from pre-5.2), you will see that all the username references in your Confluence tables, such as the CONTENT table, have been replaced by a long alphanumeric string (also known as the user key). Example:
mysql> SELECT TITLE, CREATOR FROM content WHERE TITLE LIKE '%asd%' AND PREVVER I
S NULL;
+-----------------+----------------------------------+
| TITLE | CREATOR |
+-----------------+----------------------------------+
| sad sasa asd as | 4028a08146a997f40146a99a5fe00003 |
| sad sasa asd as | 4028a08146a997f40146a99a5fe00003 |
| sa dsa asd sa | 4028a08146a997f40146a99a5fe00003 |
+-----------------+----------------------------------+
3 rows in set (0.00 sec)
Before you proceed
Make sure that you have a pre-upgrade database backup that you can restore to. If so, then simply restore that and ignore the following.
We recommend backing up the Confluence Home Directory and the Confluence Database before performing any sort of upgrade
Resolution
The following will restore your database to a working pre-upgrade copy, with all content ownership preserved
Please create a backup of your database before running these!
Run the following queries in the now-broken Confluence Database:
UPDATE logininfo a
JOIN user_mapping u
ON a.USERNAME = u.user_key
SET a.USERNAME = u.username;
If this query returns an error, and since logininfo is not mission-critical, run this instead:
TRUNCATE logininfo;
Run this only if the first query fails!
To fix all the broken content ownership:
UPDATE attachments a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;
UPDATE attachments a
JOIN user_mapping u
ON a.LASTMODIFIER = u.user_key
SET a.LASTMODIFIER = u.username;
UPDATE content a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;
UPDATE content a
JOIN user_mapping u
ON a.LASTMODIFIER = u.user_key
SET a.LASTMODIFIER = u.username;
UPDATE content a
JOIN user_mapping u
ON a.USERNAME = u.user_key
SET a.USERNAME = u.username;
UPDATE content_label a
JOIN user_mapping u
ON a.OWNER = u.user_key
SET a.OWNER = u.username;
UPDATE content_perm a
JOIN user_mapping u
ON a.USERNAME = u.user_key
SET a.USERNAME = u.username;
UPDATE content_perm a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;
UPDATE content_perm a
JOIN user_mapping u
ON a.LASTMODIFIER = u.user_key
SET a.LASTMODIFIER = u.username;
UPDATE extrnlnks a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;
UPDATE extrnlnks a
JOIN user_mapping u
ON a.LASTMODIFIER = u.user_key
SET a.LASTMODIFIER = u.username;
UPDATE follow_connections a
JOIN user_mapping u
ON a.FOLLOWER = u.user_key
SET a.FOLLOWER = u.username;
UPDATE follow_connections a
JOIN user_mapping u
ON a.FOLLOWEE = u.user_key
SET a.FOLLOWEE = u.username;
UPDATE label a
JOIN user_mapping u
ON a.OWNER = u.user_key
SET a.OWNER = u.username;
UPDATE likes a
JOIN user_mapping u
ON a.USERNAME = u.user_key
SET a.USERNAME = u.username;
UPDATE links a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;
UPDATE links a
JOIN user_mapping u
ON a.LASTMODIFIER = u.user_key
SET a.LASTMODIFIER = u.username;
UPDATE notifications a
JOIN user_mapping u
ON a.USERNAME = u.user_key
SET a.USERNAME = u.username;
UPDATE notifications a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;
UPDATE notifications a
JOIN user_mapping u
ON a.LASTMODIFIER = u.user_key
SET a.LASTMODIFIER = u.username;
UPDATE pagetemplates a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;
UPDATE pagetemplates a
JOIN user_mapping u
ON a.LASTMODIFIER = u.user_key
SET a.LASTMODIFIER = u.username;
UPDATE remembermetoken a
JOIN user_mapping u
ON a.username = u.user_key
SET a.username = u.username;
UPDATE spacepermissions a
JOIN user_mapping u
ON a.PERMUSERNAME = u.user_key
SET a.PERMUSERNAME = u.username;
UPDATE spacepermissions a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;
UPDATE spacepermissions a
JOIN user_mapping u
ON a.LASTMODIFIER = u.user_key
SET a.LASTMODIFIER = u.username;
UPDATE spaces a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;
UPDATE spaces a
JOIN user_mapping u
ON a.LASTMODIFIER = u.user_key
SET a.LASTMODIFIER = u.username;
Lastly, drop the user_mapping table:
DROP TABLE user_mapping;
If you receive errors such as these:
ERROR 1146 (42S02): Table 'confluence.attachments' doesn't existChange the query so that the table name is in all capital instead. Example:
UPDATE ATTACHMENTS a
JOIN user_mapping u
ON a.CREATOR = u.user_key
SET a.CREATOR = u.username;