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

Grisu · May 24, 2023, 2:49pm

@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.

pierreguillot · May 25, 2023, 8:32am

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

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
)

Add the unique index

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

Start SonarQube and run an analysis to validate that everything works as expected
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.
Follow the Recover from ElasticSerach read-only indices procedure to make sure the values in ES are up to date as well.

Topic		Replies	Views
Error while re-indexing projects after upgrade to Sonar CE version 9.9.0 SonarQube Server / Community Build sonarqube	1	352	March 20, 2023
Analysis of some projects failing after database migration: VisitException SonarQube Server / Community Build sonarqube , scanner , database	2	794	August 19, 2020
Visit of Component .... Failed SonarQube Server / Community Build 8-9-lts-upgrade	7	5192	June 9, 2021
Database upgrade fails when upgrading from 8.9 to 9.9 SonarQube Server / Community Build	2	694	February 17, 2023
SonarQube 9.9 LTS DB migration issue SonarQube Server / Community Build	1	822	February 22, 2023

Error after upgrading from version 8.9 to 9.9

Related topics