Upgrading from 8.5.1

taavit21 · December 15, 2020, 9:09am

This is the error i received when i tried /setup

./logs/web.log:2020.12.15 11:04:44 ERROR web[][DbMigrations] #4102 'Drop primary key of table 'default_qprofiles'': failure | time=7ms
./logs/web.log:org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #4102 'Drop primary key of table 'default_qprofiles'' failed
./logs/web.log:Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE default_qprofiles DROP CONSTRAINT pk_default_qprofiles
./logs/web.log:Caused by: org.postgresql.util.PSQLException: ERROR: constraint "pk_default_qprofiles" of relation "default_qprofiles" does not exist

pierreguillot · January 5, 2021, 11:03am

Hi, are you still struggling with this?

Mol_Olsson · January 9, 2021, 1:32am

Not the original reporter, but I got the same error yesterday when trying to upgrade from 8.3.1 (community edition) to 8.6.0 (community edition).

Last year I had a trial run of upgrading a clone of this db from 8.3.1 to 8.4.2 (enterprise trial). That went flawlessly.
We have our own quality profiles, and 90% sure we haven’t deleted any default ones.

 2021-01-08 18:25:38jvm 1    | 2021.01.08 17:25:38 INFO  web[][DbMigrations] #4101 'Drop organization_uuid from 'quality_profile' table': success | time=6ms
2021-01-08 18:25:38jvm 1    | 2021.01.08 17:25:38 INFO  web[][DbMigrations] #4102 'Drop primary key of table 'default_qprofiles''...
2021-01-08 18:25:38jvm 1    | 2021.01.08 17:25:38 ERROR web[][DbMigrations] #4102 'Drop primary key of table 'default_qprofiles'': failure | time=6ms
2021-01-08 18:25:38jvm 1    | 2021.01.08 17:25:38 ERROR web[][DbMigrations] Executed DB migrations: failure | time=142175ms
2021-01-08 18:25:38jvm 1    | 2021.01.08 17:25:38 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=142259ms
2021-01-08 18:25:38jvm 1    | 2021.01.08 17:25:38 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
2021-01-08 18:25:38jvm 1    | org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #4102 'Drop primary key of table 'default_qprofiles'' failed
2021-01-08 18:25:38jvm 1    | 	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:79)
2021-01-08 18:25:38jvm 1    | 	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
2021-01-08 18:25:38jvm 1    | 	at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:405)
2021-01-08 18:25:38jvm 1    | 	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:52)
2021-01-08 18:25:38jvm 1    | 	at org.sonar.server.platform.db.migration.engine.MigrationEngineImpl.execute(MigrationEngineImpl.java:68)
2021-01-08 18:25:38jvm 1    | 	at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doUpgradeDb(DatabaseMigrationImpl.java:105)
2021-01-08 18:26:16jvm 1    | 	at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doDatabaseMigration(DatabaseMigrationImpl.java:80)
2021-01-08 18:26:16jvm 1    | 	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
2021-01-08 18:26:16jvm 1    | 	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
2021-01-08 18:26:16jvm 1    | 	at java.base/java.lang.Thread.run(Thread.java:834)
2021-01-08 18:26:16jvm 1    | Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE default_qprofiles DROP CONSTRAINT pk_default_qprofiles
2021-01-08 18:26:16jvm 1    | 	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:106)
2021-01-08 18:26:16jvm 1    | 	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:86)
2021-01-08 18:26:16jvm 1    | 	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:128)
2021-01-08 18:26:16jvm 1    | 	at org.sonar.server.platform.db.migration.version.v86.DropDefaultQProfilesPk.execute(DropDefaultQProfilesPk.java:34)
2021-01-08 18:26:16jvm 1    | 	at org.sonar.server.platform.db.migration.step.DdlChange.execute(DdlChange.java:45)
2021-01-08 18:26:16jvm 1    | 	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
2021-01-08 18:26:16jvm 1    | 	... 9 common frames omitted
2021-01-08 18:26:16jvm 1    | Caused by: org.postgresql.util.PSQLException: ERROR: constraint "pk_default_qprofiles" of relation "default_qprofiles" does not exist
2021-01-08 18:26:16jvm 1    | 	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
2021-01-08 18:26:16jvm 1    | 	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
2021-01-08 18:26:16jvm 1    | 	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
2021-01-08 18:26:16jvm 1    | 	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
2021-01-08 18:26:16jvm 1    | 	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
2021-01-08 18:26:16jvm 1    | 	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
2021-01-08 18:26:16jvm 1    | 	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
2021-01-08 18:26:16jvm 1    | 	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
2021-01-08 18:26:16jvm 1    | 	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
2021-01-08 18:26:16jvm 1    | 	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
2021-01-08 18:26:16jvm 1    | 	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
2021-01-08 18:26:16jvm 1    | 	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)
2021-01-08 18:26:16jvm 1    | 	... 14 common frames omitted

taavit21 · January 11, 2021, 6:56am

@pierreguillot - Yes, i still have the same issue. I can´t upgrade.

kunal_jha · January 13, 2021, 9:37am

I am also facing this issue for my sonarqube instance.

while upgrading from 8.5 to 8.6

Starting DB Migration and container restart
2021.01.13 03:33:34 INFO  web[][DbMigrations] Executing DB migrations...
2021.01.13 03:33:34 INFO  web[][DbMigrations] #4102 'Drop primary key of table 'default_qprofiles''...
2021.01.13 03:33:34 ERROR web[][DbMigrations] #4102 'Drop primary key of table 'default_qprofiles'': failure | time=10ms
2021.01.13 03:33:34 ERROR web[][DbMigrations] Executed DB migrations: failure | time=10ms
2021.01.13 03:33:34 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=76ms
2021.01.13 03:33:34 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 #4102 'Drop primary key of 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 DROP CONSTRAINT pk_default_qprofiles
        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.step.DdlChange$ContextImpl.execute(DdlChange.java:128)
        at org.sonar.server.platform.db.migration.version.v86.DropDefaultQProfilesPk.execute(DropDefaultQProfilesPk.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: constraint "pk_default_qprofiles" of relation "default_qprofiles" does not exist

pierreguillot · January 13, 2021, 6:13pm

Ok, thanks for reporting this. I dug a bit and found a bug that may affect you. I reported it here.

Could you please search for the primary key constraint’s name on the default_qprofiles table on your database?

SELECT conname FROM pg_constraint WHERE conrelid in
(SELECT oid FROM pg_class WHERE relname LIKE '%default_qprofiles')

kunal_jha · January 13, 2021, 7:33pm


sonar=> SELECT conname FROM pg_constraint WHERE conrelid in
sonar-> (SELECT oid FROM pg_class WHERE relname LIKE '%default_qprofiles');
      conname
--------------------
 idx_118195_primary
(1 row)

sonar=> \d+  default_qprofiles;
                                           Table "sonar.default_qprofiles"
      Column       |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description
-------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 organization_uuid | character varying(40)  |           | not null |         | extended |              |
 language          | character varying(20)  |           | not null |         | extended |              |
 qprofile_uuid     | character varying(255) |           | not null |         | extended |              |
 created_at        | numeric                |           | not null |         | main     |              |
 updated_at        | numeric                |           | not null |         | main     |              |
Indexes:
    "idx_118195_primary" PRIMARY KEY, btree (organization_uuid, language)
    "idx_118195_uniq_default_qprofiles_uuid" UNIQUE, btree (qprofile_uuid)

sonar=>

pierreguillot · January 14, 2021, 9:45am

Okay so given your situation, we will update the database manually.

The first step is to backup your database.

Then, you can manually remove the column and the constraint:
ALTER TABLE default_qprofiles ALTER COLUMN organization_uuid DROP DEFAULT
ALTER TABLE default_qprofiles DROP CONSTRAINT idx_118195_primary

Once this is done, verify that the column is gone and the constraint removed. If successful, skip the migration with this:
insert into schema_migrations(version) values (4102)

Then you can restart SonarQube, the migration will resume and continue at the next step.

kunal_jha · January 14, 2021, 10:26am

Going to perform this in the next 45-60 mins and get back to you @pierreguillot

taavit21 · January 14, 2021, 10:28am

This helped. Thank you!

kunal_jha · January 14, 2021, 11:05am

@pierreguillot
It worked.
Thank you very much you have saved a lot of time for us.

sonar=> ALTER TABLE default_qprofiles ALTER COLUMN organization_uuid DROP DEFAULT ;
ALTER TABLE
sonar=> ALTER TABLE default_qprofiles DROP CONSTRAINT idx_118195_primary;
ALTER TABLE
sonar=> insert into schema_migrations(version) values (4102);
INSERT 0 1
sonar=>