SonarQube 8.4 Possible Database Upgrade Bug

[malceore (Brandon T. Wood)]

Hello,

We are trying to upgrade our Sonar instance to 8.4, we made the hop from 6.7 to 7.9.3 LTS with no issue, but when we attempted the database upgrade for 8.4 we receive the below error:

2020.07.14 14:08:10 INFO  web[][DbMigrations] #3311 'Remove column 'id' in 'components''...
2020.07.14 14:08:10 ERROR web[][DbMigrations] #3311 'Remove column 'id' in 'components'': failure | time=4ms
2020.07.14 14:08:10 ERROR web[][DbMigrations] Executed DB migrations: failure | time=405572ms
2020.07.14 14:08:10 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=405668ms
2020.07.14 14:08:10 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 #3311 'Remove column 'id' in 'components'' 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: Cannot find constraint for table 'components'
	at org.sonar.server.platform.db.migration.version.v84.util.SqlHelper.contraintNotFoundException(SqlHelper.java:113)
	at org.sonar.server.platform.db.migration.version.v84.util.SqlHelper.getPostgresSqlConstraint(SqlHelper.java:66)
	at org.sonar.server.platform.db.migration.version.v84.util.DropPrimaryKeySqlGenerator.generate(DropPrimaryKeySqlGenerator.java:52)
	at org.sonar.server.platform.db.migration.version.v83.DropIdFromComponentsTable.execute(DropIdFromComponentsTable.java:42)
	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

It looks as though the migration itself creates this table by renaming Projects:

2020.07.14 14:01:29 INFO  web[][DbMigrations] #3204 'Rename table 'PROJECTS' to 'COMPONENTS''...
2020.07.14 14:01:29 INFO  web[][DbMigrations] #3204 'Rename table 'PROJECTS' to 'COMPONENTS'': success | time=6ms
2020.07.14 14:01:30 INFO  web[][DbMigrations] #3207 'Drop 'TAGS' column from COMPONENTS table'...
2020.07.14 14:01:30 INFO  web[][DbMigrations] #3207 'Drop 'TAGS' column from COMPONENTS table': success | time=7ms
sonar-ets/c916737a-2754-434b-b0ac-36c288debdbd:/var/vcap/packages/sonar/sonar/logs$ 2020.07.14 14:05:30 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 359 rows processed (1 items/sec)

We noticed that there is mixed case sensitivity, but cannot confirm if this is the cause. We also recently migrated from Mysql to Postgresql before performing our 6.7 to 7.9 upgrade. Additionally we reproduced this on Postgresql 9.6 and 10.6 and we had no issue running the “Drop Column” command manually against the databases in question.

Steps to reproduce:

1. Setup 6.7 empty instance.
2. Upgrade to 7.9.3 LTS
3. Attempt Database Upgrade to 8.4.0

Thanks,
Brandon

[pierreguillot]

Hello again Brandon Your conclusion so far are correct, so let’s dig into the issue further.

The mechanism of this drop column method is to search for the constrain name, in order to delete it. We may have an issue with the uppercase on the table name. Could you please execute this query and show us the output?

SELECT conname FROM pg_constraint

And confirm that this query does find the primary key constraint:

SELECT conname FROM pg_constraint WHERE conrelid = 
(SELECT oid FROM pg_class WHERE LOWER(relname) LIKE LOWER('components'))

[malceore (Brandon T. Wood)]

Hi again Pierre,

We ran the upgrade in both an empty instance and a lightly populated Dev instance, getting the same error but we rolled back our empty instance to 7.9, so I am giving you the output of the Dev instance. When we migrate the database we could force it to cast the tables to uppercase and retry if you think that is valuable to test? Lastly, looks like the output of the queries is as you described,

Query #1
sonar=> SELECT conname FROM pg_constrain;
conname
------------------------------
cardinal_number_domain_check
yes_or_no_check
pk_live_measures
pk_org_quality_gates
pk_deprecated_rule_keys
pk_project_links2
pk_webhooks
pk_alm_app_installs
pk_project_mappings
pk_project_alm_bindings
pk_ce_task_message
pk_organization_alm_bindings
pk_event_component_changes
pk_user_properties
pk_internal_component_props
pk_project_qgates
pk_new_code_periods
pk_alm_settings
pk_project_alm_settings
pk_new_projects
pk_alm_pats
(21 rows)

query #2

sonar=> SELECT conname FROM pg_constraint WHERE conrelid = 
sonar-> (SELECT oid FROM pg_class WHERE LOWER(relname) LIKE LOWER('components'))
sonar-> ;
 conname
---------
(0 rows)

-Brandon and Team

[pierreguillot]

@malceore

When we migrate the database we could force it to cast the tables to uppercase and retry if you think that is valuable to test?

You can give it a try, but I won’t have too much hope in that (cast to lowercase would be more appropriate).

So in your case, there is no primary key on the components table, where we should have one. Right after this migration 3311 fail, could you please show us the what the components table looks like? (column names, indexes, primary key)

[malceore (Brandon T. Wood)]

Looks like we do not have an easy way to send to uppercase during our migration so we will hold off until we see no other path. Do you think something could have failed in our migration to 7.9.3 that stopped this Primary Key from being created? Should this primary key exist in Project prior to 7.9.3? If the constant simply needs to be remove could we add it manually? Here are the requested outputs below.

Contents:
# The schema must be created first.
id | name | description | enabled | scope | qualifier | kee | language | long_name | created_at | path | deprecated_kee | uuid | project_uuid | module_uuid | module_uuid_path | root_uuid | copy_component_uuid | uuid_path | b_changed | b_copy_component_uuid | b_description | b_enabled | b_language | b_long_name | b_module_uuid | b_module_uuid_path | b_name | b_path | b_qualifier | b_uuid_path | organization_uuid | private | main_branch_project_uuid
----±-----±------------±--------±------±----------±----±---------±----------±-----------±-----±---------------±-----±-------------±------------±-----------------±----------±--------------------±----------±----------±----------------------±--------------±----------±-----------±------------±--------------±-------------------±-------±-------±------------±------------±------------------±--------±-------------------------
(0 rows)

Description:
sonar=> \d components
Table “sonar.components”
Column | Type | Modifiers
--------------------------±-------------------------±------------------------------------------------------
id | bigint | not null default nextval(‘projects_id_seq’::regclass)
name | character varying(2000) |
description | character varying(2000) |
enabled | boolean | not null default true
scope | character varying(3) |
qualifier | character varying(10) |
kee | character varying(400) |
language | character varying(20) |
long_name | character varying(2000) |
created_at | timestamp with time zone |
path | character varying(2000) |
deprecated_kee | character varying(400) |
uuid | character varying(50) | not null
project_uuid | character varying(50) | not null
module_uuid | character varying(50) |
module_uuid_path | character varying(1500) |
root_uuid | character varying(50) | not null
copy_component_uuid | character varying(50) |
uuid_path | character varying(1500) | not null
b_changed | boolean |
b_copy_component_uuid | character varying(50) |
b_description | character varying(2000) |
b_enabled | boolean |
b_language | character varying(20) |
b_long_name | character varying(500) |
b_module_uuid | character varying(50) |
b_module_uuid_path | character varying(1500) |
b_name | character varying(500) |
b_path | character varying(2000) |
b_qualifier | character varying(10) |
b_uuid_path | character varying(1500) |
organization_uuid | character varying(40) | not null
private | boolean | not null
main_branch_project_uuid | character varying(50) |

Indexes:
sonar=> SELECT
sonar-> indexname,
sonar-> indexdef
sonar-> FROM
sonar-> pg_indexes
sonar-> WHERE
sonar-> tablename = ‘components’;
indexname | indexdef
-----------±---------
(0 rows)

All Existing Contraints:
sonar=> SELECT conname FROM pg_constraint;
conname
------------------------------
cardinal_number_domain_check
yes_or_no_check
pk_live_measures
pk_org_quality_gates
pk_deprecated_rule_keys
pk_project_links2
pk_webhooks
pk_alm_app_installs
pk_project_mappings
pk_project_alm_bindings
pk_ce_task_message
pk_organization_alm_bindings
pk_event_component_changes
pk_user_properties
pk_internal_component_props
pk_project_qgates
pk_new_code_periods
pk_alm_settings
pk_project_alm_settings
pk_new_projects
pk_alm_pats
(21 rows)

Additionally we watched the Postgresql processes during the migration and these were some of the last we saw below:
delete from properties where properties.resource_id is not null and not exists (select $1 from components c where properties.resource_id = c.id)
delete from group_roles where group_roles.resource_id is not null and not exists (select $1 from components c where group_roles.resource_id = c.id)
ALTER TABLE components DROP COLUMN tags, DROP COLUMN developer_uuid, DROP COLUMN authorization_updated_at
delete from user_roles where user_roles.resource_id is not null and not exists (select $1 from components c where user_roles.resource_id = c.id)
select c.uuid from components c where c.scope in ($1) and c.qualifier in ($2, $3, $4, $5)
select p.id as p_id, c.uuid as c_uuid from properties p, components c where p.resource_id = c.id and p.component_uuid is null
select cp.uuid, cp.kee, cp.qualifier, cp.organization_uuid, cp.name, cp.description, cp.private, cp.tags, cp.created_at from components cp left join projects np on np.uuid = cp.uuid where cp.scope = $1 and cp.qualifier in ($2, $3) and cp.main_branch_project_uuid is null and np.uuid is null
select * from components
ALTER TABLE projects RENAME TO components
select ur.id as ur_id, c.uuid as c_uuid from user_roles ur, components c where ur.resource_id = c.id and ur.component_uuid is null
select gp.id as gp_id, c.uuid as c_uuid from group_roles gp, components c where gp.resource_id = c.id and gp.component_uuid is null

[malceore (Brandon T. Wood)]

We discovered that our Mysql to Postgresql migration using pgloader seems to alter the names of the indexes in such a way that the upgrade cannot alter them. Below screenshot shows mysql on left and postgresql on the right.

Once we switch to doing a mysql_dump then psql restore we encounter the below error where the database throws an error trying to compare a boolean and integer value. Seems like these errors are inconsistent

Seems like this is an issue with the data contained in the database. Is there some article on a support method for migrating Sonar from Mysql to Postgresql?

[pierreguillot]

Our recommended way to go is to use our own https://github.com/SonarSource/mysql-migrator, maintained by SonarSource for this specific use case.

Using SonarQube to create the destination schema and use the tool to only copy the rows helps a lot to have the correct output

[malceore (Brandon T. Wood)]

Hi Pierre,

Using that tool seems to have solved our issues for now.

Thanks,
Brandon and Team

[malceore (Brandon T. Wood)]

@pierreguillot,

There wouldn’t happen to be a document on available tweaks for improving the runtime speed of that migration tool would there? It seems to be taking over twice as long as the PGloader tool we previously used. We have already tried assigning it more memory and threads, but it uses less than 20% of both during it’s cycle.

Thanks,
Brandon and Team

[pierreguillot]

Hi, the only option available is commitSize, default value is 5000. You can try to double or triple it. Don’t expect an incredible improvement.

[malceore (Brandon T. Wood)]

How is that option used, do we pass it to the migrator like a CLI parameter, -commitSize 10000? I could not find anything about this command in the project source. Also thanks again for your help!

[pierreguillot]

Oh crap. Indeed sorry, i mixed things up with another tool. This option is not available for mysql-migrator.