Sonarqube upgrade 8.9 and DB migration Error

Upgrade from Sonarqube 7.9 to 8.9 fails due DB migration ERROR

Hi everyone.

During the migration of the database the following error does not allow us to complete it.

Any ideas how to identify the project or projects causing this issue and what should we do to fix it?

sonarqube_app.1.bv59dst68kek@sq_serv | 2021.07.14 20:30:21 INFO web[DbMigrations] #3481 ‘Add primary key on ‘UUID’ column of ‘ISSUE_CHANGES’ table’…
sonarqube_app.1.bv59dst68kek@sq_serv | 2021.07.14 20:30:22 ERROR web[DbMigrations] #3481 ‘Add primary key on ‘UUID’ column of ‘ISSUE_CHANGES’ table’: failure | time=452ms
sonarqube_app.1.bv59dst68kek@sq_serv | 2021.07.14 20:30:22 ERROR web[DbMigrations] Executed DB migrations: failure | time=1129092ms
sonarqube_app.1.bv59dst68kek@sq_serv | 2021.07.14 20:30:22 ERROR web[o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=1129156ms
sonarqube_app.1.bv59dst68kek@sq_serv | 2021.07.14 20:30:22 ERROR web[o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
sonarqube_app.1.bv59dst68kek@sq_serv | org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #3481 ‘Add primary key on ‘UUID’ column of ‘ISSUE_CHANGES’ table’ failed
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:79)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
sonarqube_app.1.bv59dst68kek@sq_serv |    at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:405)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:52)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.engine.MigrationEngineImpl.execute(MigrationEngineImpl.java:68)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doUpgradeDb(DatabaseMigrationImpl.java:105)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doDatabaseMigration(DatabaseMigrationImpl.java:80)
sonarqube_app.1.bv59dst68kek@sq_serv |    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
sonarqube_app.1.bv59dst68kek@sq_serv |    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
sonarqube_app.1.bv59dst68kek@sq_serv |    at java.base/java.lang.Thread.run(Thread.java:829)
sonarqube_app.1.bv59dst68kek@sq_serv | Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE issue_changes ADD CONSTRAINT pk_issue_changes PRIMARY KEY (uuid)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:106)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:86)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.version.v84.issuechanges.AddPrimaryKeyOnUuidColumnOfIssueChangesTable.execute(AddPrimaryKeyOnUuidColumnOfIssueChangesTable.java:35)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.step.DdlChange.execute(DdlChange.java:45)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
sonarqube_app.1.bv59dst68kek@sq_serv | … 9 common frames omitted
sonarqube_app.1.bv59dst68kek@sq_serv | Caused by: org.postgresql.util.PSQLException: ERROR: could not create unique index “pk_issue_changes”
sonarqube_app.1.bv59dst68kek@sq_serv | Detail: Key (uuid)=(190289418) is duplicated.
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
sonarqube_app.1.bv59dst68kek@sq_serv |    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)

Hello @omarzavala,

It seems like your ISSUE_CHANGES table is likely to be corrupted and there are rows which have same value in UUID.

Could you execute following query to confirm?

select uuid, count(uuid) from issue_changes ic group by uuid having count(uuid) > 1