Upgrade to 10.4.1: ERROR: duplicate key value violates unique constraint "pk_rule_tags"

SonarQube Server / Community Build
1

Hi,

I’m trying to upgrade from 10.3 to 10.4.1 and I get the following error:

2024.03.15 15:32:59 INFO  web[][o.s.s.p.Platform] Database needs to be migrated. Please refer to https://docs.sonarsource.com/sonarqube/latest/setup/upgrading
2024.03.15 15:32:59 INFO  web[][o.s.s.p.w.MasterServletFilter] Initializing servlet filter org.sonar.server.platform.web.WebServiceFilter@430df350 [pattern=UrlPattern{inclusions=[/api/system/migrate_db.*, ...], exclusions=[/api/components/update_key, ...]}]
2024.03.15 15:33:00 INFO  web[][o.h.v.i.util.Version] HV000001: Hibernate Validator null
2024.03.15 15:33:31 INFO  web[][o.s.s.p.d.m.DatabaseMigrationImpl] Starting DB Migration and container restart
2024.03.15 15:33:31 INFO  web[][DbMigrations] Executing DB migrations...
2024.03.15 15:33:31 INFO  web[][DbMigrations] #104000 'Delete redundant Failed Alerts for Applications'...
2024.03.15 15:33:31 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 0 rows processed (0 items/sec)
2024.03.15 15:33:31 INFO  web[][DbMigrations] #104000 'Delete redundant Failed Alerts for Applications': success | time=8ms
2024.03.15 15:33:31 INFO  web[][DbMigrations] #104001 'Rename metric 'wont_fix_issues' to 'accepted_issues''...
2024.03.15 15:33:31 INFO  web[][DbMigrations] #104001 'Rename metric 'wont_fix_issues' to 'accepted_issues'': success | time=3ms
2024.03.15 15:33:31 INFO  web[][DbMigrations] #104002 'Create table 'rules_tags''...
2024.03.15 15:33:31 INFO  web[][DbMigrations] #104002 'Create table 'rules_tags'': success | time=15ms
2024.03.15 15:33:31 INFO  web[][DbMigrations] #104003 'Populate 'rule_tags' table'...
2024.03.15 15:33:31 ERROR web[][DbMigrations] #104003 'Populate 'rule_tags' table': failure | time=109ms
2024.03.15 15:33:31 ERROR web[][DbMigrations] Executed DB migrations: failure | time=138ms
2024.03.15 15:33:31 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=155ms
2024.03.15 15:33:31 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #104003 'Populate 'rule_tags' table' failed
	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:79)
	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
	at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:52)
	at org.sonar.server.platform.db.migration.engine.MigrationEngineImpl.execute(MigrationEngineImpl.java:54)
	at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doUpgradeDb(DatabaseMigrationImpl.java:105)
	at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doDatabaseMigration(DatabaseMigrationImpl.java:80)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.BatchUpdateException: Batch entry 145 INSERT INTO rule_tags (rule_uuid, is_system_tag, value)
VALUES ('AXy39gJlIW5Gx6gsADst', 'FALSE', 'test')
 was aborted: ERROR: duplicate key value violates unique constraint "pk_rule_tags"
  Detail: Key (value, rule_uuid)=(test, AXy39gJlIW5Gx6gsADst) already exists.  Call getNextException to see other errors in the batch.
	at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
	at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:52)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2402)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:574)
	at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:896)
	at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:919)
	at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1685)
	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
	at org.sonar.server.platform.db.migration.step.UpsertImpl.execute(UpsertImpl.java:68)
	at org.sonar.server.platform.db.migration.version.v104.PopulateRuleTagsTable.execute(PopulateRuleTagsTable.java:69)
	at org.sonar.server.platform.db.migration.step.DataChange.execute(DataChange.java:44)
	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
	... 9 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pk_rule_tags"
  Detail: Key (value, rule_uuid)=(test, AXy39gJlIW5Gx6gsADst) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
	... 19 common frames omitted

For your information, I’ve got a custom plugin. I checked if one of my rules was defining multiple times the same “test” tag but it does not seem to be the case.

How could I help you investigate?

Thank you

Regards

Nicolas

2

Hey there.

Probably we should dive into the rules table to figure out which specific rule is the issue here.

Can you execute this query and return the results?

   SELECT *
    FROM rules
    WHERE uuid = 'AXy39gJlIW5Gx6gsADst'
3

Hi Colin,

It is one of our custom rules.
status: READY
system_tags: test,test-1

Would you need any other fields? Because I’m not allowed to paste all data here.

Thank you

Regards

Nicolas

4

tags would be the other useful field

5

Hi Colin,

I, indeed, missed this important column.
tags column value is test.

It looks like the rule didn’t have any tag at first. Then, the test tag was manually added to ti. And later on, a new version of our plugin globally added the test tag to this rule. This “duplication” might be the root cause of the migration issue.

Also, for another instance on which the rule was not manually tagged as test, the database migration worked well.

Should I manually update the database to empty the tags column for this rule? Or do you plan to provide a fix to support this corner case?

Many thanks!

Regards

Nicolas

6

I don’t quite know what you should do next (or what we’ll do next), but I’ll flag it for the team that will know :smiley:

9

Hi Colin,

For your information, I managed to upgrade after emptying the tags column for this specific rule.
I’ve got a database backup (before migration) in case you’d like me to test the upgrade with a patch.

Thank you

Regards

Nicolas

10

Hi there @Nicolas_Alcaraz,

I suspect that the test tag was duplicated for this specific rule in the DB. Would it be possible to confirm this for us on the old backup?

You can check this by running:

SELECT system_tags, tags FROM RULES WHERE uuid = 'AXy39gJlIW5Gx6gsADst'

Kind regards
Alain

11

Hi Alain,

Yes, this test tag was both in system_tags and tags columns.
The probable use case to end up in this situation is explained there: Upgrade to 10.4.1: ERROR: duplicate key value violates unique constraint "pk_rule_tags" - #5 by Nicolas_Alcaraz

Regards

Nicolas

12

Forgive me, I must have misunderstood the original post - so indeed there was a duplication, but one is a system tag, and the other is a normal tag.

While this is a corner case, it should still be addressed. You can follow the ticket here.

In any case, I am glad your manual intervention solved the issue.

1 Like
15

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.