Upgrade SonarQube version 6.7.2 (build 37468) to version 7.9.1 failed in migration step

SonarQube Server / Community Build
1

Current setup:

  1. Microsoft SQL server 2014 ( mssql-jdbc-7.2.2.jre11.jar )
  2. Linux RedHat 7.6
  3. Java openjdk 11

Today we migrated SonarQube 6.7.2 to 7.9.1
We started the database migration ( http://sonar:9000/setup ) and ended up in a SQLServerException

2019.07.23 10:18:34 INFO  web[][DbMigrations] #1942 'Add live_measures.metric_id index'...
2019.07.23 10:19:41 ERROR web[][DbMigrations] #1942 'Add live_measures.metric_id index': failure | time=66409ms
2019.07.23 10:19:41 ERROR web[][DbMigrations] Executed DB migrations: failure | time=66410ms
2019.07.23 10:19:41 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=66518ms
2019.07.23 10:19:41 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 #1942 'Add live_measures.metric_id index' 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 java.base/java.lang.Iterable.forEach(Iterable.java:75)
        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(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.lang.IllegalStateException: Fail to execute CREATE UNIQUE INDEX live_measures_component ON live_measures (component_uuid, metric_id)
        at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:97)
        at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:77)
        at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:117)
        at org.sonar.server.platform.db.migration.version.v70.AddLiveMeasuresMetricIndex.execute(AddLiveMeasuresMetricIndex.java:44)
        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: com.microsoft.sqlserver.jdbc.SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.live_measures' and the index name 'live_measures_component'. The duplicate key value is (AViIF_AnIp5lMGVR_a3-, 1).
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2935)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:744)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175)
        at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:82)
        ... 14 common frames omitted

We investigated the (new) table live_measures and found 143 rows matching the duplicate component_uuid.

2

Hi,

Welcome to the community. As discussed here:

This error is caused by the presence in your database of projects that have not been analyzed since 5.6. You should either analyze those projects via a restored 6.7.2 before upgrade, or delete them.

 
HTH,
Ann

3

Hi Ann,

Thanks for the quick reply and link to the workaround.
We are using an old test database and we discovered two projects which we have deleted.

Cheers,
Frank