From f89cc8b22da1257b284ff195bfe7cac32844ce77 Mon Sep 17 00:00:00 2001 From: Gregory Havenga Date: Wed, 24 Sep 2025 17:13:49 +0200 Subject: [PATCH 1/8] Add two non-ref contextual columns from findings to vulnerabilities --- ...olution_severity_and_cve_to_vulnerabilities.rb | 15 +++++++++++++++ db/schema_migrations/20250924143708 | 1 + db/structure.sql | 6 +++++- 3 files changed, 21 insertions(+), 1 deletion(-) create mode 100644 db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb create mode 100644 db/schema_migrations/20250924143708 diff --git a/db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb b/db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb new file mode 100644 index 00000000000000..f67d29214a9a2d --- /dev/null +++ b/db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class AddSolutionSeverityAndCveToVulnerabilities < Gitlab::Database::Migration[2.3] + milestone '18.5' + + disable_ddl_transaction! + + def change + add_column :vulnerabilities, :solution, :text + add_column :vulnerabilities, :cve, :text + + add_text_limit :vulnerabilities, :solution, 7000 + add_text_limit :vulnerabilities, :cve, 48400 + end +end diff --git a/db/schema_migrations/20250924143708 b/db/schema_migrations/20250924143708 new file mode 100644 index 00000000000000..9cf29a1c3b9e5f --- /dev/null +++ b/db/schema_migrations/20250924143708 @@ -0,0 +1 @@ +a7d71864c6c00c7715c774b062a14b2bdaebdabaed615af0c5a48bd1db6704a8 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 387820a3fa389b..91db2b0d0f6691 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -28042,7 +28042,11 @@ CREATE TABLE vulnerabilities ( cvss jsonb DEFAULT '[]'::jsonb, auto_resolved boolean DEFAULT false NOT NULL, uuid uuid, - CONSTRAINT check_4d8a873f1f CHECK ((finding_id IS NOT NULL)) + solution text, + cve text, + CONSTRAINT check_4d8a873f1f CHECK ((finding_id IS NOT NULL)), + CONSTRAINT check_614bee5c16 CHECK ((char_length(cve) <= 48400)), + CONSTRAINT check_d7634b42b6 CHECK ((char_length(solution) <= 7000)) ); CREATE SEQUENCE vulnerabilities_id_seq -- GitLab From aa2d2ea22f1b82c49e0998d71dfe8abba9f0fa3c Mon Sep 17 00:00:00 2001 From: Gregory Havenga Date: Wed, 1 Oct 2025 15:22:58 +0200 Subject: [PATCH 2/8] Define up and down --- ...708_add_solution_severity_and_cve_to_vulnerabilities.rb | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) diff --git a/db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb b/db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb index f67d29214a9a2d..a69139e8b020b8 100644 --- a/db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb +++ b/db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb @@ -5,11 +5,16 @@ class AddSolutionSeverityAndCveToVulnerabilities < Gitlab::Database::Migration[2 disable_ddl_transaction! - def change + def up add_column :vulnerabilities, :solution, :text add_column :vulnerabilities, :cve, :text add_text_limit :vulnerabilities, :solution, 7000 add_text_limit :vulnerabilities, :cve, 48400 end + + def down + remove_column :vulnerabilities, :solution + remove_column :vulnerabilities, :cve + end end -- GitLab From dda54dd7bb41b5cb145aa5b7d926a5527459a93a Mon Sep 17 00:00:00 2001 From: Gregory Havenga Date: Wed, 1 Oct 2025 15:34:43 +0200 Subject: [PATCH 3/8] Align name to action --- ...> 20250924143708_add_solution_and_cve_to_vulnerabilities.rb} | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) rename db/migrate/{20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb => 20250924143708_add_solution_and_cve_to_vulnerabilities.rb} (82%) diff --git a/db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb b/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb similarity index 82% rename from db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb rename to db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb index a69139e8b020b8..80de5d2a797c2a 100644 --- a/db/migrate/20250924143708_add_solution_severity_and_cve_to_vulnerabilities.rb +++ b/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb @@ -1,6 +1,6 @@ # frozen_string_literal: true -class AddSolutionSeverityAndCveToVulnerabilities < Gitlab::Database::Migration[2.3] +class AddSolutionAndCveToVulnerabilities < Gitlab::Database::Migration[2.3] milestone '18.5' disable_ddl_transaction! -- GitLab From 288a5e003a6814b0597d00627e97e6fce4d753b3 Mon Sep 17 00:00:00 2001 From: Gregory Havenga Date: Wed, 15 Oct 2025 17:13:09 +0200 Subject: [PATCH 4/8] Don't validate the new constraints during migration --- ...24143708_add_solution_and_cve_to_vulnerabilities.rb | 4 ++-- db/structure.sql | 10 +++++++--- 2 files changed, 9 insertions(+), 5 deletions(-) diff --git a/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb b/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb index 80de5d2a797c2a..0580cbb2f4a1dc 100644 --- a/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb +++ b/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb @@ -9,8 +9,8 @@ def up add_column :vulnerabilities, :solution, :text add_column :vulnerabilities, :cve, :text - add_text_limit :vulnerabilities, :solution, 7000 - add_text_limit :vulnerabilities, :cve, 48400 + add_text_limit :vulnerabilities, :solution, 7000, validate: false + add_text_limit :vulnerabilities, :cve, 48400, validate: false end def down diff --git a/db/structure.sql b/db/structure.sql index 91db2b0d0f6691..a4ac6d571a3246 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -28044,9 +28044,7 @@ CREATE TABLE vulnerabilities ( uuid uuid, solution text, cve text, - CONSTRAINT check_4d8a873f1f CHECK ((finding_id IS NOT NULL)), - CONSTRAINT check_614bee5c16 CHECK ((char_length(cve) <= 48400)), - CONSTRAINT check_d7634b42b6 CHECK ((char_length(solution) <= 7000)) + CONSTRAINT check_4d8a873f1f CHECK ((finding_id IS NOT NULL)) ); CREATE SEQUENCE vulnerabilities_id_seq @@ -33230,6 +33228,9 @@ ALTER TABLE ONLY instance_type_ci_runners ALTER TABLE bulk_import_trackers ADD CONSTRAINT check_5f034e7cad CHECK ((num_nonnulls(namespace_id, organization_id, project_id) = 1)) NOT VALID; +ALTER TABLE vulnerabilities + ADD CONSTRAINT check_614bee5c16 CHECK ((char_length(cve) <= 48400)) NOT VALID; + ALTER TABLE ONLY project_type_ci_runners ADD CONSTRAINT check_619c71f3a2 UNIQUE (id); @@ -33263,6 +33264,9 @@ ALTER TABLE work_item_custom_statuses ALTER TABLE packages_packages ADD CONSTRAINT check_d6301aedeb CHECK ((char_length(status_message) <= 255)) NOT VALID; +ALTER TABLE vulnerabilities + ADD CONSTRAINT check_d7634b42b6 CHECK ((char_length(solution) <= 7000)) NOT VALID; + ALTER TABLE commit_user_mentions ADD CONSTRAINT check_ddd6f289f4 CHECK ((namespace_id IS NOT NULL)) NOT VALID; -- GitLab From 31b81250532887731ee953bc023e71aaed6c9786 Mon Sep 17 00:00:00 2001 From: Gregory Havenga <11164960-ghavenga@users.noreply.gitlab.com> Date: Tue, 21 Oct 2025 11:05:31 +0200 Subject: [PATCH 5/8] Update milestone --- .../20250924143708_add_solution_and_cve_to_vulnerabilities.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb b/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb index 0580cbb2f4a1dc..2d6eeff88b65c8 100644 --- a/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb +++ b/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb @@ -1,7 +1,7 @@ # frozen_string_literal: true class AddSolutionAndCveToVulnerabilities < Gitlab::Database::Migration[2.3] - milestone '18.5' + milestone '18.6' disable_ddl_transaction! -- GitLab From 75a30e6f49cd56a8587fe32b07925e1253f41fd0 Mon Sep 17 00:00:00 2001 From: Gregory Havenga Date: Tue, 21 Oct 2025 11:24:26 +0200 Subject: [PATCH 6/8] Update migration timestamp --- ...=> 20251020103708_add_solution_and_cve_to_vulnerabilities.rb} | 0 db/schema_migrations/20250924143708 | 1 - db/schema_migrations/20251020103708 | 1 + 3 files changed, 1 insertion(+), 1 deletion(-) rename db/migrate/{20250924143708_add_solution_and_cve_to_vulnerabilities.rb => 20251020103708_add_solution_and_cve_to_vulnerabilities.rb} (100%) delete mode 100644 db/schema_migrations/20250924143708 create mode 100644 db/schema_migrations/20251020103708 diff --git a/db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb b/db/migrate/20251020103708_add_solution_and_cve_to_vulnerabilities.rb similarity index 100% rename from db/migrate/20250924143708_add_solution_and_cve_to_vulnerabilities.rb rename to db/migrate/20251020103708_add_solution_and_cve_to_vulnerabilities.rb diff --git a/db/schema_migrations/20250924143708 b/db/schema_migrations/20250924143708 deleted file mode 100644 index 9cf29a1c3b9e5f..00000000000000 --- a/db/schema_migrations/20250924143708 +++ /dev/null @@ -1 +0,0 @@ -a7d71864c6c00c7715c774b062a14b2bdaebdabaed615af0c5a48bd1db6704a8 \ No newline at end of file diff --git a/db/schema_migrations/20251020103708 b/db/schema_migrations/20251020103708 new file mode 100644 index 00000000000000..84150067d6deba --- /dev/null +++ b/db/schema_migrations/20251020103708 @@ -0,0 +1 @@ +0a4f16950aaccc23304392cb1cae9ecef9d744121e486747d827a2129a4b73c2 \ No newline at end of file -- GitLab From 92f395140a1abe67ec32a46d5bca9606908f449d Mon Sep 17 00:00:00 2001 From: Gregory Havenga Date: Wed, 22 Oct 2025 11:20:37 +0200 Subject: [PATCH 7/8] Enable lock retries for high traffic tables --- ...08_add_solution_and_cve_to_vulnerabilities.rb | 16 ++++++++++++---- 1 file changed, 12 insertions(+), 4 deletions(-) diff --git a/db/migrate/20251020103708_add_solution_and_cve_to_vulnerabilities.rb b/db/migrate/20251020103708_add_solution_and_cve_to_vulnerabilities.rb index 2d6eeff88b65c8..236e9964da151c 100644 --- a/db/migrate/20251020103708_add_solution_and_cve_to_vulnerabilities.rb +++ b/db/migrate/20251020103708_add_solution_and_cve_to_vulnerabilities.rb @@ -6,15 +6,23 @@ class AddSolutionAndCveToVulnerabilities < Gitlab::Database::Migration[2.3] disable_ddl_transaction! def up - add_column :vulnerabilities, :solution, :text - add_column :vulnerabilities, :cve, :text + with_lock_retries do + add_column :vulnerabilities, :solution, :text, if_not_exists: true + end + with_lock_retries do + add_column :vulnerabilities, :cve, :text, if_not_exists: true + end add_text_limit :vulnerabilities, :solution, 7000, validate: false add_text_limit :vulnerabilities, :cve, 48400, validate: false end def down - remove_column :vulnerabilities, :solution - remove_column :vulnerabilities, :cve + with_lock_retries do + remove_column :vulnerabilities, :solution, if_exists: true + end + with_lock_retries do + remove_column :vulnerabilities, :cve, if_exists: true + end end end -- GitLab From efbec5237b311c03c6ca33c8745bbaed4e565578 Mon Sep 17 00:00:00 2001 From: Gregory Havenga Date: Thu, 23 Oct 2025 10:18:42 +0200 Subject: [PATCH 8/8] Remove CVE column as may not be needed --- ...=> 20251020103708_add_solution_to_vulnerabilities.rb} | 9 +-------- db/structure.sql | 4 ---- 2 files changed, 1 insertion(+), 12 deletions(-) rename db/migrate/{20251020103708_add_solution_and_cve_to_vulnerabilities.rb => 20251020103708_add_solution_to_vulnerabilities.rb} (53%) diff --git a/db/migrate/20251020103708_add_solution_and_cve_to_vulnerabilities.rb b/db/migrate/20251020103708_add_solution_to_vulnerabilities.rb similarity index 53% rename from db/migrate/20251020103708_add_solution_and_cve_to_vulnerabilities.rb rename to db/migrate/20251020103708_add_solution_to_vulnerabilities.rb index 236e9964da151c..6a897c2da33af3 100644 --- a/db/migrate/20251020103708_add_solution_and_cve_to_vulnerabilities.rb +++ b/db/migrate/20251020103708_add_solution_to_vulnerabilities.rb @@ -1,6 +1,6 @@ # frozen_string_literal: true -class AddSolutionAndCveToVulnerabilities < Gitlab::Database::Migration[2.3] +class AddSolutionToVulnerabilities < Gitlab::Database::Migration[2.3] milestone '18.6' disable_ddl_transaction! @@ -9,20 +9,13 @@ def up with_lock_retries do add_column :vulnerabilities, :solution, :text, if_not_exists: true end - with_lock_retries do - add_column :vulnerabilities, :cve, :text, if_not_exists: true - end add_text_limit :vulnerabilities, :solution, 7000, validate: false - add_text_limit :vulnerabilities, :cve, 48400, validate: false end def down with_lock_retries do remove_column :vulnerabilities, :solution, if_exists: true end - with_lock_retries do - remove_column :vulnerabilities, :cve, if_exists: true - end end end diff --git a/db/structure.sql b/db/structure.sql index a4ac6d571a3246..9b1d07742e0299 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -28043,7 +28043,6 @@ CREATE TABLE vulnerabilities ( auto_resolved boolean DEFAULT false NOT NULL, uuid uuid, solution text, - cve text, CONSTRAINT check_4d8a873f1f CHECK ((finding_id IS NOT NULL)) ); @@ -33228,9 +33227,6 @@ ALTER TABLE ONLY instance_type_ci_runners ALTER TABLE bulk_import_trackers ADD CONSTRAINT check_5f034e7cad CHECK ((num_nonnulls(namespace_id, organization_id, project_id) = 1)) NOT VALID; -ALTER TABLE vulnerabilities - ADD CONSTRAINT check_614bee5c16 CHECK ((char_length(cve) <= 48400)) NOT VALID; - ALTER TABLE ONLY project_type_ci_runners ADD CONSTRAINT check_619c71f3a2 UNIQUE (id); -- GitLab