Error after upgrading from version 8.9 to 9.9

SonarQube 9.9 LTS Upgrade
1

We Upgrade from Version 8.9.3 to the 9.9 lts version. with this upgrade we installed java 17 and postgresql 15, because both is needed in the newest version. we upgrade the database in postgresql from version 10 to version 15.
Now, we have an error by running the analyse with the following message:

Error Details

org.sonar.ce.task.projectanalysis.component.VisitException: Visit of Component {key=<hidden>,type=PROJECT} 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.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:539)
	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:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)
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 0 insert into live_measures (
      uuid,
      component_uuid,
      project_uuid,
      metric_uuid,
      value,
      text_value,
      measure_data,
      created_at,
      updated_at
    ) values (
      'AYY755pe3PYwjEmWgCm9',
      'AYY2orJuWGdxJBhl71xo',
      'AYY2orJuWGdxJBhl71xo',
      '43',
      0.0,
      NULL,
      NULL,
      1676042279518,
      1676042279518
    )
    on conflict(component_uuid, metric_uuid) do update set
      value = excluded.value,
      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.text_value is distinct from excluded.text_value or
      live_measures.measure_data is distinct from excluded.measure_data was aborted: FEHLER: es gibt keinen Unique-Constraint oder Exclusion-Constraint, der auf die ON-CONFLICT-Angabe passt  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 0 insert into live_measures (
      uuid,
      component_uuid,
      project_uuid,
      metric_uuid,
      value,
      text_value,
      measure_data,
      created_at,
      updated_at
    ) values (
      'AYY755pe3PYwjEmWgCm9',
      'AYY2orJuWGdxJBhl71xo',
      'AYY2orJuWGdxJBhl71xo',
      '43',
      0.0,
      NULL,
      NULL,
      1676042279518,
      1676042279518
    )
    on conflict(component_uuid, metric_uuid) do update set
      value = excluded.value,
      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.text_value is distinct from excluded.text_value or
      live_measures.measure_data is distinct from excluded.measure_data was aborted: FEHLER: es gibt keinen Unique-Constraint oder Exclusion-Constraint, der auf die ON-CONFLICT-Angabe passt  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)
	... 20 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 0 insert into live_measures (
      uuid,
      component_uuid,
      project_uuid,
      metric_uuid,
      value,
      text_value,
      measure_data,
      created_at,
      updated_at
    ) values (
      'AYY755pe3PYwjEmWgCm9',
      'AYY2orJuWGdxJBhl71xo',
      'AYY2orJuWGdxJBhl71xo',
      '43',
      0.0,
      NULL,
      NULL,
      1676042279518,
      1676042279518
    )
    on conflict(component_uuid, metric_uuid) do update set
      value = excluded.value,
      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.text_value is distinct from excluded.text_value or
      live_measures.measure_data is distinct from excluded.measure_data was aborted: FEHLER: es gibt keinen Unique-Constraint oder Exclusion-Constraint, der auf die ON-CONFLICT-Angabe passt  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)
	... 27 more
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into live_measures (
      uuid,
      component_uuid,
      project_uuid,
      metric_uuid,
      value,
      text_value,
      measure_data,
      created_at,
      updated_at
    ) values (
      'AYY755pe3PYwjEmWgCm9',
      'AYY2orJuWGdxJBhl71xo',
      'AYY2orJuWGdxJBhl71xo',
      '43',
      0.0,
      NULL,
      NULL,
      1676042279518,
      1676042279518
    )
    on conflict(component_uuid, metric_uuid) do update set
      value = excluded.value,
      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.text_value is distinct from excluded.text_value or
      live_measures.measure_data is distinct from excluded.measure_data was aborted: FEHLER: es gibt keinen Unique-Constraint oder Exclusion-Constraint, der auf die ON-CONFLICT-Angabe passt  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:893)
	at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:916)
	at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1684)
	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)
	... 32 more
Caused by: org.postgresql.util.PSQLException: FEHLER: es gibt keinen Unique-Constraint oder Exclusion-Constraint, der auf die ON-CONFLICT-Angabe passt
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
	... 39 more

do you know, how we have the error and how we can solve it?

3

Not sure if this was tried yet, but from the notes of the 9.9 LTS post:

Also don’t forget that if you run a version older than the previous LTS (8.9), you need to upgrade to 8.9.10 first, and then to 9.9 LTS.

4

on the following release-notes site is nothing written, that i must first upgrade to version 8.9.10 . this link have i used: Release upgrade notes

5

We have Version 8.9.3 before. Now i have upgrade to 8.9.10 and then to 9.9 with the same error

6

Hello, could you please comfirm that

FEHLER: es gibt keinen Unique-Constraint oder Exclusion-Constraint, der auf die ON-CONFLICT-Angabe passt

Can be translated in English with

ERROR: there is no unique constraint or exclusion constraint that matches the ON-CONFLICT phrase

8

yes, its real translated

10

Hi @Grisu ,

Could you send us the output of the following sql query?

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and lower(i.relname)='live_measures_component'
order by
    t.relname,
    i.relname;

We would like to make sure that the index live_measures_component exists on your DB. In case you don’t have it, you can use the following statement to recreate it:

CREATE UNIQUE INDEX live_measures_component ON public.live_measures USING btree (component_uuid, metric_uuid);

Thanks,
Aurélien

12

@aurelien.poscia
Here is the result of your query. It is tested with the version 9.9.1:

sonar=# select
sonar-#     t.relname as table_name,
sonar-#     i.relname as index_name,
sonar-#     a.attname as column_name
sonar-# from
sonar-#     pg_class t,
sonar-#     pg_class i,
sonar-#     pg_index ix,
sonar-#     pg_attribute a
sonar-# where
sonar-#     t.oid = ix.indrelid
sonar-#     and i.oid = ix.indexrelid
sonar-#     and a.attrelid = t.oid
sonar-#     and a.attnum = ANY(ix.indkey)
sonar-#     and t.relkind = 'r'
sonar-#     and lower(i.relname)='live_measures_component'
sonar-# order by
sonar-#     t.relname,
sonar-#     i.relname;
 table_name | index_name | column_name
------------+------------+-------------
(0 Zeilen)

When i try to create the index, then it comes following:

sonar=# CREATE UNIQUE INDEX live_measures_component ON public.live_measures USING btree (component_uuid, metric_uuid);
FEHLER:  konnte Unique Index »live_measures_component« nicht erstellen
DETAIL:  SchlĂĽssel (component_uuid, metric_uuid)=(AWIqK9GJIXWGXqZjyvm_, 16) ist doppelt vorhanden.
14

It seems that you have duplicates in the live_measures table, which is unexpected. Let’s clean things up:

  1. Remove the duplicates (this query does not use index, it may take a while)
delete from live_measures 
where uuid in (
	select uuid from (
	SELECT uuid, ROW_NUMBER() OVER( PARTITION BY component_uuid, metric_uuid ORDER BY uuid ) AS row_num
	FROM live_measures lm 
	) duplicate where duplicate.row_num > 1
)
  1. Add the unique index
CREATE UNIQUE INDEX live_measures_component ON public.live_measures USING btree (component_uuid, metric_uuid);

  1. Start SonarQube and run an analysis to validate that everything works as expected

  2. As we delete the duplicate live_measures, you might observe some inconsistent data in the UI. I strongly advise you to re-analyze your projects to make sure all measures are updated.

  3. Follow the Recover from ElasticSerach read-only indices procedure to make sure the values in ES are up to date as well.

1 Like