17.2.1 upgrade failure: unknown sequences cause migration to fail

Related ZD tickets:

The migration in !160139 (merged) appears to have issues when an unknown sequence is provided:

rails_migration[gitlab-rails] (gitlab::database_migrations line 51) had an error: Mixlib::ShellOut::ShellCommandFailed: bash_hide_env[migrate gitlab-rails database] (gitlab::database_migrations line 20) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received '1'
---- Begin output of "bash" ----
STDOUT: rake aborted!
StandardError: An error has occurred, all later migrations canceled:

key not found: "geo_reset_checksum_events_id_seq"
Did you mean? "geo_events_id_seq"
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240709014000_fix_sequence_owners_for_ci_builds.rb:668:in `fetch'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240709014000_fix_sequence_owners_for_ci_builds.rb:668:in `block in up'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240709014000_fix_sequence_owners_for_ci_builds.rb:667:in `each'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240709014000_fix_sequence_owners_for_ci_builds.rb:667:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:40:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:54:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:136:in `configure_database'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:107:in `configure_pg_databases'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:94:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'

Caused by:
KeyError: key not found: "geo_reset_checksum_events_id_seq"
Did you mean? "geo_events_id_seq"
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240709014000_fix_sequence_owners_for_ci_builds.rb:668:in `fetch'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240709014000_fix_sequence_owners_for_ci_builds.rb:668:in `block in up'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240709014000_fix_sequence_owners_for_ci_builds.rb:667:in `each'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240709014000_fix_sequence_owners_for_ci_builds.rb:667:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:40:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:54:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:136:in `configure_database'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:107:in `configure_pg_databases'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:94:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25: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: 57080, pg_backend_pid: 381
main: == 20240709014000 FixSequenceOwnersForCiBuilds: migrating =====================
main: -- select_all(" SELECT s.relname AS seq_name,\n t.relname AS table_name,\n a.attname AS column_name\n FROM pg_class s\n JOIN pg_depend d ON d.objid = s.oid\n AND d.classid = 'pg_class'::regclass\n AND d.refclassid = 'pg_class'::regclass\n JOIN pg_catalog.pg_class t ON t.oid = d.refobjid\n JOIN pg_catalog.pg_namespace n ON n.oid = t.relnamespace\n JOIN pg_catalog.pg_attribute a ON a.attrelid = t.oid\n AND a.attnum = d.refobjsubid\n WHERE s.relkind = 'S'\n AND (t.relname = 'ci_builds'\n OR t.relname = 'p_ci_builds');\n")
main: -> 0.0179s
main: == [advisory_lock_connection] object_id: 57080, pg_backend_pid: 381
STDERR:
---- End output of "bash" ----
Ran "bash" returned 1

Current workaround

In the database console (e.g. gitlab-psql), run:

DROP SEQUENCE IF EXISTS ci_editor_ai_conversation_messages_id_seq
DROP SEQUENCE IF EXISTS ci_partitions_id_seq
DROP SEQUENCE IF EXISTS dast_scanner_profiles_tags_id_seq
DROP SEQUENCE IF EXISTS external_approval_rules_protected_branches_id_seq
DROP SEQUENCE IF EXISTS geo_hashed_storage_attachments_events_id_seq
DROP SEQUENCE IF EXISTS geo_hashed_storage_migrated_events_id_seq
DROP SEQUENCE IF EXISTS geo_repositories_changed_events_id_seq
DROP SEQUENCE IF EXISTS geo_repository_created_events_id_seq
DROP SEQUENCE IF EXISTS geo_repository_deleted_events_id_seq
DROP SEQUENCE IF EXISTS geo_repository_renamed_events_id_seq
DROP SEQUENCE IF EXISTS geo_repository_updated_events_id_seq
DROP SEQUENCE IF EXISTS geo_reset_checksum_events_id_seq
DROP SEQUENCE IF EXISTS in_product_marketing_emails_id_seq
DROP SEQUENCE IF EXISTS member_tasks_id_seq
DROP SEQUENCE IF EXISTS merge_request_diff_llm_summaries_id_seq
DROP SEQUENCE IF EXISTS product_analytics_events_experimental_id_seq
DROP SEQUENCE IF EXISTS project_repository_states_id_seq
DROP SEQUENCE IF EXISTS sbom_vulnerable_component_versions_id_seq
DROP SEQUENCE IF EXISTS schema_inconsistencies_id_seq
DROP SEQUENCE IF EXISTS u2f_registrations_id_seq
DROP SEQUENCE IF EXISTS vulnerability_advisories_id_seq
DROP SEQUENCE IF EXISTS zoekt_indexed_namespaces_id_seq

If you are seeing errors with namespace_root_storage_statistics_namespace_id_seq, you can just ignore this and mark the migration complete:

gitlab-rake "gitlab:db:mark_migration_complete[20240709014000]"

Fixes

!160609 (merged) will fix this migration to ignore orphaned sequences.
!160624 (merged) will drop these orphaned sequences.

Edited Jul 26, 2024 by Stan Hu