Upgrading Confluence from 6.0.x on MySQL fails with "Duplicate entry for key 'e_h_p_s_idx'" error
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
Upgrading from Confluence 6.0.x to a newer version fails, and an error similar to the following shows in the browser:
Upgrade failed. Please consult the system logs for details. You will need to fix these problems, restore your database and confluence home directory to the pre upgrade state. Then retry the upgrade. Upgrade error message: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DropAndRecreateSynchronyEventsIndexUpgradeTask@63b2c25b failed during the UPGRADE phase due to: StatementCallback; SQL [create unique index e_h_p_s_idx on `EVENTS` (`history`, `partition`, `sequence`)]; Duplicate entry '/Synchrony-12345-abcde-1234567/confluence-12345' for key 'e_h_p_s_idx'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '/Synchrony-12345-abcde-1234567/confluence-12345' for key 'e_h_p_s_idx'
Diagnosis
Environment
Upgrading from a 6.0.x version
- On MySQL
Cause
Confluence 6.0.x had a bug in which it would sometimes be missing certain indexes on the EVENTS table: CONFSERVER-45745 - Getting issue details... STATUS . During the upgrade, Confluence will try to re-add these indexes, but won't be able to if there are duplicate values on the EVENTS table that violate the index rules.
Workaround
You can remove this conflicting data from the database so Confluence won't run into the error during the upgrade, with the following steps:
- Restore Confluence to a pre-upgrade state
- Follow the steps on How to reduce the size of Synchrony tables. This will walk through truncating all the Synchrony tables, including the EVENTS table.
- Re-try the upgrade
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.