Thanks @pierreguillot I will take a look at deleting the indexes and let you know if that helped.
@pierreguillot Unfortunately deleting the indexes isnāt enough. I am not getting the following error:
2020.08.03 18:08:51 ERROR web[][DbMigrations] #3400 'Drop primary key on 'ID' column of 'EVENTS' table': failure | time=12ms
2020.08.03 18:08:51 ERROR web[][DbMigrations] Executed DB migrations: failure | time=14ms
2020.08.03 18:08:51 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=128ms
2020.08.03 18:08:51 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
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:103)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.sonar.server.platform.db.migration.version.v84.util.SqlHelper.getPostgresSqlConstraint(SqlHelper.java:62)
at org.sonar.server.platform.db.migration.version.v84.util.DropPrimaryKeySqlGenerator.generate(DropPrimaryKeySqlGenerator.java:52)
at org.sonar.server.platform.db.migration.version.v84.common.DropPrimaryKeyOnIdColumn.execute(DropPrimaryKeyOnIdColumn.java:39)
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
I am going to continue to look into it. I have a copy of the bad system to play with it however.
Hi @pierreguillot,
I just came back of vacation today. I spent some time today on this issue and realized that my problem was due to having multiple sonar schema in the same database (one in public and another one in a schema named āsonarqubeā). After removing the older schema (āsonarqubeā), I didnāt had any problems migrating to 8.4.
Since, Iām the only one managing our Sonar instance, I knew this was not a mysql to postgresql migration issue.
So, no problems anymore. Thanks.
1 Like
Hi @pierreguillot,
could I also try to ask for help with the same issue ā#3400 āDrop primary key on āIDā column of āEVENTSā tableā: failureā?
We did not migrate from mySQL, we use Postgresql and the database remained the same. All settings copied from v.8.2 into 8.4 insatallation but we get same exact error mentioned by Eric.
For 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;
|"clients"|"client_pkey"|"id"|
|---|---|---|
|"deployments"|"deployments_pkey"|"id"|
|"replications"|"replications_pkey"|"id"|
|"pull_requests"|"pull_requests_pkey"|"id"|
|"reported_updates"|"pk_reported_updates"|"pull_request_id"|
|"reported_updates"|"pk_reported_updates"|"commit_id"|
|"active_rule_parameters"|"pk_active_rule_parameters"|"id"|
|"active_rules"|"pk_active_rules"|"id"|
|"ce_activity"|"pk_ce_activity"|"id"|
|"ce_queue"|"pk_ce_queue"|"id"|
|"ce_scanner_context"|"pk_ce_scanner_context"|"task_uuid"|
|"ce_task_input"|"pk_ce_task_input"|"task_uuid"|
|"default_qprofiles"|"pk_default_qprofiles"|"organization_uuid"|
|"default_qprofiles"|"pk_default_qprofiles"|"language"|
|"duplications_index"|"pk_duplications_index"|"id"|
|"es_queue"|"pk_es_queue"|"uuid"|
|"events"|"pk_events"|"id"|
|"file_sources"|"pk_file_sources"|"id"|
|"group_roles"|"pk_group_roles"|"id"|
|"groups"|"pk_groups"|"id"|
|"internal_properties"|"pk_internal_properties"|"kee"|
|"issue_changes"|"pk_issue_changes"|"id"|
|"issues"|"pk_issues"|"id"|
|"loaded_templates"|"pk_loaded_templates"|"id"|
|"manual_measures"|"pk_manual_measures"|"id"|
|"metrics"|"pk_metrics"|"id"|
|"notifications"|"pk_notifications"|"id"|
|"org_qprofiles"|"pk_org_qprofiles"|"uuid"|
|"organization_members"|"pk_organization_members"|"user_id"|
|"organization_members"|"pk_organization_members"|"organization_uuid"|
|"organizations"|"pk_organizations"|"uuid"|
|"perm_templates_groups"|"pk_perm_templates_groups"|"id"|
|"perm_templates_users"|"pk_perm_templates_users"|"id"|
|"perm_tpl_characteristics"|"pk_perm_tpl_characteristics"|"id"|
|"permission_templates"|"pk_permission_templates"|"id"|
|"project_links"|"pk_project_links"|"id"|
|"project_measures"|"pk_project_measures"|"id"|
|"project_qprofiles"|"pk_project_qprofiles"|"id"|
|"projects"|"pk_projects"|"id"|
|"properties"|"pk_properties"|"id"|
|"qprofile_changes"|"pk_qprofile_changes"|"kee"|
|"quality_gate_conditions"|"pk_quality_gate_conditions"|"id"|
|"quality_gates"|"pk_quality_gates"|"id"|
|"rule_repositories"|"pk_rule_repositories"|"kee"|
|"rules"|"pk_rules"|"id"|
|"rules_metadata"|"pk_rules_metadata"|"rule_id"|
|"rules_metadata"|"pk_rules_metadata"|"organization_uuid"|
|"rules_parameters"|"pk_rules_parameters"|"id"|
|"rules_profiles"|"pk_rules_profiles"|"id"|
|"snapshots"|"pk_snapshots"|"id"|
|"user_roles"|"pk_user_roles"|"id"|
|"user_tokens"|"pk_user_tokens"|"id"|
|"users"|"pk_users"|"id"|
|"webhook_deliveries"|"pk_webhook_deliveries"|"uuid"|
|"active_rule_parameters"|"pk_active_rule_parameters"|"id"|
|"active_rules"|"pk_active_rules"|"id"|
|"alm_app_installs"|"pk_alm_app_installs"|"uuid"|
|"analysis_properties"|"pk_analysis_properties"|"uuid"|
|"ce_activity"|"pk_ce_activity"|"id"|
|"ce_queue"|"pk_ce_queue"|"id"|
|"ce_scanner_context"|"pk_ce_scanner_context"|"task_uuid"|
|"ce_task_characteristics"|"pk_ce_task_characteristics"|"uuid"|
|"ce_task_input"|"pk_ce_task_input"|"task_uuid"|
|"ce_task_message"|"pk_ce_task_message"|"uuid"|
|"default_qprofiles"|"pk_default_qprofiles"|"organization_uuid"|
|"default_qprofiles"|"pk_default_qprofiles"|"language"|
|"deprecated_rule_keys"|"pk_deprecated_rule_keys"|"uuid"|
|"duplications_index"|"pk_duplications_index"|"id"|
|"es_queue"|"pk_es_queue"|"uuid"|
|"event_component_changes"|"pk_event_component_changes"|"uuid"|
|"events"|"pk_events"|"id"|
|"file_sources"|"pk_file_sources"|"id"|
|"group_roles"|"pk_group_roles"|"id"|
|"groups"|"pk_groups"|"id"|
|"internal_component_props"|"pk_internal_component_props"|"uuid"|
|"internal_properties"|"pk_internal_properties"|"kee"|
|"issue_changes"|"pk_issue_changes"|"id"|
|"issues"|"pk_issues"|"id"|
|"live_measures"|"pk_live_measures"|"uuid"|
|"manual_measures"|"pk_manual_measures"|"id"|
|"metrics"|"pk_metrics"|"id"|
|"notifications"|"pk_notifications"|"id"|
|"org_qprofiles"|"pk_org_qprofiles"|"uuid"|
|"org_quality_gates"|"pk_org_quality_gates"|"uuid"|
|"organization_alm_bindings"|"pk_organization_alm_bindings"|"uuid"|
|"organization_members"|"pk_organization_members"|"organization_uuid"|
|"organization_members"|"pk_organization_members"|"user_id"|
|"organizations"|"pk_organizations"|"uuid"|
|"perm_templates_groups"|"pk_perm_templates_groups"|"id"|
|"perm_templates_users"|"pk_perm_templates_users"|"id"|
|"perm_tpl_characteristics"|"pk_perm_tpl_characteristics"|"id"|
|"permission_templates"|"pk_permission_templates"|"id"|
|"plugins"|"pk_plugins"|"uuid"|
|"project_alm_bindings"|"pk_project_alm_bindings"|"uuid"|
|"project_branches"|"pk_project_branches"|"uuid"|
|"project_links"|"pk_project_links2"|"uuid"|
|"project_mappings"|"pk_project_mappings"|"uuid"|
|"project_measures"|"pk_project_measures"|"id"|
|"project_qprofiles"|"pk_project_qprofiles"|"id"|
|"projects"|"pk_projects"|"id"|
|"properties"|"pk_properties"|"id"|
|"qprofile_changes"|"pk_qprofile_changes"|"kee"|
|"qprofile_edit_groups"|"pk_qprofile_edit_groups"|"uuid"|
|"qprofile_edit_users"|"pk_qprofile_edit_users"|"uuid"|
|"quality_gate_conditions"|"pk_quality_gate_conditions"|"id"|
|"quality_gates"|"pk_quality_gates"|"id"|
|"rule_repositories"|"pk_rule_repositories"|"kee"|
|"rules"|"pk_rules"|"id"|
|"rules_metadata"|"pk_rules_metadata"|"rule_id"|
|"rules_metadata"|"pk_rules_metadata"|"organization_uuid"|
|"rules_parameters"|"pk_rules_parameters"|"id"|
|"rules_profiles"|"pk_rules_profiles"|"id"|
|"snapshots"|"pk_snapshots"|"id"|
|"user_properties"|"pk_user_properties"|"uuid"|
|"user_roles"|"pk_user_roles"|"id"|
|"user_tokens"|"pk_user_tokens"|"id"|
|"users"|"pk_users"|"id"|
|"webhook_deliveries"|"pk_webhook_deliveries"|"uuid"|
|"webhooks"|"pk_webhooks"|"uuid"|
|"active_rule_parameters"|"pk_active_rule_parameters"|"id"|
|"active_rules"|"pk_active_rules"|"id"|
|"alm_app_installs"|"pk_alm_app_installs"|"uuid"|
|"alm_pats"|"pk_alm_pats"|"uuid"|
|"alm_settings"|"pk_alm_settings"|"uuid"|
|"analysis_properties"|"pk_analysis_properties"|"uuid"|
|"ce_activity"|"pk_ce_activity"|"id"|
|"ce_queue"|"pk_ce_queue"|"id"|
|"ce_scanner_context"|"pk_ce_scanner_context"|"task_uuid"|
|"ce_task_characteristics"|"pk_ce_task_characteristics"|"uuid"|
|"ce_task_input"|"pk_ce_task_input"|"task_uuid"|
|"ce_task_message"|"pk_ce_task_message"|"uuid"|
|"default_qprofiles"|"pk_default_qprofiles"|"language"|
|"default_qprofiles"|"pk_default_qprofiles"|"organization_uuid"|
|"deprecated_rule_keys"|"pk_deprecated_rule_keys"|"uuid"|
|"duplications_index"|"pk_duplications_index"|"id"|
|"es_queue"|"pk_es_queue"|"uuid"|
|"event_component_changes"|"pk_event_component_changes"|"uuid"|
|"events"|"pk_events"|"id"|
|"file_sources"|"pk_file_sources"|"id"|
|"group_roles"|"pk_group_roles"|"id"|
|"groups"|"pk_groups"|"id"|
|"internal_component_props"|"pk_internal_component_props"|"uuid"|
|"internal_properties"|"pk_internal_properties"|"kee"|
|"issue_changes"|"pk_issue_changes"|"id"|
|"issues"|"pk_issues"|"id"|
|"live_measures"|"pk_live_measures"|"uuid"|
|"manual_measures"|"pk_manual_measures"|"id"|
|"metrics"|"pk_metrics"|"id"|
|"new_code_periods"|"pk_new_code_periods"|"uuid"|
|"notifications"|"pk_notifications"|"id"|
|"org_qprofiles"|"pk_org_qprofiles"|"uuid"|
|"org_quality_gates"|"pk_org_quality_gates"|"uuid"|
|"organization_alm_bindings"|"pk_organization_alm_bindings"|"uuid"|
|"organization_members"|"pk_organization_members"|"user_id"|
|"organization_members"|"pk_organization_members"|"organization_uuid"|
|"organizations"|"pk_organizations"|"uuid"|
|"perm_templates_groups"|"pk_perm_templates_groups"|"id"|
|"perm_templates_users"|"pk_perm_templates_users"|"id"|
|"perm_tpl_characteristics"|"pk_perm_tpl_characteristics"|"id"|
|"permission_templates"|"pk_permission_templates"|"id"|
|"plugins"|"pk_plugins"|"uuid"|
|"project_alm_bindings"|"pk_project_alm_bindings"|"uuid"|
|"project_alm_settings"|"pk_project_alm_settings"|"uuid"|
|"project_branches"|"pk_project_branches"|"uuid"|
|"project_links"|"pk_project_links"|"uuid"|
|"project_mappings"|"pk_project_mappings"|"uuid"|
|"project_measures"|"pk_project_measures"|"id"|
|"project_qgates"|"pk_project_qgates"|"project_uuid"|
|"project_qprofiles"|"pk_project_qprofiles"|"id"|
|"projects"|"pk_new_projects"|"uuid"|
|"properties"|"pk_properties"|"id"|
|"qprofile_changes"|"pk_qprofile_changes"|"kee"|
|"qprofile_edit_groups"|"pk_qprofile_edit_groups"|"uuid"|
|"qprofile_edit_users"|"pk_qprofile_edit_users"|"uuid"|
|"quality_gate_conditions"|"pk_quality_gate_conditions"|"id"|
|"quality_gates"|"pk_quality_gates"|"id"|
|"rule_repositories"|"pk_rule_repositories"|"kee"|
|"rules"|"pk_rules"|"id"|
|"rules_metadata"|"pk_rules_metadata"|"organization_uuid"|
|"rules_metadata"|"pk_rules_metadata"|"rule_id"|
|"rules_parameters"|"pk_rules_parameters"|"id"|
|"rules_profiles"|"pk_rules_profiles"|"id"|
|"snapshots"|"pk_snapshots"|"id"|
|"user_properties"|"pk_user_properties"|"uuid"|
|"user_roles"|"pk_user_roles"|"id"|
|"user_tokens"|"pk_user_tokens"|"id"|
|"users"|"pk_users"|"id"|
|"webhook_deliveries"|"pk_webhook_deliveries"|"uuid"|
|"webhooks"|"pk_webhooks"|"uuid"|
|"active_rule_parameters"|"active_rule_parameters_pkey"|"id"|
|"active_rules"|"active_rules_pkey"|"id"|
|"ce_activity"|"ce_activity_pkey"|"id"|
|"ce_queue"|"ce_queue_pkey"|"id"|
|"ce_scanner_context"|"ce_scanner_context_pkey"|"task_uuid"|
|"ce_task_input"|"ce_task_input_pkey"|"task_uuid"|
|"default_qprofiles"|"default_qprofiles_pkey"|"organization_uuid"|
|"default_qprofiles"|"default_qprofiles_pkey"|"language"|
|"duplications_index"|"duplications_index_pkey"|"id"|
|"es_queue"|"es_queue_pkey"|"uuid"|
|"events"|"events_pkey"|"id"|
|"file_sources"|"file_sources_pkey"|"id"|
|"group_roles"|"group_roles_pkey"|"id"|
|"groups"|"groups_pkey"|"id"|
|"internal_properties"|"internal_properties_pkey"|"kee"|
|"issue_changes"|"issue_changes_pkey"|"id"|
|"issues"|"issues_pkey"|"id"|
|"loaded_templates"|"loaded_templates_pkey"|"id"|
|"manual_measures"|"manual_measures_pkey"|"id"|
|"metrics"|"metrics_pkey"|"id"|
|"notifications"|"notifications_pkey"|"id"|
|"org_qprofiles"|"org_qprofiles_pkey"|"uuid"|
|"organization_members"|"organization_members_pkey"|"user_id"|
|"organization_members"|"organization_members_pkey"|"organization_uuid"|
|"organizations"|"organizations_pkey"|"uuid"|
|"perm_templates_groups"|"perm_templates_groups_pkey"|"id"|
|"perm_templates_users"|"perm_templates_users_pkey"|"id"|
|"perm_tpl_characteristics"|"perm_tpl_characteristics_pkey"|"id"|
|"permission_templates"|"permission_templates_pkey"|"id"|
|"project_links"|"project_links_pkey"|"id"|
|"project_measures"|"project_measures_pkey"|"id"|
|"project_qprofiles"|"project_qprofiles_pkey"|"id"|
|"projects"|"projects_pkey"|"id"|
|"properties"|"properties_pkey"|"id"|
|"qprofile_changes"|"qprofile_changes_pkey"|"kee"|
|"quality_gate_conditions"|"quality_gate_conditions_pkey"|"id"|
|"quality_gates"|"quality_gates_pkey"|"id"|
|"rule_repositories"|"rule_repositories_pkey"|"kee"|
|"rules"|"rules_pkey"|"id"|
|"rules_metadata"|"rules_metadata_pkey"|"organization_uuid"|
|"rules_metadata"|"rules_metadata_pkey"|"rule_id"|
|"rules_parameters"|"rules_parameters_pkey"|"id"|
|"rules_profiles"|"rules_profiles_pkey"|"id"|
|"snapshots"|"snapshots_pkey"|"id"|
|"user_roles"|"user_roles_pkey"|"id"|
|"user_tokens"|"user_tokens_pkey"|"id"|
|"users"|"users_pkey"|"id"|
|"webhook_deliveries"|"webhook_deliveries_pkey"|"uu|id"
Thank you!
@pierreguillot apparently the tables and indexes in the sonarqube index are correct but not the ones in public. This means that the idx ones are ācorrectā or at least the ones used. What I actually found was that I needed to remove all of the tables from public (this is only for sonar so they were all sonar tables). Once I did that the upgrade almost worked but ended up dying after it tried to start up on the new version with the following error:
ERROR web[][o.s.s.p.Platform] Background initialization failed. Stopping SonarQube
java.lang.IllegalArgumentException: Comparison method violates its general contract!
at java.base/java.util.TimSort.mergeLo(TimSort.java:781)
at java.base/java.util.TimSort.mergeAt(TimSort.java:518)
at java.base/java.util.TimSort.mergeCollapse(TimSort.java:448)
at java.base/java.util.TimSort.sort(TimSort.java:245)
at java.base/java.util.Arrays.sort(Arrays.java:1515)
at java.base/java.util.ArrayList.sort(ArrayList.java:1750)
at org.sonar.server.issue.index.AsyncIssueIndexingImpl.sortProjectUuids(AsyncIssueIndexingImpl.java:104)
at org.sonar.server.issue.index.AsyncIssueIndexingImpl.triggerOnIndexCreation(AsyncIssueIndexingImpl.java:81)
at org.sonar.server.issue.index.IssueIndexer.triggerAsyncIndexOnStartup(IssueIndexer.java:102)
at org.sonar.server.es.IndexerStartupTask.asynchronousIndexing(IndexerStartupTask.java:94)
at org.sonar.server.es.IndexerStartupTask.indexUninitializedTypes(IndexerStartupTask.java:73)
at java.base/java.util.Spliterators$ArraySpliterator.forEachRemaining(Spliterators.java:948)
at java.base/java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:658)
at org.sonar.server.es.IndexerStartupTask.execute(IndexerStartupTask.java:55)
at java.base/java.util.Optional.ifPresent(Optional.java:183)
at org.sonar.server.platform.platformlevel.PlatformLevelStartup$1.doPrivileged(PlatformLevelStartup.java:86)
at org.sonar.server.user.DoPrivileged.execute(DoPrivileged.java:46)
at org.sonar.server.platform.platformlevel.PlatformLevelStartup.start(PlatformLevelStartup.java:82)
at org.sonar.server.platform.PlatformImpl.executeStartupTasks(PlatformImpl.java:198)
at org.sonar.server.platform.PlatformImpl.access$400(PlatformImpl.java:46)
at org.sonar.server.platform.PlatformImpl$1.lambda$doRun$1(PlatformImpl.java:122)
at org.sonar.server.platform.PlatformImpl$AutoStarterRunnable.runIfNotAborted(PlatformImpl.java:370)
at org.sonar.server.platform.PlatformImpl$1.doRun(PlatformImpl.java:122)
at org.sonar.server.platform.PlatformImpl$AutoStarterRunnable.run(PlatformImpl.java:354)
at java.base/java.lang.Thread.run(Thread.java:834)
Any thoughts?
It looks like you also have your indexes duplicates. Do you have several SonarQube schema on your database?
Hi @pierreguillot
We do, but they are living there under different names just as backup I believeā¦ So what should be our action in this case?
The action would be to have only one sonarqube schema on your database, to not confuse the migration.
@pierreguillot I found that after I posted but it wouldnāt let me add another post last night because I am a new user. I am not in a working state in my dev environment and will update my production environment today.
Thank you for all your help!
I had the exact same issue. I solved it by dropping a backup sonarqube schema that was on same postgres database