UserIdMigrationUpgradeTask should delete from UserApplicationLink

Impact

After an upgrade to Confluence 5.3.x or higher, the Workbox notification tray on the upper right of Confluence shows a stacktrace (foreign key constraint violation) when clicked.

Technical notes

AO does not support cascade deletes. Currently UserIdMigrationUpgradeTask tries to delete all user specific data for users that do not exist in the system, but only deletes rows from the user table. It should also delete the rows from the user_app_link table for users which are not in the user_mapping table, before deleting from the USER table.

This bug is causing:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`confluence5`.`ao_9412a1_user_app_link`, CONSTRAINT `fk_ao_9412a1_user_app_link_user_id` FOREIGN KEY (`USER_ID`) REFERENCES `ao_9412a1_aouser` (`ID`))

(see line 53 in UserIdMigrationUpgradeTask)

Workaround

Purge the invalid rows manually from the 'AO_9412A1_USER_APP_LINK' table. Example queries are provided below for MySQL and Postgres; you may need to modify the queries slightly to suit your own database management system (change table casing, surround them in quotation marks, etc).

Please back up your database and perform/verify these operations in a test environment before applying to production

Postgres

1. Ensure Confluence is shut down
2. Run the following SELECT statement. It should return with a non-empty set to be deleted.

   SELECT * FROM "AO_9412A1_USER_APP_LINK" WHERE "USER_ID" not in (select "ID" from "AO_9412A1_AOUSER" aou, user_mapping um where lower(aou."USERNAME") = um.lower_username);
   

   OR

   SELECT * FROM "AO_9412A1_USER_APP_LINK" WHERE "USER_ID" not in (select "ID" from "AO_9412A1_AOUSER" aou, user_mapping um where lower(aou."USERNAME") = um.user_key);
   

3. Run the following DELETE statement to remove the above rows:

   DELETE FROM "AO_9412A1_USER_APP_LINK" WHERE "USER_ID" not in (select "ID" from "AO_9412A1_AOUSER" aou, user_mapping um where lower(aou."USERNAME") = um.lower_username);
   

   OR

   DELETE FROM "AO_9412A1_USER_APP_LINK" WHERE "USER_ID" not in (select "ID" from "AO_9412A1_AOUSER" aou, user_mapping um where lower(aou."USERNAME") = um.user_key);
   

4. Run the SELECT from Step 2 again to confirm. Now an empty set should be returned.
5. Start Confluence and see if issue has been resolved

MySQL

1. Ensure Confluence is shut down
2. Run the following SELECT statement. It should return with a non-empty set to be deleted.

   SELECT * from AO_9412A1_USER_APP_LINK where USER_ID not in (select ID from AO_9412A1_AOUSER aou, user_mapping um where lower(aou.USERNAME) = um.lower_username);
   

   OR

   SELECT * from AO_9412A1_USER_APP_LINK where USER_ID not in (select ID from AO_9412A1_AOUSER aou, user_mapping um where lower(aou.USERNAME) = um.user_key);
   

3. Run the following DELETE statement to remove the above rows:

   DELETE from AO_9412A1_USER_APP_LINK where USER_ID not in (select ID from AO_9412A1_AOUSER aou, user_mapping um where lower(aou.USERNAME) = um.lower_username);
   

   OR

   DELETE from AO_9412A1_USER_APP_LINK where USER_ID not in (select ID from AO_9412A1_AOUSER aou, user_mapping um where lower(aou.USERNAME) = um.user_key);
   

4. Run the SELECT from Step 2 again to confirm. Now an empty set should be returned.
5. Start Confluence and see if issue has been resolved