Upgrade from 17.8.7 → 17.11.3 fails with there is no unique constraint matching given keys for referenced table "instance_type_ci_runners_e59bb2812d"

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Summary

When upgrading, based on the upgrade path, from 17.8.7 → 17.11.3 encountering the below error on the migrations part:

Caused by:
PG::InvalidForeignKey: ERROR:  there is no unique constraint matching given keys for referenced table "instance_type_ci_runners_e59bb2812d"
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:48:in `exec'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:48:in `block (2 levels) in execute'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/dependencies/interlock.rb:41:in `permit_concurrent_loads'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `block in execute'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/abstract_adapter.rb:752:in `block in log'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/abstract_adapter.rb:743:in `log'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:46:in `execute'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/marginalia-1.11.1/lib/marginalia.rb:71:in `execute_with_marginalia'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/abstract/schema_statements.rb:326:in `create_table'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:965:in `block in method_missing'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:935:in `block in say_with_time'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:935:in `say_with_time'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:954:in `method_missing'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:556:in `create_table'
/srv/gitlab/lib/gitlab/database/migration_helpers/v2.rb:28:in `create_table'
/srv/gitlab/db/migrate/20250213145404_create_ci_hosted_runners.rb:7:in `change'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:905:in `exec_migration'
/srv/gitlab/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/srv/gitlab/lib/gitlab/database/query_analyzer.rb:83:in `within'
/srv/gitlab/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/srv/gitlab/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:889:in `block (2 levels) in migrate'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:888:in `block in migrate'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:215:in `with_connection'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:887:in `migrate'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1095:in `migrate'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1409:in `block in execute_migration_in_transaction'
/srv/gitlab/lib/gitlab/database/with_lock_retries.rb:123:in `run_block'
/srv/gitlab/lib/gitlab/database/with_lock_retries.rb:134:in `block in run_block_with_lock_timeout'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/abstract/transaction.rb:319:in `block in within_new_transaction'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activesupport-7.0.8.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/abstract/transaction.rb:317:in `within_new_transaction'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/abstract/database_statements.rb:316:in `transaction'
/srv/gitlab/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send'
/srv/gitlab/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer'
/srv/gitlab/lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
/srv/gitlab/lib/gitlab/database/load_balancing/load_balancer.rb:228:in `retry_with_backoff'
/srv/gitlab/lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
/srv/gitlab/lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer'
/srv/gitlab/lib/gitlab/database/load_balancing/connection_proxy.rb:78:in `transaction'
/srv/gitlab/lib/gitlab/database/with_lock_retries.rb:129:in `run_block_with_lock_timeout'
/srv/gitlab/lib/gitlab/database/with_lock_retries.rb:97:in `run'
/srv/gitlab/lib/gitlab/database/migrations/lock_retry_mixin.rb:52:in `ddl_transaction'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1408:in `execute_migration_in_transaction'
/srv/gitlab/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1382:in `each'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1382:in `migrate_without_lock'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1331:in `block in migrate'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1481:in `block in with_advisory_lock'
/srv/gitlab/lib/gitlab/database/migrations/pg_backend_pid.rb:28:in `block in with_advisory_lock_connection'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:215:in `with_connection'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1496:in `with_advisory_lock_connection'
/srv/gitlab/lib/gitlab/database/migrations/pg_backend_pid.rb:25:in `with_advisory_lock_connection'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1477:in `with_advisory_lock'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1331:in `migrate'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1166:in `up'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/migration.rb:1141:in `migrate'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/tasks/database_tasks.rb:262:in `migrate'
/srv/gitlab/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.7/lib/active_record/railties/databases.rake:92:in `block (2 levels) in <main>'
/srv/gitlab/lib/tasks/gitlab/db.rake:148:in `configure_database'
/srv/gitlab/lib/tasks/gitlab/db.rake:115:in `configure_pg_databases'
/srv/gitlab/lib/tasks/gitlab/db.rake:102:in `block (3 levels) in <main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
Running db:migrate rake task
main: == [advisory_lock_connection] object_id: 51540, pg_backend_pid: 32966
main: == 20250213145404 CreateCiHostedRunners: migrating ============================
main: -- create_table(:ci_hosted_runners, {:id=>false})
main: == [advisory_lock_connection] object_id: 51540, pg_backend_pid: 32966

It looks like there might be an issue with the below migrations not running in sequence or something:

add unique id constrain is added in 17.9: https://gitlab.com/gitlab-org/gitlab/-/blob/master/db/post_migrate/20250128131232_add_unique_id_constraint_on_ci_runners_table_partitions.rb

create ci hosted runners table in 17.10 https://gitlab.com/gitlab-org/gitlab/-/blob/master/db/migrate/20250213145404_create_ci_hosted_runners.rb

To bypass this error we can upgrade to the latest v17.9 first.

Workaround

Use upgrade path 17.8.7 → 17.9.8 → 17.11.3 instead of 17.8.7 → 17.11.3