DB migration error while upgrading from 9.5 to 9.7: duplicate key value violates unique constraint

[c.joosse]

While upgrading from SonarQube Developer edition v9.5 to v9.7, the manually triggered DB migrations failed with the following message:

sonarqube_1  | 2022.11.17 08:50:58 INFO  web[][DbMigrations] Executing DB migrations...
sonarqube_1  | 2022.11.17 08:50:58 INFO  web[][DbMigrations] #6608 'Remove branch information from 'kee' in 'components''...
db_1         | 2022-11-17 08:50:58.611 UTC [35] ERROR:  duplicate key value violates unique constraint "idx_16633_projects_kee"
db_1         | 2022-11-17 08:50:58.611 UTC [35] DETAIL:  Key (kee)=(PeriodicDBBackupRunner:openscap-datadog-check) already exists.
db_1         | 2022-11-17 08:50:58.611 UTC [35] STATEMENT:  update components set kee = $1 where uuid= $2
sonarqube_1  | 2022.11.17 08:50:58 ERROR web[][DbMigrations] #6608 'Remove branch information from 'kee' in 'components'': failure | time=108ms
sonarqube_1  | 2022.11.17 08:50:58 ERROR web[][DbMigrations] Executed DB migrations: failure | time=120ms
sonarqube_1  | 2022.11.17 08:50:58 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=225ms
sonarqube_1  | 2022.11.17 08:50:58 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception

We’re running both the PostgreSQL database and SonarQube in docker containers.
To be able to keep using SonarQube I’ve removed the index and re-ran the migrations. Now everything seems to run fine. Should this unique index still be there, or is it safe to remove it?

[leo.geoffroy]

Hello @c.joosse and welcome to the community!

You can have a look to this post with a similar issue.

As explained in the other post, the index project_kee is dropped in 9.7, and is replaced by another one, but it is also expected that the index has exactly the name projects_kee and not idx_16633_projects_kee.

Could you please provide us with the following information:

• The first version of SonarQube that was setup on that instance
• The result of this command on the sq schema (schema name might need to be adapted):

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
        schemaname = 'public'
ORDER BY
    tablename,
    indexname;

• If you use some specific tools to backup and restore the database (such as pgloader)

So for now I think it is safe to remove it manually, but you may encounter other issues in the future, so it is important that we understand how those index names are created

[c.joosse]

Hi Leo,

Thanks for you response!
The first version of SonarQube that ran was before I started working at this company, so I think about 5 years ago. We migrated once from a MySQL database to PostgreSQL and from running bare metal to running in a container.

See the attached CSV for the result of the query:
postgres_pg_catalog_pg_indexes.txt (26.6 KB)

We used a specific tool for migrating from MySQL to PostgreQSL, but that was already a while ago. I think it was indeed pgloader.

For now we removed the index and that seems to run fine indeed.

[leo.geoffroy]

Hello @c.joosse,
For information, we have worked on this ticket for 9.9, so it is easier for users that have “special” index name (such as idx_{number}_{indexName}) to do their migrations since we saw it was a common recurrence.