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
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 ?
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;
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.
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.
Restore postgresql DB from previous backup (new empty container, clean/rename your volumes) cat backup.sql | docker exec -i <DB_CONTAINER> psql -U sonar
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;'
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 );'
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 @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.