From 37ea263e1ee03ac6402d4b2629d5602233323f9f Mon Sep 17 00:00:00 2001 From: mo khan Date: Mon, 1 Dec 2025 09:23:33 -0700 Subject: [PATCH 1/4] Validate constraints on `spam_logs.user_id` Changelog: changed --- ...ull_constraint_on_spam_logs_for_user_id.rb | 18 +++++++++++++++ ...key_constraint_on_spam_logs_for_user_id.rb | 23 +++++++++++++++++++ db/schema_migrations/20251201161815 | 1 + db/schema_migrations/20251201164159 | 1 + db/structure.sql | 8 +++---- 5 files changed, 46 insertions(+), 5 deletions(-) create mode 100644 db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb create mode 100644 db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb create mode 100644 db/schema_migrations/20251201161815 create mode 100644 db/schema_migrations/20251201164159 diff --git a/db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb b/db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb new file mode 100644 index 00000000000000..4a31603c945f8e --- /dev/null +++ b/db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true + +class ValidateNotNullConstraintOnSpamLogsForUserId < Gitlab::Database::Migration[2.3] + milestone '18.8' + + def up + validate_not_null_constraint :spam_logs, :user_id, constraint_name: "check_56d1d910ee" + end + + def down + with_lock_retries do + execute 'ALTER TABLE spam_logs DROP CONSTRAINT IF EXISTS check_56d1d910ee' + end + with_lock_retries do + execute 'ALTER TABLE spam_logs ADD CONSTRAINT check_56d1d910ee CHECK (user_id IS NOT NULL) NOT VALID' + end + end +end diff --git a/db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb b/db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb new file mode 100644 index 00000000000000..084181967d6da2 --- /dev/null +++ b/db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb @@ -0,0 +1,23 @@ +# frozen_string_literal: true + +class ValidateForeignKeyConstraintOnSpamLogsForUserId < Gitlab::Database::Migration[2.3] + milestone '18.8' + + def up + validate_foreign_key :spam_logs, :user_id, name: 'fk_1cb83308b1' + end + + def down + with_lock_retries do + execute 'ALTER TABLE spam_logs DROP CONSTRAINT IF EXISTS fk_1cb83308b1' + end + with_lock_retries do + execute <<~SQL + ALTER TABLE spam_logs + ADD CONSTRAINT fk_1cb83308b1 + FOREIGN KEY (user_id) REFERENCES users(id) + ON DELETE CASCADE NOT VALID + SQL + end + end +end diff --git a/db/schema_migrations/20251201161815 b/db/schema_migrations/20251201161815 new file mode 100644 index 00000000000000..ce1e6d8affce3c --- /dev/null +++ b/db/schema_migrations/20251201161815 @@ -0,0 +1 @@ +25fd1a32fd95847023aa6d2684d6cd1690eecd144d8be105a933c750ffa77aff \ No newline at end of file diff --git a/db/schema_migrations/20251201164159 b/db/schema_migrations/20251201164159 new file mode 100644 index 00000000000000..7a6577ab54eaad --- /dev/null +++ b/db/schema_migrations/20251201164159 @@ -0,0 +1 @@ +230f9f63439173c89bd80f17356d72e20e7dea7930757e321e1cfcd9f14e2927 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 6746648605376e..47a43bcd3f7490 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -27652,7 +27652,8 @@ CREATE TABLE spam_logs ( submitted_as_ham boolean DEFAULT false NOT NULL, recaptcha_verified boolean DEFAULT false NOT NULL, target_id bigint, - organization_id bigint + organization_id bigint, + CONSTRAINT check_56d1d910ee CHECK ((user_id IS NOT NULL)) ); CREATE SEQUENCE spam_logs_id_seq @@ -35075,9 +35076,6 @@ ALTER TABLE user_details ALTER TABLE diff_note_positions ADD CONSTRAINT check_4c86140f48 CHECK ((namespace_id IS NOT NULL)) NOT VALID; -ALTER TABLE spam_logs - ADD CONSTRAINT check_56d1d910ee CHECK ((user_id IS NOT NULL)) NOT VALID; - ALTER TABLE ONLY instance_type_ci_runners ADD CONSTRAINT check_5c34a3c1db UNIQUE (id); @@ -50155,7 +50153,7 @@ ALTER TABLE ONLY approval_policy_rule_project_links ADD CONSTRAINT fk_1c78796d52 FOREIGN KEY (approval_policy_rule_id) REFERENCES approval_policy_rules(id) ON DELETE CASCADE; ALTER TABLE ONLY spam_logs - ADD CONSTRAINT fk_1cb83308b1 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE NOT VALID; + ADD CONSTRAINT fk_1cb83308b1 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; ALTER TABLE ONLY issue_links ADD CONSTRAINT fk_1cce06b868 FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE; -- GitLab From 04720a472b51f675273928276d223850cb6a95f9 Mon Sep 17 00:00:00 2001 From: mo khan Date: Fri, 12 Dec 2025 16:03:06 -0700 Subject: [PATCH 2/4] Replace down script with no-op --- ...e_not_null_constraint_on_spam_logs_for_user_id.rb | 7 +------ ...oreign_key_constraint_on_spam_logs_for_user_id.rb | 12 +----------- 2 files changed, 2 insertions(+), 17 deletions(-) diff --git a/db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb b/db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb index 4a31603c945f8e..803360cfb13e98 100644 --- a/db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb +++ b/db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb @@ -8,11 +8,6 @@ def up end def down - with_lock_retries do - execute 'ALTER TABLE spam_logs DROP CONSTRAINT IF EXISTS check_56d1d910ee' - end - with_lock_retries do - execute 'ALTER TABLE spam_logs ADD CONSTRAINT check_56d1d910ee CHECK (user_id IS NOT NULL) NOT VALID' - end + # no-op end end diff --git a/db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb b/db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb index 084181967d6da2..88ab565796afa1 100644 --- a/db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb +++ b/db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb @@ -8,16 +8,6 @@ def up end def down - with_lock_retries do - execute 'ALTER TABLE spam_logs DROP CONSTRAINT IF EXISTS fk_1cb83308b1' - end - with_lock_retries do - execute <<~SQL - ALTER TABLE spam_logs - ADD CONSTRAINT fk_1cb83308b1 - FOREIGN KEY (user_id) REFERENCES users(id) - ON DELETE CASCADE NOT VALID - SQL - end + # no-op end end -- GitLab From 1cd5cf0e55e33bf1452361fca18c9d49e389b36f Mon Sep 17 00:00:00 2001 From: mo khan Date: Fri, 12 Dec 2025 16:04:10 -0700 Subject: [PATCH 3/4] Remove exception for spam_logs.user_id --- ee/spec/lib/gitlab/database/desired_sharding_key_spec.rb | 1 - 1 file changed, 1 deletion(-) diff --git a/ee/spec/lib/gitlab/database/desired_sharding_key_spec.rb b/ee/spec/lib/gitlab/database/desired_sharding_key_spec.rb index d115649ae05309..fff75fa656e7f1 100644 --- a/ee/spec/lib/gitlab/database/desired_sharding_key_spec.rb +++ b/ee/spec/lib/gitlab/database/desired_sharding_key_spec.rb @@ -23,7 +23,6 @@ 'design_management_versions.issue_id', # https://gitlab.com/gitlab-org/gitlab/-/issues/461330 'requirements_management_test_reports.issue_id', # https://gitlab.com/gitlab-org/gitlab/-/issues/383031 # We nullify the FK once the parent is deleted and remove entries using a cleanup worker. - 'spam_logs.user_id', 'packages_nuget_symbols.package_id' ] end -- GitLab From cb385e202af16a53c7bd4c76ac6e38a109ac2df6 Mon Sep 17 00:00:00 2001 From: mo khan Date: Mon, 15 Dec 2025 11:08:41 -0700 Subject: [PATCH 4/4] Refresh timestamps --- ...743_validate_not_null_constraint_on_spam_logs_for_user_id.rb} | 0 ..._validate_foreign_key_constraint_on_spam_logs_for_user_id.rb} | 0 db/schema_migrations/20251201161815 | 1 - db/schema_migrations/20251201164159 | 1 - db/schema_migrations/20251215180743 | 1 + db/schema_migrations/20251215180745 | 1 + 6 files changed, 2 insertions(+), 2 deletions(-) rename db/post_migrate/{20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb => 20251215180743_validate_not_null_constraint_on_spam_logs_for_user_id.rb} (100%) rename db/post_migrate/{20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb => 20251215180745_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb} (100%) delete mode 100644 db/schema_migrations/20251201161815 delete mode 100644 db/schema_migrations/20251201164159 create mode 100644 db/schema_migrations/20251215180743 create mode 100644 db/schema_migrations/20251215180745 diff --git a/db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb b/db/post_migrate/20251215180743_validate_not_null_constraint_on_spam_logs_for_user_id.rb similarity index 100% rename from db/post_migrate/20251201161815_validate_not_null_constraint_on_spam_logs_for_user_id.rb rename to db/post_migrate/20251215180743_validate_not_null_constraint_on_spam_logs_for_user_id.rb diff --git a/db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb b/db/post_migrate/20251215180745_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb similarity index 100% rename from db/post_migrate/20251201164159_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb rename to db/post_migrate/20251215180745_validate_foreign_key_constraint_on_spam_logs_for_user_id.rb diff --git a/db/schema_migrations/20251201161815 b/db/schema_migrations/20251201161815 deleted file mode 100644 index ce1e6d8affce3c..00000000000000 --- a/db/schema_migrations/20251201161815 +++ /dev/null @@ -1 +0,0 @@ -25fd1a32fd95847023aa6d2684d6cd1690eecd144d8be105a933c750ffa77aff \ No newline at end of file diff --git a/db/schema_migrations/20251201164159 b/db/schema_migrations/20251201164159 deleted file mode 100644 index 7a6577ab54eaad..00000000000000 --- a/db/schema_migrations/20251201164159 +++ /dev/null @@ -1 +0,0 @@ -230f9f63439173c89bd80f17356d72e20e7dea7930757e321e1cfcd9f14e2927 \ No newline at end of file diff --git a/db/schema_migrations/20251215180743 b/db/schema_migrations/20251215180743 new file mode 100644 index 00000000000000..7b05ea279c09ce --- /dev/null +++ b/db/schema_migrations/20251215180743 @@ -0,0 +1 @@ +2e56a8a912918b634b92153d1ab852a4b137c61088db2a790ab25bd76a916d02 \ No newline at end of file diff --git a/db/schema_migrations/20251215180745 b/db/schema_migrations/20251215180745 new file mode 100644 index 00000000000000..0b9e9cb83aa648 --- /dev/null +++ b/db/schema_migrations/20251215180745 @@ -0,0 +1 @@ +25b9f50f211266f92b03e21d0d8f83bcb9cfe6730e24cd7c466995060b83c769 \ No newline at end of file -- GitLab