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

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

Have you recently updated your instance to v 9.7.1?

1 Like

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.

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?

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