Upgrading Confluence from 6.0.x on MySQL fails with "Duplicate entry for key 'e_h_p_s_idx'" error
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
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 - Huge number of content inside Confluence causes Page Editing and Collaborative Editing to load really slow. 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.
Solution
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 making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Was this helpful?