Error after upgrading from version 8.9 to 9.9

Grisu · February 10, 2023, 3:31pm

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?

Cyan007 · February 12, 2023, 8:53pm

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.

Grisu · February 13, 2023, 8:16am

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

Grisu · February 13, 2023, 3:45pm

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

pierreguillot · February 27, 2023, 12:44pm

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

Grisu · February 27, 2023, 12:58pm

yes, its real translated

aurelien.poscia · March 9, 2023, 2:53pm

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