Symptoms

During the upgrade attempt, the following appears in the atlassian-confluence.log:

2011-10-03 16:42:06,800 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask@c384f5 failed during the SCHEMA_UPGRADE phase due to: PreparedStatementCallback; SQL [DELETE FROM CONTENT_PERM_SET WHERE ID = ?]; Cannot delete or update a parent row: a foreign key constraint fails (`confluence40`.`content_perm`, CONSTRAINT `FKBD74B31676E33274` FOREIGN KEY (`CPS_ID`) REFERENCES `content_perm_set` (`ID`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`confluence40`.`content_perm`, CONSTRAINT `FKBD74B31676E33274` FOREIGN KEY (`CPS_ID`) REFERENCES `content_perm_set` (`ID`)) 
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask@c384f5 failed during the SCHEMA_UPGRADE phase due to: PreparedStatementCallback; SQL [DELETE FROM CONTENT_PERM_SET WHERE ID = ?]; Cannot delete or update a parent row: a foreign key constraint fails (`confluence40`.`content_perm`, CONSTRAINT `FKBD74B31676E33274` FOREIGN KEY (`CPS_ID`) REFERENCES `content_perm_set` (`ID`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`confluence40`.`content_perm`, CONSTRAINT `FKBD74B31676E33274` FOREIGN KEY (`CPS_ID`) REFERENCES `content_perm_set` (`ID`)) 
.....
Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [DELETE FROM CONTENT_PERM_SET WHERE ID = ?]; Cannot delete or update a parent row: a foreign key constraint fails (`confluence40`.`content_perm`, CONSTRAINT `FKBD74B31676E33274` FOREIGN KEY (`CPS_ID`) REFERENCES `content_perm_set` (`ID`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`confluence40`.`content_perm`, CONSTRAINT `FKBD74B31676E33274` FOREIGN KEY (`CPS_ID`) REFERENCES `content_perm_set` (`ID`)) 

Cause

The upgrade failed while processing duplicate page/space permissions. This is a bug that is being tracked at  CONF-26169 - Getting issue details... STATUS .

Resolution

1. Restore Confluence to the pre-upgrade state.

2. Run this query to find the problematic rows in parent (CONTENT_PERM_SET) and child (CONTENT_PERM) table. In this case, a content with ID 26247193 has 2 duplicated records.

   SELECT CONTENT_ID, CONT_PERM_TYPE, COUNT(CONTENT_ID) FROM CONTENT_PERM_SET GROUP BY CONTENT_ID, CONT_PERM_TYPE HAVING COUNT(*) > 1;

   Example results:

    content_id | cont_perm_type | count 
   ------------+----------------+-------
      26247193 | Edit           |     3

3. Review these offending records in parent table by using the content ID.

   SELECT * FROM CONTENT_PERM_SET WHERE CONTENT_ID = 26247193;

   Example results:

       id    | cont_perm_type | content_id | creationdate | lastmoddate 
   ----------+----------------+------------+--------------+-------------
    26607617 | Edit           |   26247193 |              | 
    26607618 | Edit           |   26247193 |              | 
    26607619 | Edit           |   26247193 |              | 

4. Review these offending records in child table by using the parent ID.

   SELECT * FROM CONTENT_PERM WHERE CPS_ID IN (26607617, 26607618, 26607619);

   Example results:

        id    | cp_type |     username     | groupname |  cps_id  |     creator      |      creationdate       |   lastmodifier   |       lastmoddate       
   ----------+---------+------------------+-----------+----------+------------------+-------------------------+------------------+-------------------------
    26411012 | Edit    | adiallo@rocket.com |           | 26607617 | adiallo@rocket.com | 2012-08-07 10:03:28.753 | adiallo@rocket.com | 2012-08-07 10:03:28.753
    26411013 | Edit    | adiallo@rocket.com |           | 26607618 | adiallo@rocket.com | 2012-08-07 10:04:02.987 | adiallo@rocket.com | 2012-08-07 10:04:02.987
    26411014 | Edit    | adiallo@rocket.com |           | 26607619 | adiallo@rocket.com | 2012-08-07 10:04:05.589 | adiallo@rocket.com | 2012-08-07 10:04:05.589

5. Using the IDs, run these queries to delete the duplicated records, leaving only one valid parent-child record.

   DELETE FROM CONTENT_PERM WHERE CPS_ID IN (26607618, 26607619);
   DELETE FROM CONTENT_PERM_SET WHERE ID IN (26607618, 26607619);

6. Attempt the upgrade again.