Key (pipeline_id_convert_to_bigint)=(58598) is not present in table "ci_pipelines".
Hi,
I'm trying to upgrade from 16.3.6 to 16.6.1. we are using the official docker container, and receiving the error below.
PG::ForeignKeyViolation: ERROR: insert or update on table "ci_pipeline_variables" violates foreign key constraint "temp_fk_rails_8d3b04e3e1"
DETAIL: Key (pipeline_id_convert_to_bigint)=(58598) is not present in table "ci_pipelines".
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:303:in `block in validate_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/timeout_helpers.rb:26:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:302:in `validate_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230901044003_add_sync_foreign_key_for_ci_pipeline_variables_pipeline_id.rb:9: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:37: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/load_balancing/connection_proxy.rb:127:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:137:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:224:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:126:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:78:in `transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database.rb:359:in `block in transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database.rb:358:in `transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:36: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:126:in `configure_database'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:101:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'Here is what ci_pipelines table looks like right now.
gitlab-prod-# \d ci_pipelines
Table "public.ci_pipelines"
Column | Type | Collation | Nullable | Default
---------------------------------------+-----------------------------+-----------+----------+------------------------------------------
id | integer | | not null | nextval('ci_pipelines_id_seq'::regclass)
ref | character varying(255) | | |
sha | character varying(255) | | |
before_sha | character varying(255) | | |
created_at | timestamp without time zone | | |
updated_at | timestamp without time zone | | |
tag | boolean | | | false
yaml_errors | text | | |
committed_at | timestamp without time zone | | |
project_id | integer | | |
status | character varying | | |
started_at | timestamp without time zone | | |
finished_at | timestamp without time zone | | |
duration | integer | | |
user_id | integer | | |
lock_version | integer | | | 0
auto_canceled_by_id | integer | | |
pipeline_schedule_id | integer | | |
source | integer | | |
protected | boolean | | |
config_source | integer | | |
failure_reason | integer | | |
iid | integer | | |
merge_request_id | integer | | |
source_sha | bytea | | |
target_sha | bytea | | |
external_pull_request_id | bigint | | |
ci_ref_id | bigint | | |
locked | smallint | | not null | 1
partition_id | bigint | | not null |
id_convert_to_bigint | bigint | | not null | 0
auto_canceled_by_id_convert_to_bigint | bigint | | |
Indexes:
"ci_pipelines_pkey" PRIMARY KEY, btree (id)
"idx_ci_pipelines_artifacts_locked" btree (ci_ref_id, id) WHERE locked = 1
"index_ci_pipelines_for_ondemand_dast_scans" btree (id) WHERE source = 13
"index_ci_pipelines_on_auto_canceled_by_id" btree (auto_canceled_by_id)
"index_ci_pipelines_on_ci_ref_id_and_more" btree (ci_ref_id, id DESC, source, status) WHERE ci_ref_id IS NOT NULL
"index_ci_pipelines_on_external_pull_request_id" btree (external_pull_request_id) WHERE external_pull_request_id IS NOT NULL
"index_ci_pipelines_on_merge_request_id" btree (merge_request_id) WHERE merge_request_id IS NOT NULL
"index_ci_pipelines_on_pipeline_schedule_id_and_id" btree (pipeline_schedule_id, id)
"index_ci_pipelines_on_project_id_and_id_desc" btree (project_id, id DESC)
"index_ci_pipelines_on_project_id_and_iid" UNIQUE, btree (project_id, iid) WHERE iid IS NOT NULL
"index_ci_pipelines_on_project_id_and_ref_and_status_and_id" btree (project_id, ref, status, id)
"index_ci_pipelines_on_project_id_and_sha" btree (project_id, sha)
"index_ci_pipelines_on_project_id_and_source" btree (project_id, source)
"index_ci_pipelines_on_project_id_and_status_and_config_source" btree (project_id, status, config_source)
"index_ci_pipelines_on_project_id_and_status_and_created_at" btree (project_id, status, created_at)
"index_ci_pipelines_on_project_id_and_status_and_updated_at" btree (project_id, status, updated_at)
"index_ci_pipelines_on_project_id_and_user_id_and_status_and_ref" btree (project_id, user_id, status, ref) WHERE source <> 12
"index_ci_pipelines_on_project_idandrefandiddesc" btree (project_id, ref, id DESC)
"index_ci_pipelines_on_status_and_id" btree (status, id)
"index_ci_pipelines_on_user_id_and_created_at_and_config_source" btree (user_id, created_at, config_source)
"index_ci_pipelines_on_user_id_and_created_at_and_source" btree (user_id, created_at, source)
"index_ci_pipelines_on_user_id_and_id_and_cancelable_status" btree (user_id, id) WHERE status::text = ANY (ARRAY['running'::character varying::text, 'waiting_for_resource'::c
haracter varying::text, 'preparing'::character varying::text, 'pending'::character varying::text, 'created'::character varying::text, 'scheduled'::character varying::text])
"index_ci_pipelines_on_user_id_and_id_desc_and_user_not_verified" btree (user_id, id DESC) WHERE failure_reason = 3
Foreign-key constraints:
"fk_190998ef09" FOREIGN KEY (external_pull_request_id) REFERENCES external_pull_requests(id) ON DELETE SET NULL
"fk_262d4c2d19" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL
"fk_d80e161c54" FOREIGN KEY (ci_ref_id) REFERENCES ci_refs(id) ON DELETE SET NULL
Referenced by:
TABLE "ci_pipelines" CONSTRAINT "fk_262d4c2d19" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL
TABLE "p_ci_builds" CONSTRAINT "fk_87f4cefcda" FOREIGN KEY (upstream_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_stages" CONSTRAINT "fk_fb57e6cc56" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_sources_projects" CONSTRAINT "fk_rails_10a1eb379a" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_metadata" CONSTRAINT "fk_rails_50c1e9ea10" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_chat_data" CONSTRAINT "fk_rails_64ebfab6b3" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_messages" CONSTRAINT "fk_rails_8d3b04e3e1" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_variables" CONSTRAINT "temp_fk_rails_8d3b04e3e1" FOREIGN KEY (pipeline_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE NOT VALID
Triggers:
ci_pipelines_loose_fk_trigger AFTER DELETE ON ci_pipelines REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_recor
ds()
trigger_1bd97da9c1a4 BEFORE INSERT OR UPDATE ON ci_pipelines FOR EACH ROW EXECUTE FUNCTION trigger_1bd97da9c1a4()
trigger_b2d852e1e2cb BEFORE INSERT OR UPDATE ON ci_pipelines FOR EACH ROW EXECUTE FUNCTION trigger_b2d852e1e2cb()And then for ci_pipeline_variables
gitlab-prod-# \d ci_pipeline_variables
Table "public.ci_pipeline_variables"
Column | Type | Collation | Nullable | Default
-------------------------------+-------------------+-----------+----------+---------------------------------------------------
key | character varying | | not null |
value | text | | |
encrypted_value | text | | |
encrypted_value_salt | character varying | | |
encrypted_value_iv | character varying | | |
pipeline_id | integer | | not null |
variable_type | smallint | | not null | 1
partition_id | bigint | | not null |
raw | boolean | | not null | false
id | bigint | | not null | nextval('ci_pipeline_variables_id_seq'::regclass)
pipeline_id_convert_to_bigint | bigint | | not null | 0
Indexes:
"ci_pipeline_variables_pkey" PRIMARY KEY, btree (id)
"index_ci_pipeline_variables_on_pipeline_id_and_key" UNIQUE, btree (pipeline_id, key)
"index_ci_pipeline_variables_on_pipeline_id_bigint_and_key" UNIQUE, btree (pipeline_id_convert_to_bigint, key)
Foreign-key constraints:
"temp_fk_rails_8d3b04e3e1" FOREIGN KEY (pipeline_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE NOT VALID
Triggers:
trigger_7f3d66a7d7f5 BEFORE INSERT OR UPDATE ON ci_pipeline_variables FOR EACH ROW EXECUTE FUNCTION trigger_7f3d66a7d7f5()I am not exactly sure what is wrong. I've been researching for weeks on how I can fix this.
Thanks,
Blake