ScheduleBackfillPushRulesIdInProjects fails if there are multiple rows in application_settings
On my local instance that installed the latest nightly build, I just tried running gitlab-rake db:migrate, and it failed due to the migration introduced in !28286 (merged):
# sudo gitlab-rake db:migrate
== 20200204113225 ScheduleRecalculateProjectAuthorizationsThirdRun: migrating =
-- Scheduling RecalculateProjectAuthorizationsWithMinMaxUserId jobs
== 20200204113225 ScheduleRecalculateProjectAuthorizationsThirdRun: migrated (0.2954s)
== 20200325104755 AddPushRulesIdToProjectSettings: migrating ==================
-- add_column(:project_settings, :push_rule_id, :bigint)
-> 0.0355s
== 20200325104755 AddPushRulesIdToProjectSettings: migrated (0.0864s) =========
== 20200325104756 AddPushRulesForeignKeyToProjectSettings: migrating ==========
-- transaction_open?()
-> 0.0000s
-- index_exists?(:project_settings, :push_rule_id, {:unique=>true, :algorithm=>:concurrently})
-> 0.0069s
-- execute("SET statement_timeout TO 0")
-> 0.0007s
-- add_index(:project_settings, :push_rule_id, {:unique=>true, :algorithm=>:concurrently})
-> 0.1125s
-- execute("RESET ALL")
-> 0.0010s
-- transaction_open?()
-> 0.0000s
-- foreign_keys(:project_settings)
-> 0.0456s
-- execute("ALTER TABLE project_settings\nADD CONSTRAINT fk_413a953e20\nFOREIGN KEY (push_rule_id)\nREFERENCES push_rules (id)\nON DELETE CASCADE\nNOT VALID;\n")
-> 0.1017s
-- execute("SET statement_timeout TO 0")
-> 0.0007s
-- execute("ALTER TABLE project_settings VALIDATE CONSTRAINT fk_413a953e20;")
-> 0.0703s
-- execute("RESET ALL")
-> 0.0009s
== 20200325104756 AddPushRulesForeignKeyToProjectSettings: migrated (0.3489s) =
== 20200325104833 AddPushRulesIdToApplicationSettings: migrating ==============
-- add_column(:application_settings, :push_rule_id, :bigint)
-> 0.0017s
== 20200325104833 AddPushRulesIdToApplicationSettings: migrated (0.0042s) =====
== 20200325104834 AddPushRulesForeignKeyToApplicationSettings: migrating ======
-- transaction_open?()
-> 0.0000s
-- index_exists?(:application_settings, :push_rule_id, {:unique=>true, :algorithm=>:concurrently})
-> 0.0065s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- add_index(:application_settings, :push_rule_id, {:unique=>true, :algorithm=>:concurrently})
-> 0.0130s
-- execute("RESET ALL")
-> 0.0005s
-- transaction_open?()
-> 0.0000s
-- foreign_keys(:application_settings)
-> 0.0060s
-- execute("ALTER TABLE application_settings\nADD CONSTRAINT fk_693b8795e4\nFOREIGN KEY (push_rule_id)\nREFERENCES push_rules (id)\nON DELETE SET NULL\nNOT VALID;\n")
-> 0.0107s
-- execute("SET statement_timeout TO 0")
-> 0.0007s
-- execute("ALTER TABLE application_settings VALIDATE CONSTRAINT fk_693b8795e4;")
-> 0.0035s
-- execute("RESET ALL")
-> 0.0006s
== 20200325104834 AddPushRulesForeignKeyToApplicationSettings: migrated (0.0474s)
== 20200325111432 AddIssuesCreateLimitToApplicationSettings: migrating ========
-- add_column(:application_settings, :issues_create_limit, :integer, {:default=>300, :null=>false})
-> 0.0091s
== 20200325111432 AddIssuesCreateLimitToApplicationSettings: migrated (0.0093s)
== 20200325162730 ScheduleBackfillPushRulesIdInProjects: migrating ============
-- execute("UPDATE application_settings SET push_rule_id = 9")
rake aborted!
StandardError: An error has occurred, all later migrations canceled:
PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_application_settings_on_push_rule_id"
DETAIL: Key (push_rule_id)=(9) already exists.
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20200325162730_schedule_backfill_push_rules_id_in_projects.rb:21:in `up'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Caused by:
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_application_settings_on_push_rule_id"
DETAIL: Key (push_rule_id)=(9) already exists.
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20200325162730_schedule_backfill_push_rules_id_in_projects.rb:21:in `up'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Caused by:
PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_application_settings_on_push_rule_id"
DETAIL: Key (push_rule_id)=(9) already exists.
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20200325162730_schedule_backfill_push_rules_id_in_projects.rb:21:in `up'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)This looks like it happens if the instance happens to have duplicate application_settings. This happened long ago when we had a bug where multiple application_settings were inserted.
gitlabhq_production=> select id, push_rule_id, created_at, updated_at from application_settings order by id;
id | push_rule_id | created_at | updated_at
----+--------------+----------------------------+----------------------------
1 | | 2017-02-06 20:49:09.216214 | 2019-12-09 21:37:29.287837
2 | | 2017-02-06 20:49:09.254403 | 2019-12-09 21:37:29.291537
3 | | 2017-02-06 20:49:09.372803 | 2019-12-09 21:37:29.297227
4 | | 2017-02-06 20:49:09.4092 | 2019-12-09 21:37:29.294251
5 | | 2017-02-06 20:49:09.519043 | 2020-03-26 17:25:50.787984
(5 rows)Before, it wasn't a big deal since we always took the latest id, but now that we are actually depending on this table to have one row, the unique index is failing.
dev.gitlab.org also has this problem:
gitlabhq_production=> select id, created_at, updated_at from application_settings order by id;
id | created_at | updated_at
----+----------------------------+----------------------------
1 | 2015-01-09 03:00:45.55412 | 2019-12-04 03:22:32.425003
2 | 2017-07-07 09:42:24.30994 | 2019-12-04 03:22:32.401102
3 | 2017-07-07 09:42:24.598087 | 2019-12-04 03:22:32.40349
4 | 2017-07-07 09:42:24.800332 | 2019-12-04 03:22:32.405854
5 | 2017-07-07 09:42:25.2129 | 2019-12-04 03:22:32.389287
6 | 2017-07-07 09:42:25.223187 | 2019-12-04 03:22:32.413069
7 | 2017-07-07 09:44:22.13386 | 2019-12-04 03:22:32.415555
8 | 2017-07-07 09:44:22.150257 | 2019-12-04 03:22:32.3674
9 | 2017-07-07 09:44:22.280643 | 2019-12-04 03:22:32.417921
10 | 2017-07-07 09:44:22.399299 | 2019-12-04 03:22:32.420296
11 | 2017-07-07 09:44:23.028125 | 2019-12-04 03:22:32.391625
12 | 2017-07-07 09:44:23.098763 | 2019-12-04 03:22:32.408206
13 | 2017-07-08 09:48:36.270305 | 2019-12-04 03:22:32.369904
14 | 2017-07-08 09:48:36.370462 | 2019-12-04 03:22:32.410581
15 | 2017-07-08 09:48:36.88248 | 2019-12-04 03:22:32.379547
16 | 2017-07-08 09:48:36.970432 | 2019-12-04 03:22:32.37225
17 | 2017-07-08 12:24:20.00659 | 2019-12-04 03:22:32.364074
18 | 2017-07-08 12:24:20.226801 | 2019-12-04 03:22:32.393988
19 | 2017-07-08 12:24:20.227876 | 2019-12-04 03:22:32.374616
20 | 2017-07-08 12:24:20.542347 | 2019-12-04 03:22:32.382088
21 | 2017-07-08 12:24:20.859801 | 2019-12-04 03:22:32.377013
22 | 2017-07-08 13:03:35.820201 | 2019-12-04 03:22:32.384526
23 | 2017-07-08 13:03:35.948099 | 2019-12-04 03:22:32.396392
24 | 2017-07-08 13:03:36.053212 | 2019-12-04 03:22:32.42266
25 | 2017-07-08 13:03:36.13958 | 2019-12-04 03:22:32.386914
26 | 2017-07-08 13:03:36.187089 | 2019-12-04 03:22:32.398749
27 | 2017-07-08 13:03:36.460217 | 2019-12-04 03:22:32.427465
(27 rows)I think to fix this problem we might want to drop all other rows except the max(id).
/cc: @mksionek, @jprovaznik
Edited by Stan Hu