resource_iteration_events table blocks user deletion owing to SET NULL / NOT NULL contradiction
Summary
Customer raised a ticket as they get errors deleting a user. GitLab team members can read more in the tickets (1, 2).
In the PostgreSQL log:
2021-11-30_03:08:21.68660 ERROR: null value in column "user_id" violates not-null constraint\
2021-11-30_03:08:21.68669 DETAIL: Failing row contains (123, null, 456, null, 789, 2021-02-29 23:59:00.123456+00, 1).\
2021-11-30_03:08:21.68672 CONTEXT: SQL statement "UPDATE ONLY "public"."resource_iteration_events" SET "user_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "user_id""\
In Sidekiq jobs view:
Looking at the definition of the table in 13.12.15 (the relevant elements are still the same in %14.6 :
CREATE TABLE resource_iteration_events (
id bigint NOT NULL,
user_id bigint NOT NULL,
issue_id bigint,
merge_request_id bigint,
iteration_id bigint,
created_at timestamp with time zone NOT NULL,
action smallint NOT NULL
);
ALTER TABLE ONLY resource_iteration_events
ADD CONSTRAINT fk_rails_501fa15d69 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;
The user deletion in the users
table will attempt to SET NULL
the user_id
field via the FK constraint, but the field is set not nullable.
Workaround
-
gitlab-rails console
-
Reassign these records to the
ghost
user. In this example, the username (@handle
) isfred.delete
ghostu = User.find_by(username: 'ghost') deleteu = User.find_by(username: 'fred.delete') ResourceIterationEvent.where(user_id: deleteu.id).update(user_id: ghostu.id)
-
Delete the user account. With the records assigned to the Ghost user, this will now be successful.
Steps to reproduce
- Create a user.
- Generate iteration activity using that user, such as assigning an issue to an iteration.
- Delete the user.
Example Project
What is the current bug behavior?
User deletion fails if they've generated records in the resource_iteration_events
table.
What is the expected correct behavior?
User deletion is successful.
Relevant logs and/or screenshots
gitlabhq_production=# select * from resource_iteration_events;
id | user_id | issue_id | merge_request_id | iteration_id | created_at | action
----+---------+----------+------------------+--------------+-------------------------------+--------
1 | 1 | 147206 | | 1 | 2021-12-30 09:51:35.598221+00 | 1
2 | 4 | 147207 | | 2 | 2021-12-30 10:18:25.903289+00 | 1
based on rails console cheat sheet
irb(main):008:0> user = User.find_by_id(4)
=> #<User id:4 @zd259193>
irb(main):009:0> users = User.where('id NOT IN (select distinct(user_id) from project_authorizations)')
=> #<ActiveRecord::Relation [#<User id:2 @alert-bot>, #<User id:3 @support-bot>, #<User id:4 @zd259193>]>
irb(main):010:0> users = User.where('id in (4)')
=> #<ActiveRecord::Relation [#<User id:4 @zd259193>]>
irb(main):011:0> current_user = User.find_by(id: 1)
=> #<User id:1 @root>
irb(main):012:1* users.each do |user|
irb(main):013:1* DeleteUserWorker.new.perform(current_user.id, user.id)
irb(main):014:0> end
Traceback (most recent call last):
16: from app/services/users/destroy_service.rb:69:in `execute'
15: from lib/gitlab/database/query_analyzers/prevent_cross_database_modification.rb:16:in `allow_cross_database_modification_within_transaction'
14: from lib/gitlab/database/query_analyzers/base.rb:18:in `with_suppressed'
13: from app/services/users/destroy_service.rb:70:in `block in execute'
12: from lib/gitlab/database/load_balancing/connection_proxy.rb:77:in `transaction'
11: from lib/gitlab/database/load_balancing/connection_proxy.rb:125:in `write_using_load_balancer'
10: from lib/gitlab/database/load_balancing/load_balancer.rb:110:in `read_write'
9: from lib/gitlab/database/load_balancing/load_balancer.rb:172:in `retry_with_backoff'
8: from lib/gitlab/database/load_balancing/load_balancer.rb:112:in `block in read_write'
7: from lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `block in write_using_load_balancer'
6: from lib/gitlab/database/load_balancing/connection_proxy.rb:67:in `block (2 levels) in <class:ConnectionProxy>'
5: from lib/gitlab/database/load_balancing/connection_proxy.rb:125:in `write_using_load_balancer'
4: from lib/gitlab/database/load_balancing/load_balancer.rb:110:in `read_write'
3: from lib/gitlab/database/load_balancing/load_balancer.rb:172:in `retry_with_backoff'
2: from lib/gitlab/database/load_balancing/load_balancer.rb:112:in `block in read_write'
1: from lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `block in write_using_load_balancer'
ActiveRecord::NotNullViolation (PG::NotNullViolation: ERROR: null value in column "user_id" violates not-null constraint)
DETAIL: Failing row contains (2, null, 147207, null, 2, 2021-12-30 10:18:25.903289+00, 1).
CONTEXT: SQL statement "UPDATE ONLY "public"."resource_iteration_events" SET "user_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "user_id""
Output of checks
Results of GitLab environment info
Expand for output related to GitLab environment info
(For installations with omnibus-gitlab package run and paste the output of: `sudo gitlab-rake gitlab:env:info`) (For installations from source run and paste the output of: `sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)
Results of GitLab application Check
Expand for output related to the GitLab application check
(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true
)(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true
)(we will only investigate if the tests are passing)