Fisheye/Crucible upgrade to versions > 4.8 fails with unique constraint violation on uk_source_rev_path_hash
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
Summary
In rare cases, an upgrade to Fisheye/Crucible may fail to upgrade the database with a unique constraint violation on uk_source_rev_path_hash. It is caused by data duplication originating from Crucible older than 3.0.
Environment
Upgrade to Fisheye > 4.8.0
Diagnosis
In upgrading to Fisheye/Crucible 4.8.0 or later, the following type of error in atlassian-fisheye-<date>.log prevents the upgrade from completing:
Oracle example
<date> ERROR - The Web context could not be started
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dbControlFactory' defined in ServletContext resource [/WEB-INF/applicationContext.xml]: Instantiation of bean failed; nested exception is org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [com.cenqua.crucible.hibernate.DBControlFactoryImpl]: Constructor threw exception; nested exception is com.cenqua.crucible.hibernate.CruDBException: Problem upgrading database
...
Caused by: com.cenqua.crucible.hibernate.CruDBException: Problem upgrading database
...
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (FISHEYE_OWNER.UK_SOURCE_REV_PATH_HASH) violatedSQL Server example
<date> ERROR - The Web context could not be started
Caused by: com.cenqua.crucible.hibernate.CruDBException: Problem upgrading database
...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.cru_revision' and the index name 'uk_source_rev_path_hash'. The duplicate key value is (xxxxxxxxx).
Cause
It is caused by data duplication originating from Crucible older than 3.0.
Solution
Ensure you first have a backup of the pre-upgrade database
- Stop Fisheye/Crucible
- Run the version of this script compatible with your database (Oracle, SQLServer or Postgres)
(i) This is an example script and may need to be modified - Start Fisheye 4.8
You can always contact Atlassian Support for guidance.