Error updating database

SonarQube Server / Community Build
1

I tried to upgrade Sonar application [hosted in windows server] from 7.9.6 to 8.9 and i faced few issues in DB migration. So I have reverted back both application and database [external database - postgresql].

After restoration, application is up and running fine. But it can’t able to update the database. I am getting the below error in all the builds.

Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
### The error may exist in org.sonar.db.measure.LiveMeasureMapper
### The error may involve org.sonar.db.measure.LiveMeasureMapper.upsert-Inline
### The error occurred while setting parameters
### SQL: insert into live_measures (       uuid,       component_uuid,       project_uuid,       metric_id,       value,       text_value,       variation,       measure_data,       created_at,       updated_at     ) values      (         ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      ),(        ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?      )      on conflict(component_uuid, metric_id) 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
### Cause: org.postgresql.util.PSQLException: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Can anyone help to fix this issue.

2

Hey there.

When you restored your SonarQube v7.9 database, did you make sure you restored all the relevant indexes as well, and not just the data?

I’m not an expert on this error but:

  • I know that sometimes, data gets restored without indexes
  • This StackOverflow makes be believe the error you’re receiving can show up when there should be an index, but isn’t.

As a first step, you might just check your database to see if there’s an index of any kind on the live_measures table.

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
ORDER BY
    tablename,
    indexname;
3

Yes… Indexes has been restored as well…

Indexes has been set to uuid and project_uuid columns.

But if you look the insert statement, there are no values in the statement. So, will there be any issues at the sonar application end ?

4

@ganncamp As per Checking 7.9 LTS EOL version of SonarQube , does 7.9.6 version wont work anymore ?

If it will work, then are we missing anything in the restoring the server and database? Pls provide your suggestion

5

Just because SonarQube v7.9 is EOL doesn’t mean that one day it stops working all of a sudden… but it seems like something went wrong with the database migration or restoration.

You see, there should be an index preventing conflicts on (component_uuid, metric_id). This is why I think you’re missing some indexes.

While yes, you could restore a single index manually:

CREATE UNIQUE INDEX "LIVE_MEASURES_COMPONENT" ON "LIVE_MEASURES"("COMPONENT_UUID", "METRIC_UUID");

This does not necessarily mean the rest of your database is in good shape.

Is your original database still running somewhere, or an old backup sitting around?