Cannot upgrade to SonarQube version 8.4, since the DB migrations: failure

SonarQube Server / Community Build
,
9

Hello!

For documentation purpose, I also encountered this problem while upgrading from 8.3 to 8.4:

2020.07.18 18:30:21 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #3545 'Add index on 'metric_uuid' column of 'LIVE_MEASURES' table' failed
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:79)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
        at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:405)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:52)
        at org.sonar.server.platform.db.migration.engine.MigrationEngineImpl.execute(MigrationEngineImpl.java:68)
        at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doUpgradeDb(DatabaseMigrationImpl.java:105)
        at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doDatabaseMigration(DatabaseMigrationImpl.java:80)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.IllegalStateException: Fail to execute CREATE UNIQUE INDEX live_measures_component ON live_measures (component_uuid, metric_uuid)
        at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:106)
        at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:86)
        at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:128)
        at org.sonar.server.platform.db.migration.version.v84.metrics.livemeasures.AddIndexOnMetricUuidOfLiveMeasuresTable.execute(AddIndexOnMetricUuidOfLiveMeasuresTable.java:43)
        at org.sonar.server.platform.db.migration.step.DdlChange.execute(DdlChange.java:45)
        at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
        ... 9 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: could not create unique index "live_measures_component"
  Detail: Key (component_uuid, metric_uuid)=(AVqxndNYVE5O0vCg4RV_, 166) is duplicated.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:268)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)
        ... 14 common frames omitted

After restoring my database, I checked for duplicates with the command:

select component_uuid,metric_id,count(*) from live_measures
group by component_uuid,metric_id
having count(*) > 1

And there were a lot of duplicates (~2000).

So I cleaned up these duplicates with the following command:

delete from live_measures where uuid in (
	select max(uuid) from live_measures
	group by component_uuid,metric_id
	having count(*) > 1 
)

After these operation, I relaunched the migration to 8.4, and it worked successfully.

Thanks!

4 Likes