Failed upgrade from 8.1 to 8.9 beacause of pk_duplicated profiles in default_qprofiles

Hello team,

I’m trying to upgrade from 8.1.0 which is completelly functional to sonarqube-8.9.7.52159 but I’m facing an error during the database upgrade in /sonar/setup action:

2022.04.13 12:42:47 INFO  web[][DbMigrations] #4101 'Drop organization_uuid from 'quality_profile' table'...
2022.04.13 12:42:47 INFO  web[][DbMigrations] #4101 'Drop organization_uuid from 'quality_profile' table': success | time=15ms
2022.04.13 12:42:47 INFO  web[][DbMigrations] #4102 'Drop primary key of table 'default_qprofiles''...
2022.04.13 12:42:47 INFO  web[][DbMigrations] #4102 'Drop primary key of table 'default_qprofiles'': success | time=24ms
2022.04.13 12:42:47 INFO  web[][DbMigrations] #4103 'Drop organization_uuid from 'default_qprofiles' table'...
2022.04.13 12:42:47 INFO  web[][DbMigrations] #4103 'Drop organization_uuid from 'default_qprofiles' table': success | time=15ms
2022.04.13 12:42:47 INFO  web[][DbMigrations] #4104 'Add primary key to the table 'default_qprofiles'...
2022.04.13 12:42:47 ERROR web[][DbMigrations] #4104 'Add primary key to the table 'default_qprofiles': failure | time=5ms
2022.04.13 12:42:47 ERROR web[][DbMigrations] Executed DB migrations: failure | time=78123ms
2022.04.13 12:42:47 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=78180ms
2022.04.13 12:42:47 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 #4104 'Add primary key to the table 'default_qprofiles' 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:405)
	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:68)
	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(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE default_qprofiles ADD CONSTRAINT pk_default_qprofiles PRIMARY KEY (language)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:106)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:86)
	at org.sonar.server.platform.db.migration.version.v86.AddPrimaryKeyToDefaultQProfiles.execute(AddPrimaryKeyToDefaultQProfiles.java:34)
	at org.sonar.server.platform.db.migration.step.DdlChange.execute(DdlChange.java:45)
	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
	... 9 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: could not create unique index "pk_default_qprofiles"
  Detail: Key (language)=(js) is duplicated.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)
	... 13 common frames omitted

It seems like the pk for the table pk_default_qprofiles is duplicated and it cannot create anything else.
If I check the table in the data base I see all profiles duplicated, which wheren’t in 8.1 version.

May I know what’s going on, why and how to fix it? My data base is postgresql, thanks so much in advance.

Hey there.

Are you gleaning this from what you saw in the UI, or what you saw in the database? If I were to make a first suggestion, it would be to query this table on a backup (which I hope you have) of your v8.1 instance and see if you only see one entry per language, or multiple.