Sonarqube upgrade to 8.9.9 to 8.9.10 LTA failed due to uniq_profile_rule_uuids index issue

ljaladi · July 6, 2024, 4:32am

Hello together,

I’m upgrading sonarqube from 8.9.9 to 9.9.5 LTA community Version. initially i started with 8.9.10 LTA then 9.9.5 LTA. while upgrading from 8.9.9 to 8.9.10. LTA the upgrade works well. but i have database version postgres10. due to incompatibility of postgres-10 in 9.9.x releases i have upgraded my postgres10 to postgres-11 before postgres upgrade i have also performed ubuntu OS upgrade to 20.04 LTS from 18.04 LTS. after upgrading the database & OS when i start sonarqube it is continuously restarting after every 16s of start. then in the logs i found that it causing due to a index that not allowing sonarqube to start.

2024-07-06 04:53:16 BST STATEMENT:  insert into active_rules (
              uuid,
              profile_uuid,
              rule_uuid,
              failure_level,
              inheritance,
              created_at,
              updated_at
            ) values (
              $1,
              $2,
              $3,
              $4,
              $5,
              $6,
              $7
            )
        RETURNING *
2024-07-06 04:53:39 BST ERROR:  duplicate key value violates unique constraint "uniq_profile_rule_uuids"
2024-07-06 04:53:39 BST DETAIL:  Key (profile_uuid, rule_uuid)=(AYHPrK5FTiVai3yrmiy-, 2581) already exists.
2024-07-06 04:53:39 BST STATEMENT:  insert into active_rules (
              uuid,
              profile_uuid,
              rule_uuid,
              failure_level,
              inheritance,
              created_at,
              updated_at
            ) values (
              $1,
              $2,
              $3,
              $4,
              $5,
              $6,
              $7
            )
        RETURNING *

cat web.log  | grep 'uniq_profile_rule_uuids'
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_profile_rule_uuids"
### Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_profile_rule_uuids"
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_profile_rule_uuids"
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_profile_rule_uuids"
### Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_profile_rule_uuids"
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_profile_rule_uuids"

when i remove the index uniq_profile_rule_uuids the sonarqube is starting. but creating duplicate values. i know the index is created for blocking duplicate entries in the active_rules table. i tried to remove the duplicates manually in the tables initially. every time it is giving new rule_uuid in postgres table.

--
2024.07.04 02:40:23 INFO  web[][o.s.s.q.RegisterQualityProfiles] Update profile java/Sonar way
2024.07.04 02:40:23 INFO  web[][o.s.s.q.RegisterQualityProfiles] Update profile web/Sonar way
2024.07.04 02:40:23 INFO  web[][o.s.s.q.RegisterQualityProfiles] Update profile xml/Sonar way
2024.07.04 02:40:23 INFO  web[][o.s.s.q.RegisterQualityProfiles] Update profile flex/Sonar way
2024.07.04 02:40:23 INFO  web[][o.s.s.q.RegisterQualityProfiles] Update profile php/Sonar way
2024.07.04 02:40:23 INFO  web[][o.s.s.q.RegisterQualityProfiles] Update profile php/PSR-2
2024.07.04 02:40:23 INFO  web[][o.s.s.q.RegisterQualityProfiles] Update profile php/Drupal
2024.07.04 02:40:23 INFO  web[][o.s.s.q.RegisterQualityProfiles] Update profile vbnet/Sonar way
2024.07.04 02:40:23 ERROR web[][o.s.s.p.Platform] Background initialization failed. Stopping SonarQube
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_profile_rule_uuids"
  Detail: Key (profile_uuid, rule_uuid)=(AYHPrK5FTiVai3yrmiy-, 2581) already exists.
### The error may exist in org.sonar.db.qualityprofile.ActiveRuleMapper
### The error may involve org.sonar.db.qualityprofile.ActiveRuleMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into active_rules (       uuid,       profile_uuid,       rule_uuid,       failure_level,       inheritance,       created_at,       updated_at     ) values (       ?,       ?,       ?,       ?,       ?,       ?,       ?     )
### Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_profile_rule_uuids"
  Detail: Key (profile_uuid, rule_uuid)=(AYHPrK5FTiVai3yrmiy-, 2581) already exists.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
	at com.sun.proxy.$Proxy57.insert(Unknown Source)
	at org.sonar.db.qualityprofile.ActiveRuleDao.insert(ActiveRuleDao.java:106)
	at org.sonar.server.qualityprofile.RuleActivator.doInsert(RuleActivator.java:262)
--
	at org.sonar.server.platform.platformlevel.PlatformLevelStartup$1.doPrivileged(PlatformLevelStartup.java:87)
	at org.sonar.server.user.DoPrivileged.execute(DoPrivileged.java:45)
	at org.sonar.server.platform.platformlevel.PlatformLevelStartup.start(PlatformLevelStartup.java:84)
	at org.sonar.server.platform.PlatformImpl.executeStartupTasks(PlatformImpl.java:198)
	at org.sonar.server.platform.PlatformImpl.access$400(PlatformImpl.java:46)
	at org.sonar.server.platform.PlatformImpl$1.lambda$doRun$1(PlatformImpl.java:122)
	at org.sonar.server.platform.PlatformImpl$AutoStarterRunnable.runIfNotAborted(PlatformImpl.java:370)
	at org.sonar.server.platform.PlatformImpl$1.doRun(PlatformImpl.java:122)
	at org.sonar.server.platform.PlatformImpl$AutoStarterRunnable.run(PlatformImpl.java:354)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uniq_profile_rule_uuids"
  Detail: Key (profile_uuid, rule_uuid)=(AYHPrK5FTiVai3yrmiy-, 2581) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)

i have also performed Reindex & analyze on the sonarqube database.

each restart of sonarqube instance creating 23 duplicate values in active_rule tables.

sonarqube=# SELECT profile_uuid, rule_uuid, COUNT(*) FROM active_rules GROUP BY profile_uuid, rule_uuid HAVING COUNT(*) > 1;
     profile_uuid     |      rule_uuid       | count 
----------------------+----------------------+-------
 AYHPrK5FTiVai3yrmiy_ | 434                  |     2
 AYHPrK5FTiVai3yrmiy_ | 428                  |     2
 AYHPrK5FTiVai3yrmiy_ | 429                  |     2
 AYHPrK5FTiVai3yrmiy_ | 446                  |     2
 AYHPrK5FTiVai3yrmiy_ | 436                  |     2
 AYHPrK5FTiVai3yrmiy_ | 441                  |     2
 AYHPrK5FTiVai3yrmiy_ | 449                  |     2
 AYHPrK5FTiVai3yrmiy_ | 448                  |     2
 AYHPrK5FTiVai3yrmiy_ | 427                  |     2
 AYHPrK5FTiVai3yrmiy_ | 447                  |     2
 AYHPrK5FTiVai3yrmiy_ | 432                  |     2
 AYHPrK5FTiVai3yrmiy_ | 430                  |     2
 AYHPrK5FTiVai3yrmiy_ | 444                  |     2
 AYHPrK5FTiVai3yrmiy_ | 440                  |     2
 AYHPrK5FTiVai3yrmiy_ | 426                  |     2
 AYHPrK5FTiVai3yrmiy_ | 433                  |     2
 AYHPrK5FTiVai3yrmiy_ | AYHPraTfTiVai3yrmi1E |     2
 AYHPrK5FTiVai3yrmiy_ | 431                  |     2
 AYHPrK5FTiVai3yrmiy_ | 438                  |     2
 AYHPrK5FTiVai3yrmiy_ | 442                  |     2
 AYHPrK5FTiVai3yrmiy_ | 435                  |     2
 AYHPrK5FTiVai3yrmiy_ | 445                  |     2
 AYHPrK5FTiVai3yrmiy_ | 443                  |     2
(23 rows)

i tried to remove them. but still it is creating again and again.

Currently this is a old instance running on ubuntu 18.04 LTS. so i upgraded ubuntu to 20.04 LTS. i could have used same server but postgres stopped packages for 18.04 LTS.

any hint why it causing this issue.

Colin · July 8, 2024, 6:46am

Hey there.

It’s a big surprise to me that moving from 8.9.9 to 8.9.10 would change anything database-side. Do you have a backup of your database as it was on 8.9.9? Something tells me that maybe something went wrong in migrating your database to the new server.

ljaladi · July 8, 2024, 8:11am

Hello Colin,

Thanks for your reply.

I haven’t made any changes to the database side; it is exactly the same as in version 8.9.9 (using Postgres-10). I followed the exact steps mentioned in the upgrade document.

The upgrade from 8.9.9 to 8.9.10 LTS worked well. However, due to the end of support for Ubuntu 18.04 LTS, I upgraded the OS to 20.04 LTS. After that, the Sonar service is not starting. I’m getting errors as mentioned above, and it only starts when I drop the index.

For your information, the database upgrade was successful, and I didn’t see any issues on the database side when upgrading from Postgres-10 to Postgres-11. I encountered the same issues with Postgres-10; it is not working. I feel something is breaking after the OS upgrade from 18.04 to 20.04 in SonarQube. Before upgrading the OS, I performed all the database activities like vacuum full, reindex, and analyze.

Best regards,
Lalu

Colin · August 2, 2024, 3:02pm

Hey @ljaladi

Sorry for the late reply.

My best bet would be some database corruption. If only 23 rows are affected, the best way to secure your SonarQube instance for future updates would be to remove the duplicate entries, and add the index back.

CREATE UNIQUE INDEX "UNIQ_PROFILE_RULE_UUIDS" ON "ACTIVE_RULES"("PROFILE_UUID", "RULE_UUID");