<!--- Please read this! Before opening a new issue, make sure to search for keywords in the issues filtered by the "regression" or "type::bug" label: - https://gitlab.com/gitlab-org/gitlab/issues?label_name%5B%5D=regression - https://gitlab.com/gitlab-org/gitlab/issues?label_name%5B%5D=type::bug and verify the issue you're about to submit isn't a duplicate. ---> ### Summary <!-- Summarize the bug encountered concisely. --> Customer raised a ticket as they get errors deleting a user. GitLab team members can read more in the tickets ([1](https://gitlab.zendesk.com/agent/tickets/259193), [2](https://gitlab.zendesk.com/agent/tickets/252424)). 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](https://gitlab.com/gitlab-org/gitlab/-/blob/v13.12.15-ee/db/structure.sql) (the relevant elements are still the same in %14.6 : ```sql 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 ); ``` ```sql 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 1. [Run the Rails console](https://docs.gitlab.com/ee/administration/operations/rails_console.html) ```shell gitlab-rails console ``` 1. Reassign these records to the `ghost` user. In this example, the username (`@handle`) is `fred.delete` ```ruby ghostu = User.find_by(username: 'ghost') deleteu = User.find_by(username: 'fred.delete') ResourceIterationEvent.where(user_id: deleteu.id).update(user_id: ghostu.id) ``` 1. Delete the user account. With the records assigned to the Ghost user, this will now be successful. ### Steps to reproduce 1. Create a user. 2. Generate iteration activity using that user, such as assigning an issue to an iteration. 3. Delete the user. ### Example Project <!-- If possible, please create an example project here on GitLab.com that exhibits the problematic behavior, and link to it here in the bug report. If you are using an older version of GitLab, this will also determine whether the bug is fixed in a more recent version. --> ### 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 ```sql 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](https://docs.gitlab.com/ee/administration/troubleshooting/gitlab_rails_cheat_sheet.html#block-or-delete-users-that-have-no-projects-or-groups) ```ruby 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 <!-- If you are reporting a bug on GitLab.com, write: This bug happens on GitLab.com --> #### Results of GitLab environment info <!-- Input any relevant GitLab environment information if needed. --> <details> <summary>Expand for output related to GitLab environment info</summary> <pre> (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`) </pre> </details> #### Results of GitLab application Check <!-- Input any relevant GitLab application check information if needed. --> <details> <summary>Expand for output related to the GitLab application check</summary> <pre> (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) </pre> </details> ### Possible fixes <!-- If you can, link to the line of code that might be responsible for the problem. -->