Resolve "Sidekiq/Users::MigrateRecordsToGhostUserInBatchesWorker errors"
What does this MR do and why?
User_id column on ml_candidates is nullable, meaning it can already be created without a user. However, if it is created with a user, that user can't be delete because the fk is not handling on_delete. Fk has been updated to be on_delete: nullify
Fixes https://sentry.gitlab.net/gitlab/gitlabcom/issues/4155412/
How to set up and validate locally
All steps to be carried on rails console:
-
Setup:
# Create a user User.new(username: 'test_user', email: 'test2@example.com', name: 'Test User', password: 'pagfsadfeqwer', password_confirmation: 'pagfsadfeqwer') u.skip_confirmation! # Use it only if you wish user to be automatically confirmed. If skipped, user receives confirmation e-mail u.save! # Create a candidate exp = Ml::Experiment.create!(name: 'Gitlab Experiment', user_id: user_id, project_id: 1) c = exp.candidates.create!(user: u, start_time: 0, internal_id: 1, project_id: 1)
-
Without the migrations, this will break:
u.delete DELETE FROM "users" WHERE "users"."id" = 49 /*application:console,db_config_name:main,console_hostname:Eduardos-MacBook-Pro- 2.local,console_username:eduardobonet,line:(pry):14:in `__pry__'*/ ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR: update or delete on table "users" violates foreign key constraint "fk_rails_1b37441fe5" on table "ml_candidates"
-
Run the migrations
-
Deleting the user should now work:
u.delete User Destroy (117.6ms) DELETE FROM "users" WHERE "users"."id" = 49 /*application:console,db_config_name:main,console_hostname:Eduardos-MacBook-Pro-2.local,console_username:eduardobonet,line:(pry):4:in `__pry__'*/
Migrations
- Up
main: == [advisory_lock_connection] object_id: 223540, pg_backend_pid: 62307
main: == 20230629112833 CreateFkMlCandidatesOnUserId: migrating =====================
main: -- transaction_open?()
main: -> 0.0000s
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER TABLE ml_candidates ADD CONSTRAINT fk_ml_candidates_on_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL NOT VALID;")
main: -> 0.0018s
main: == 20230629112833 CreateFkMlCandidatesOnUserId: migrated (0.1233s) ============
main: == [advisory_lock_connection] object_id: 223540, pg_backend_pid: 62307
main: == [advisory_lock_connection] object_id: 223860, pg_backend_pid: 62310
main: == 20230629113029 ValidateFkMlCandidatesOnUserId: migrating ===================
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0006s
main: -- execute("ALTER TABLE ml_candidates VALIDATE CONSTRAINT fk_ml_candidates_on_user_id;")
main: -> 0.0026s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20230629113029 ValidateFkMlCandidatesOnUserId: migrated (0.0132s) ==========
main: == [advisory_lock_connection] object_id: 223860, pg_backend_pid: 62310
main: == [advisory_lock_connection] object_id: 224020, pg_backend_pid: 62317
main: == 20230629113133 RemoveOldFkMlCandidatesOnUserId: migrating ==================
main: -- remove_foreign_key(:ml_candidates, {:column=>:user_id, :name=>"fk_rails_1b37441fe5"})
main: -> 0.0031s
main: == 20230629113133 RemoveOldFkMlCandidatesOnUserId: migrated (0.0121s) =========
main: == [advisory_lock_connection] object_id: 224020, pg_backend_pid: 62317
- Down
main: == [advisory_lock_connection] object_id: 223240, pg_backend_pid: 61802
main: == 20230629113133 RemoveOldFkMlCandidatesOnUserId: reverting ==================
main: -- transaction_open?()
main: -> 0.0000s
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER TABLE ml_candidates ADD CONSTRAINT fk_rails_1b37441fe5 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE NOT VALID;")
main: -> 0.0013s
main: == 20230629113133 RemoveOldFkMlCandidatesOnUserId: reverted (0.1183s) =========
main: == 20230629113029 ValidateFkMlCandidatesOnUserId: reverting ===================
main: == 20230629113029 ValidateFkMlCandidatesOnUserId: reverted (0.0027s) ==========
main: == 20230629112833 CreateFkMlCandidatesOnUserId: reverting =====================
main: -- transaction_open?()
main: -> 0.0000s
main: -- remove_foreign_key(:ml_candidates, {:column=>:user_id, :on_delete=>:nullify, :name=>"fk_ml_candidates_on_user_id"})
main: -> 0.0026s
main: == 20230629112833 CreateFkMlCandidatesOnUserId: reverted (0.0109s) ============
main: == [advisory_lock_connection] object_id: 223240, pg_backend_pid: 61802
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #416588
Edited by Eduardo Bonet