PSQLException in background tasks (duplicate key value violates unique constraint)

SonarQube
,
1

Hi all,

We are using SonarQube 9.7.1 Community Edition and see the below background task error for many different projects.
We haven’t done version/db updates in the last days.
Does anyone have an idea what could be the root cause here?

Regards, Alexander

org.sonar.ce.task.projectanalysis.component.VisitException: Visit of Component {key=com.sap.core.domaindb-server:domain.db.web/src/main/java/com/sap/core/domain,uuid=AX9E8VK518eE9ybzAZX_,type=DIRECTORY} failed
	at org.sonar.ce.task.projectanalysis.component.VisitException.rethrowOrWrap(VisitException.java:44)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visit(DepthTraversalTypeAwareCrawler.java:41)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visitChildren(DepthTraversalTypeAwareCrawler.java:95)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visitImpl(DepthTraversalTypeAwareCrawler.java:54)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visit(DepthTraversalTypeAwareCrawler.java:39)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visitChildren(DepthTraversalTypeAwareCrawler.java:95)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visitImpl(DepthTraversalTypeAwareCrawler.java:54)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visit(DepthTraversalTypeAwareCrawler.java:39)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visitChildren(DepthTraversalTypeAwareCrawler.java:95)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visitImpl(DepthTraversalTypeAwareCrawler.java:54)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visit(DepthTraversalTypeAwareCrawler.java:39)
	at org.sonar.ce.task.projectanalysis.step.PersistLiveMeasuresStep.execute(PersistLiveMeasuresStep.java:141)
	at org.sonar.ce.task.step.ComputationStepExecutor.executeStep(ComputationStepExecutor.java:79)
	at org.sonar.ce.task.step.ComputationStepExecutor.executeSteps(ComputationStepExecutor.java:70)
	at org.sonar.ce.task.step.ComputationStepExecutor.execute(ComputationStepExecutor.java:57)
	at org.sonar.ce.task.projectanalysis.taskprocessor.ReportTaskProcessor.process(ReportTaskProcessor.java:75)
	at org.sonar.ce.taskprocessor.CeWorkerImpl$ExecuteTask.executeTask(CeWorkerImpl.java:212)
	at org.sonar.ce.taskprocessor.CeWorkerImpl$ExecuteTask.run(CeWorkerImpl.java:194)
	at org.sonar.ce.taskprocessor.CeWorkerImpl.findAndProcessTask(CeWorkerImpl.java:160)
	at org.sonar.ce.taskprocessor.CeWorkerImpl$TrackRunningState.get(CeWorkerImpl.java:135)
	at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:87)
	at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:53)
	at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131)
	at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:74)
	at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
	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:829)
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error committing transaction.  Cause: org.apache.ibatis.executor.BatchExecutorException: org.sonar.db.measure.LiveMeasureMapper.upsert (batch index #2) failed. 1 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: Batch entry 2 insert into live_measures (
      uuid,
      component_uuid,
      project_uuid,
      metric_uuid,
      value,
      text_value,
      variation,
      measure_data,
      created_at,
      updated_at
    ) values (
      'AYRQqD4KGAD_xcQU2JXk',
      'AX9E8VK518eE9ybzAZX_',
      'AX9E8UuntySWgu3JW0rR',
      '15',
      1421.0,
      NULL,
      NULL,
      NULL,
      1667800514058,
      1667800514058
    )
    on conflict(component_uuid, metric_uuid) do update set
      value = excluded.value,
      variation = excluded.variation,
      text_value = excluded.text_value,
      measure_data  = excluded.measure_data,
      updated_at = excluded.updated_at
    where
      live_measures.value is distinct from excluded.value or
      live_measures.variation is distinct from excluded.variation or
      live_measures.text_value is distinct from excluded.text_value or
      live_measures.measure_data is distinct from excluded.measure_data was aborted: ERROR: duplicate key value violates unique constraint "live_measures_component"
  Detail: Key (component_uuid, metric_uuid)=(AX9E8VK518eE9ybzAZX_, 15) already exists.  Call getNextException to see other errors in the batch.
### Cause: org.apache.ibatis.executor.BatchExecutorException: org.sonar.db.measure.LiveMeasureMapper.upsert (batch index #2) failed. 1 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: Batch entry 2 insert into live_measures (
      uuid,
      component_uuid,
      project_uuid,
      metric_uuid,
      value,
      text_value,
      variation,
      measure_data,
      created_at,
      updated_at
    ) values (
      'AYRQqD4KGAD_xcQU2JXk',
      'AX9E8VK518eE9ybzAZX_',
      'AX9E8UuntySWgu3JW0rR',
      '15',
      1421.0,
      NULL,
      NULL,
      NULL,
      1667800514058,
      1667800514058
    )
    on conflict(component_uuid, metric_uuid) do update set
      value = excluded.value,
      variation = excluded.variation,
      text_value = excluded.text_value,
      measure_data  = excluded.measure_data,
      updated_at = excluded.updated_at
    where
      live_measures.value is distinct from excluded.value or
      live_measures.variation is distinct from excluded.variation or
      live_measures.text_value is distinct from excluded.text_value or
      live_measures.measure_data is distinct from excluded.measure_data was aborted: ERROR: duplicate key value violates unique constraint "live_measures_component"
  Detail: Key (component_uuid, metric_uuid)=(AX9E8VK518eE9ybzAZX_, 15) already exists.  Call getNextException to see other errors in the batch.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:223)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:214)
	at org.sonar.db.DbSessionImpl.commit(DbSessionImpl.java:42)
	at org.sonar.db.BatchSession.commit(BatchSession.java:168)
	at org.sonar.ce.task.projectanalysis.step.PersistLiveMeasuresStep$MeasureVisitor.visitAny(PersistLiveMeasuresStep.java:190)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visitNode(DepthTraversalTypeAwareCrawler.java:67)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visitImpl(DepthTraversalTypeAwareCrawler.java:51)
	at org.sonar.ce.task.projectanalysis.component.DepthTraversalTypeAwareCrawler.visit(DepthTraversalTypeAwareCrawler.java:39)
	... 29 more
Caused by: org.apache.ibatis.executor.BatchExecutorException: org.sonar.db.measure.LiveMeasureMapper.upsert (batch index #2) failed. 1 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: Batch entry 2 insert into live_measures (
      uuid,
      component_uuid,
      project_uuid,
      metric_uuid,
      value,
      text_value,
      variation,
      measure_data,
      created_at,
      updated_at
    ) values (
      'AYRQqD4KGAD_xcQU2JXk',
      'AX9E8VK518eE9ybzAZX_',
      'AX9E8UuntySWgu3JW0rR',
      '15',
      1421.0,
      NULL,
      NULL,
      NULL,
      1667800514058,
      1667800514058
    )
    on conflict(component_uuid, metric_uuid) do update set
      value = excluded.value,
      variation = excluded.variation,
      text_value = excluded.text_value,
      measure_data  = excluded.measure_data,
      updated_at = excluded.updated_at
    where
      live_measures.value is distinct from excluded.value or
      live_measures.variation is distinct from excluded.variation or
      live_measures.text_value is distinct from excluded.text_value or
      live_measures.measure_data is distinct from excluded.measure_data was aborted: ERROR: duplicate key value violates unique constraint "live_measures_component"
  Detail: Key (component_uuid, metric_uuid)=(AX9E8VK518eE9ybzAZX_, 15) already exists.  Call getNextException to see other errors in the batch.
	at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:149)
	at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:129)
	at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:122)
	at org.apache.ibatis.executor.BaseExecutor.commit(BaseExecutor.java:242)
	at org.apache.ibatis.executor.CachingExecutor.commit(CachingExecutor.java:119)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:220)
	... 36 more
Caused by: java.sql.BatchUpdateException: Batch entry 2 insert into live_measures (
      uuid,
      component_uuid,
      project_uuid,
      metric_uuid,
      value,
      text_value,
      variation,
      measure_data,
      created_at,
      updated_at
    ) values (
      'AYRQqD4KGAD_xcQU2JXk',
      'AX9E8VK518eE9ybzAZX_',
      'AX9E8UuntySWgu3JW0rR',
      '15',
      1421.0,
      NULL,
      NULL,
      NULL,
      1667800514058,
      1667800514058
    )
    on conflict(component_uuid, metric_uuid) do update set
      value = excluded.value,
      variation = excluded.variation,
      text_value = excluded.text_value,
      measure_data  = excluded.measure_data,
      updated_at = excluded.updated_at
    where
      live_measures.value is distinct from excluded.value or
      live_measures.variation is distinct from excluded.variation or
      live_measures.text_value is distinct from excluded.text_value or
      live_measures.measure_data is distinct from excluded.measure_data was aborted: ERROR: duplicate key value violates unique constraint "live_measures_component"
  Detail: Key (component_uuid, metric_uuid)=(AX9E8VK518eE9ybzAZX_, 15) already exists.  Call getNextException to see other errors in the batch.
	at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2367)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:560)
	at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:887)
	at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
	at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1663)
	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
	at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:123)
	... 41 more
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "live_measures_component"
  Detail: Key (component_uuid, metric_uuid)=(AX9E8VK518eE9ybzAZX_, 15) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
	... 48 more
1 Like
2

Have you recently updated your instance to v 9.7.1?

1 Like
3

The system was updated about two weeks ago (it was 8.9.9) but there were many successful background tasks after that. The problems started this Monday and the only workaround we see currently is to re-create the problematic projects.

4

Hi everyone, we’re unfortunately facing the exact same issues. We did indeed recently update our instance to v9.7.1 — is there any known problem with it?

6

Just to add that when we export the database and then try to import it, we see the following error:

stderr:
ERROR: could not create unique index “live_measures_component”
DETAIL: Key (component_uuid, metric_uuid)=(AX4K8nJqOKa3vTviwMx_, 4) is duplicated.

We tried to find the problematic projects by following this post, but when we executed the PostgreSQL query we see the following error:
ERROR: column p.scope does not exist
LINE 7: and p.scope = ‘PRJ’

Seems it’s related to differences in the databases between the SonarQube versions.
Does anyone know how we can identify the duplicated key (so we could try to fix it)?

1 Like
7

We just updated 8.9 LTS → 9.7 (Developer edition in both cases, if it makes a differences), breaking our “only LTS” policy for the first time to get the Java 17 analysis, and we promptly ran into this error for one project.

A simple check with

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

gave us something above 1700 entries with duplicates.
Interestingly immediately after the update I did a full vacuum, so everything must have been fine at that point.

Reading other peoples problems we applied this query (SonarQube Update Database for Version 7 Fails - Stack Overflow)

DELETE FROM
     live_measures a
         USING live_measures b
 WHERE
     a.updated_at < b.updated_at
     AND (a.component_uuid = b.component_uuid AND a.metric_uuid = b.metric_uuid);

This fixed it for us (at least for now)

2 Likes
9

To complete the story: 7 Days later and everything works as expected

1 Like
10

Thanks for the update.
Could you let us know what’s the version of postgresql you’re using?
Do you have a unique index on the component_uuid and metric_uuid columns? Should be named live_measures_component.

11

As I’ve just seen we are still running on postgresql v10. I’m not sure what the official supported version for sonar is, but 10 is a old in any case.

We have the index, yes. Which bugged me anyway while I was debugging this, because I was not sure how it happened that we have duplicate entries and an index with a unique constraint which should have prevented this. But I didn’t dive too deep into this. Could be something which happened while an update or post-update-operation was running which disabled the integrity checks? Not sure, maybe I’ve missed an earlier exception which could have lead to the origin of this error.

12

We executed the following query several times (some records were duplicated more than 10 times) and the database is OK now:

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