my current setup: SonarQube Community Edition 8.3, PostgreSQL 10
what I try to achieve: Upgrade SonarQube 8.3 to SonarQube 8.4
Hello, few days ago I received a MySQL dump of SQ database, and following the instructions for migration from MySQL 5.7.30 to PostgreSQL 10,and upgrade path for SQ Community 7.4 -> 7.9 -> 8.4, which failed, and was changed to 7.4 -> 7.9 -> 8.0 -> 8.3, now I have SQ 8.3 and PostgreSQL 10 working.My goal is to upgrade to 8.4.
So I try to perform the upgrade of SonarQube (standalone/community) from version sonarqube-8.3.0.34182 to sonarqube-8.4.0.35506. The database is PostgreSQL 10, and OS is Ubuntu 20.04.
Once I get to the step http://sonarqube/setup, and press the âUpgradeâ button, a web page with following error is received:
Upgrade Failed
Database connection cannot be established. Please check database status and JDBC settings.
Lines below are from the SQ web.log:
2020.07.15 07:47:50 INFO web[][DbMigrations] Executing DB migrations...
2020.07.15 07:47:50 INFO web[][DbMigrations] #3400 'Drop primary key on 'ID' column of 'EVENTS' table'...
2020.07.15 07:47:50 TRACE web[][sql] time=5ms | sql=SELECT conname FROM pg_constraint WHERE conrelid = (SELECT oid FROM pg_class WHERE relname LIKE 'events')
2020.07.15 07:47:50 ERROR web[][DbMigrations] #3400 'Drop primary key on 'ID' column of 'EVENTS' table': failure | time=9ms
2020.07.15 07:47:50 ERROR web[][DbMigrations] Executed DB migrations: failure | time=11ms
2020.07.15 07:47:50 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=64ms
2020.07.15 07:47:50 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 #3400 'Drop primary key on 'ID' column of 'EVENTS' 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: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: org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression
Location: File: nodeSubplan.c, Routine: ExecSetParamPlan, Line: 1037
Server SQLState: 21000
Do you confirm that you were also using MySQL, and then youâve migrated to PostgresSQL ?
it will help us understand more from where this strange issue is coming.
I have the exact same issue upgrading from 7.9.1 to 8.4 and my 7.9.1 installation was definitely PostgreSQL. I canât verify what it was prior to that however as it was migrated from a different VM which is no longer available. Any help would be appreciated! If I solve my own issue I will post the resolution here.
I also did a describe of the events table and it looks different than does the table from @Radoslav:
\d events
Table "public.events"
Column | Type | Collation | Nullable | Default
----------------+-------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('events_id_seq'::regclass)
name | character varying(400) | | |
category | character varying(50) | | |
description | character varying(4000) | | |
event_data | character varying(4000) | | |
event_date | bigint | | not null |
created_at | bigint | | not null |
component_uuid | character varying(50) | | not null |
analysis_uuid | character varying(50) | | not null |
uuid | character varying(40) | | not null |
Indexes:
"pk_events" PRIMARY KEY, btree (id)
"events_uuid" UNIQUE, btree (uuid)
"events_analysis" btree (analysis_uuid)
"events_component_uuid" btree (component_uuid)
could you please execute this and share with us the result?
select kcu.table_name,
tco.constraint_name,
kcu.column_name as key_column
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
on kcu.constraint_name = tco.constraint_name
and kcu.constraint_schema = tco.constraint_schema
and kcu.constraint_name = tco.constraint_name
where tco.constraint_type = 'PRIMARY KEY'
order by kcu.table_schema,
kcu.table_name;
@julienlancelot I just realized in my haste before I ran the last query you asked me to run as the wrong PostgreSQL user. Here is the correct result:
SELECT conname FROM pg_constraint WHERE conrelid in (SELECT oid FROM pg_class WHERE relname LIKE 'events');
conname
--------------------
pk_events
idx_287598_primary
(2 rows)
Great, this is really helping, thanks So here you have almost all your indexes duplicated. Some start with idx_, and some with pk_.
The pk_ indexes are totally normal, they are the one we expect.
The idx_ indexes must be coming from something else than SonarQube himself: a restore tool, a migration in the past from another DB vendor (From MySQL ?). Do you know, by any chance, if your database has been migrated from MySQL before the 7.9 version? (and if yes, how was done the migration?)
@pierreguillot I can check tomorrow. I thought I had deleted the old server but I think I found it in the archives so I should be able to restore it and check. It is very likely it was MySQL as we have been using SonarQube for a long time and MySQL was our standard back then. I am not as confident that I can track down how I did the migration though. It was most likely done by me but maintaining these servers isnât my day job so I am not always as diligent in documenting my successes as I should be.
@pierreguillot I can verify we were on MySQL and although I canât be certain of this it looks as if pgloader was used to migrate the data. Now that I am paying attention to when this was done I now know who did it so I can double check with him when he comes online.
Assuming this was the case what would the fix be? I do understand it would most likely involve some messy manual intervention.
@pierreguillot that is the output of
select kcu.table_name,
tco.constraint_name,
kcu.column_name as key_column
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
on kcu.constraint_name = tco.constraint_name
and kcu.constraint_schema = tco.constraint_schema
and kcu.constraint_name = tco.constraint_name
where tco.constraint_type = âPRIMARY KEYâ
order by kcu.table_schema,
kcu.table_name;
Yes it will Basically we have two possibility here:
your strange indexes are coming from SQ or our tool mysql-migrator, and I have to fix a bug in our code
your strange indexes are coming from pgLoader or another tool, and i will do my best to help you, but that wonât impact SonarQube sourcecode (nothing to âfixâ on our side).
Ok so for both @Radoslav and @Eric_Amell, you are in the same situation: you have unexpected indexes in your database, created by the migration from MySQL. We do provide a tool for this migration, that guarantee the schema to remain valid. With other tools such as PgLoader, we have no control on the target schema, hence the errors you face now.
you have two possibilities here:
Engage with SonarSource paying support that will individually guide you through this. The remediation may consist of using one of our internal tool to recreate a clean new database schema and migrate your data to it.
Delete manually all your indexes starting with idx_, and hope that there is no other issue with your schema.
If you choose the second option, maybe this can help you to fix your schema: This is the expected schema for 8.3.
So here you have almost all your indexes duplicated. Some start with 
Basically we have two possibility here: