Fix foreign key and index names on `ci_sources_pipelines` for old schemas
What does this MR do and why?
When the ci_sources_pipelines table was first introduced in GitLab 9.3
EE, the foreign key names generated for the table appeared to have been
calculated via a hash using the table name
ci_pipeline_source_pipelines. This led to a merge conflict and
confusion during a CE to EE merge in GitLab 10.0, which regenerated the
schema with the correct foreign key names.
Hence anyone who installed GitLab prior to 10.0 may have been seeded the database with stale, incorrect foreign key names.
During the Great BigInt Conversion of 2021, the migrations assumed that:
- The foreign key
fk_be5624bf37existed forci_sources_pipelines. - The index
index_ci_sources_pipelines_on_source_job_idexisted.
However, older installations may have had the foreign key under the
original fk_3f0c88d7dc and
index_ci_pipeline_source_pipelines_on_source_job_id, respectively.
To eliminate future confusion and migration failures, we now rename the foreign key constraints and index to what they should be today.
NOTE: This is a NOP on GitLab.com and for instances that have the correct foreign key names.
Relates to #339844 (closed)
Screenshots or screen recordings
Before
The upgrade from 14.0.x to 14.2.x would fail since index_ci_sources_pipelines_on_source_job_id did not exist:
-- execute("ALTER TABLE \"ci_sources_pipelines\" RENAME COLUMN \"source_job_id\" TO \"source_job_id_tmp\"")
-> 0.0002s
-- quote_table_name("ci_sources_pipelines")
-> 0.0000s
-- quote_column_name(:source_job_id_convert_to_bigint)
-> 0.0000s
-- quote_column_name(:source_job_id)
-> 0.0000s
-- execute("ALTER TABLE \"ci_sources_pipelines\" RENAME COLUMN \"source_job_id_convert_to_bigint\" TO \"source_job_id\"")
-> 0.0002s
-- quote_table_name("ci_sources_pipelines")
-> 0.0000s
-- quote_column_name("source_job_id_tmp")
-> 0.0000s
-- quote_column_name(:source_job_id_convert_to_bigint)
-> 0.0000s
-- execute("ALTER TABLE \"ci_sources_pipelines\" RENAME COLUMN \"source_job_id_tmp\" TO \"source_job_id_convert_to_bigint\"")
-> 0.0003s
-- quote_table_name("trigger_8485e97c00e3")
-> 0.0000s
-- execute("ALTER FUNCTION \"trigger_8485e97c00e3\" RESET ALL")
-> 0.0002s
-- execute("DROP INDEX index_ci_sources_pipelines_on_source_job_id")
STDERR:
---- End output of "bash" "/tmp/chef-script20211217-16475-zj8nmr" ----
Ran "bash" "/tmp/chef-script20211217-16475-zj8nmr" returned 1gitlabhq_production=# \d+ ci_sources_pipelines;
Table "public.ci_sources_pipelines"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------------------------+---------+-----------+----------+--------------------------------------------------+---------+--------------+-------------
id | integer | | not null | nextval('ci_sources_pipelines_id_seq'::regclass) | plain | |
project_id | integer | | | | plain | |
pipeline_id | integer | | | | plain | |
source_project_id | integer | | | | plain | |
source_job_id | integer | | | | plain | |
source_pipeline_id | integer | | | | plain | |
source_job_id_convert_to_bigint | bigint | | | | plain | |
Indexes:
"ci_sources_pipelines_pkey" PRIMARY KEY, btree (id)
"index_ci_pipeline_source_pipelines_on_pipeline_id" btree (pipeline_id)
"index_ci_pipeline_source_pipelines_on_project_id" btree (project_id)
"index_ci_pipeline_source_pipelines_on_source_job_id" btree (source_job_id)
"index_ci_pipeline_source_pipelines_on_source_pipeline_id" btree (source_pipeline_id)
"index_ci_pipeline_source_pipelines_on_source_project_id" btree (source_project_id)
"index_ci_sources_pipelines_on_source_job_id_convert_to_bigint" btree (source_job_id_convert_to_bigint)
Foreign-key constraints:
"fk_3a3e3cb83a" FOREIGN KEY (source_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
"fk_3f0c88d7dc" FOREIGN KEY (source_job_id) REFERENCES ci_builds(id) ON DELETE CASCADE
"fk_83b4346e48" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
"fk_8868d0f3e4" FOREIGN KEY (source_project_id) REFERENCES projects(id) ON DELETE CASCADE
"fk_b8c0fac459" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
"fk_be5624bf37_tmp" FOREIGN KEY (source_job_id_convert_to_bigint) REFERENCES ci_builds(id) ON DELETE CASCADE
Triggers:
trigger_8485e97c00e3 BEFORE INSERT OR UPDATE ON ci_sources_pipelines FOR EACH ROW EXECUTE FUNCTION trigger_8485e97c00e3()
Access method: heapAfter
After dropping this patch into a 14.0.x install and running gitlab-rake db:migrate:
-- execute("ALTER TABLE \"ci_sources_pipelines\"\nRENAME CONSTRAINT \"fk_3f0c88d7dc\" TO \"fk_be5624bf37\"\n")
-> 0.0020s
-- foreign_keys("ci_sources_pipelines")
-> 0.0057s
-- quote_table_name("ci_sources_pipelines")
-> 0.0000s
-- quote_column_name("fk_b8c0fac459")
-> 0.0000s
-- quote_column_name("fk_e1bad85861")
-> 0.0000s
-- execute("ALTER TABLE \"ci_sources_pipelines\"\nRENAME CONSTRAINT \"fk_b8c0fac459\" TO \"fk_e1bad85861\"\n")
-> 0.0018s
-- foreign_keys("ci_sources_pipelines")
-> 0.0046s
-- quote_table_name("ci_sources_pipelines")
-> 0.0000s
-- quote_column_name("fk_3a3e3cb83a")
-> 0.0000s
-- quote_column_name("fk_d4e29af7d7")
-> 0.0000s
-- execute("ALTER TABLE \"ci_sources_pipelines\"\nRENAME CONSTRAINT \"fk_3a3e3cb83a\" TO \"fk_d4e29af7d7\"\n")
-> 0.0014s
-- foreign_keys("ci_sources_pipelines")
-> 0.0049s
-- quote_table_name("ci_sources_pipelines")
-> 0.0000s
-- quote_column_name("fk_8868d0f3e4")
-> 0.0000s
-- quote_column_name("fk_acd9737679")
-> 0.0000s
-- execute("ALTER TABLE \"ci_sources_pipelines\"\nRENAME CONSTRAINT \"fk_8868d0f3e4\" TO \"fk_acd9737679\"\n")
-> 0.0014s
-- foreign_keys("ci_sources_pipelines")
-> 0.0048s
-- quote_table_name("ci_sources_pipelines")
-> 0.0000s
-- quote_column_name("fk_83b4346e48")
-> 0.0000s
-- quote_column_name("fk_1e53c97c0a")
-> 0.0000s
-- execute("ALTER TABLE \"ci_sources_pipelines\"\nRENAME CONSTRAINT \"fk_83b4346e48\" TO \"fk_1e53c97c0a\"\n")
-> 0.0015s
-- indexes("ci_sources_pipelines")
-> 0.0058s
-- indexes("ci_sources_pipelines")
-> 0.0032s
-- current_schema()
-> 0.0006s
-- rename_index("ci_sources_pipelines", "index_ci_pipeline_source_pipelines_on_source_job_id", "index_ci_sources_pipelines_on_source_job_id")
-> 0.0014s
== 20210713042000 FixCiSourcesPipelinesIndexNames: migrated (0.0499s) =========gitlabhq_production=# \d+ ci_sources_pipelines;
Table "public.ci_sources_pipelines"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------------------------+---------+-----------+----------+--------------------------------------------------+---------+--------------+-------------
id | integer | | not null | nextval('ci_sources_pipelines_id_seq'::regclass) | plain | |
project_id | integer | | | | plain | |
pipeline_id | integer | | | | plain | |
source_project_id | integer | | | | plain | |
source_job_id_convert_to_bigint | integer | | | | plain | |
source_pipeline_id | integer | | | | plain | |
source_job_id | bigint | | | | plain | |
Indexes:
"ci_sources_pipelines_pkey" PRIMARY KEY, btree (id)
"index_ci_pipeline_source_pipelines_on_pipeline_id" btree (pipeline_id)
"index_ci_pipeline_source_pipelines_on_project_id" btree (project_id)
"index_ci_pipeline_source_pipelines_on_source_pipeline_id" btree (source_pipeline_id)
"index_ci_pipeline_source_pipelines_on_source_project_id" btree (source_project_id)
"index_ci_sources_pipelines_on_source_job_id" btree (source_job_id)
Foreign-key constraints:
"fk_1e53c97c0a" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
"fk_acd9737679" FOREIGN KEY (source_project_id) REFERENCES projects(id) ON DELETE CASCADE
"fk_be5624bf37" FOREIGN KEY (source_job_id) REFERENCES ci_builds(id) ON DELETE CASCADE
"fk_d4e29af7d7" FOREIGN KEY (source_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
"fk_e1bad85861" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
Triggers:
trigger_8485e97c00e3 BEFORE INSERT OR UPDATE ON ci_sources_pipelines FOR EACH ROW EXECUTE FUNCTION trigger_8485e97c00e3()
Access method: heapHow to set up and validate locally
- Install GitLab
v9.5.4-eeand upgrade all the way to GitLab 14.2 or 14.3.💥
Use a clean CentOS 7 VM:
curl -s https://packages.gitlab.com/install/repositories/gitlab/gitlab-ee/script.rpm.sh | sudo bash
sudo yum install -y gitlab-ee-9.5.4-ee.0.el7.x86_64
sudo yum install -y gitlab-ee-10.8.7-ee.0.el7.x86_64
sudo yum install -y gitlab-ee-11.0.2-ee.0.el7.x86_64
sudo yum install -y gitlab-ee-11.11.8-ee.0.el7.x86_64
sudo yum install -y gitlab-ee-12.0.6-ee.0.el7.x86_64
sudo yum install -y gitlab-ee-12.10.14-ee.0.el7.x86_64
sudo yum install -y gitlab-ee-13.0.3-ee.0.el7.x86_64
sudo yum install -y gitlab-ee-13.12.15-ee.0.el7.x86_64
sudo yum install -y gitlab-ee-14.0.6-ee.0.el7.x86_64
sudo yum install -y gitlab-ee-14.2.3-ee.0.el7.x86_64- Apply this diff to the install: (
cd /opt/gitlab/embedded/service/gitlab-rails && sudo patch -p1 < /tmp/76171.diff). -
gitlab-rake db:migrateshould go through.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Stan Hu