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

I’m trying to upgrade the SonarQube (Docker/Community) from version 8.3.1 to 8.4. Sadly during executing the upgrade via http://xxx/sonarqube/setup, the system give me an error.

My Host environment is CentOS: 7.7.1908 with Docker version 19.03.8.

The database is PostgreSQL version 10 (docker image)

The SonarQube version 8.4 docker image is pulled from the DockerHub.

The exception is as the following: -

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

Could you please help to advise?

1 Like

I face the same issue with the same error message.

I use the helm chart sonarqube-6.5.0 with Sonarqube Dockerimage 8.3.1-developer and I try to upgrade to 8.4.0-developer.

Hi, looks like there are duplicate in the live_measures table. Could you please share with us the result of this query, ran on the database after the crash?

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

EDIT: And as an additional information, what’s the result of theses 2 queries, but run on your database on version 8.3, before you start the migration?

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

and

select component_uuid,metric_id from live_measures where metric_id = 104

Ok thanks for the information. On your database before the migration, could you verify that the table live_measures contain a unique index named live_measures_component on the columns component_uuid & metric_id ?

No, there is not.

There are only PK and live_measures_project as the following: -

ALTER TABLE public.live_measures
    ADD CONSTRAINT pk_live_measures PRIMARY KEY (uuid);

and

CREATE INDEX live_measures_project
    ON public.live_measures USING btree
    (project_uuid COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

Same error here:

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)=(AW7C7BvKAq9aY22GKx8-, 5) is duplicated.

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

Thank you so much @adferrand , we faced the same issue and were able to fix it using your method.

For the record, if you are using a docker containers for the postgresql DB & SonarQube, here are the steps we performed:

  1. Shutdown SonarQube 8.2

  2. Backup the postgresql DB (container is running)
    docker exec <DB_CONTAINER> pg_dumpall -c -U sonar > backup.sql

  3. Start SonarQube 8.4, upgrade database /setup --> !!! Database upgrade failed with live_measures duplicates error message !!! (start sweating)

  4. Shutdown SonarQube 8.4

  5. Restore postgresql DB from previous backup (new empty container, clean/rename your volumes)
    cat backup.sql | docker exec -i <DB_CONTAINER> psql -U sonar

  6. Check if there are duplicates in live_measures table (probably):
    docker exec -i <DB_CONTAINER> psql -U sonar -a sonar -c 'select component_uuid,metric_id,count(*) from live_measures group by component_uuid,metric_id having count(*) > 1;'

  7. Delete duplicates in live_measures table:
    docker exec -i <DB_CONTAINER> psql -U sonar -a sonar -c 'delete from live_measures where uuid in ( select max(uuid) from live_measures group by component_uuid,metric_id having count(*) > 1 );'

  8. Start SonarQube 8.4, upgrade database /setup

  9. Enjoy

1 Like

Hi, i would like to be add a note on this thread: using this method indeed solve the duplication issue.The downside is that you may have metrics for projects that are not up-to-date, so don’t trust the numbers until the next project analysis if you do that.

Thanks for the warning, any idea how to fix this issue properly?

It would be useful to know if for this request:

the live_measures.component_uuid corresponding does have a record in the component table. Something like this:

select * from components c
where c.uuid in
(select max(uuid) as uuid from live_measures
	group by component_uuid,metric_id
	having count(*) > 1
)

My hypothesis here is that’s the duplicated live_measures you have are orphans. Otherwise, you would have error browsing your projects.

1 Like

Thanks @adferrand! This helped me to resolve the issue that we were seeing. (SQ EE 8.9.7)

Our was unrelated to any migration, it just started happening as a result of our normal day-to-day scans. Over 73,000 duplicates returned. After running the delete statement (multiple times as some combinations had several duplications), was finally able to “REINDEX INDEX live_measures_component;” and resolve our failing background tasks.