Delete a user with rails - fails

Infos:

Expected behavior:

  • i want to delete all inactive user with this:
# Actual deletion, requires overview run before
User.where(active: false).where.not(id: 1).find_each do |user|
 puts "Customer #{user.login}/#{user.email}/#{user.id} has #{Ticket.where(customer_id: user.id).count} tickets"

 puts "  Removing references for user with E-Mail #{user.email}..."
 ActivityStream.where(created_by_id: user.id).update_all(created_by_id: 1)
 History.where(created_by_id: user.id).update_all(created_by_id: 1)
 Ticket::Article.where(created_by_id: user.id).update_all(created_by_id: 1)
 Ticket::Article.where(updated_by_id: user.id).update_all(updated_by_id: 1)
 Store.where(created_by_id: user.id).update_all(created_by_id: 1)
 StatsStore.where(created_by_id: user.id).update_all(created_by_id: 1)
 Tag.where(created_by_id: user.id).update_all(created_by_id: 1)
 if OnlineNotification.find_by(user_id: user.id)==""
  OnlineNotification.find_by(user_id: user.id).destroy!
 end

 puts "  Deleting user #{user.login}/#{user.email}..."
 user.destroy
end

worked fine for all inactive users except for one.

Actual behavior:

irb(main):021:0> User.where(active: false).where.not(id: 1).find_each do |user|
irb(main):022:1*  puts "Customer #{user.login}/#{user.email}/#{user.id} has #{Ticket.where(customer_id: user.id).count} tickets"
irb(main):023:1>
irb(main):024:1>  puts "  Removing references for user with E-Mail #{user.email}..."
irb(main):025:1>  ActivityStream.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):026:1>  History.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):027:1>  Ticket::Article.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):028:1>  Ticket::Article.where(updated_by_id: user.id).update_all(updated_by_id: 1)
irb(main):029:1>  Store.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):030:1>  StatsStore.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):031:1>  Tag.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):032:1>  if OnlineNotification.find_by(user_id: user.id)==""
irb(main):033:2>   OnlineNotification.find_by(user_id: user.id).destroy!
irb(main):034:2>  end
irb(main):035:1>
irb(main):036:1>  puts "  Deleting user #{user.login}/#{user.email}..."
irb(main):037:1>  user.destroy
irb(main):038:1> end
Customer render.rodriguez@de/render.rodriguez@de/13 has 0 tickets
  Removing references for user with E-Mail render.rodriguez@de...
  Deleting user render.rodriguez@.de/render.rodriguez@de...
Traceback (most recent call last):
        2: from (irb):21
        1: from (irb):37:in `block in irb_binding'
ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR:  update or delete on table "users" violates foreign key constraint "fk_rails_f694a04b71" on table "tickets")
DETAIL:  Key (id)=(13) is still referenced from table "tickets".
: DELETE FROM "users" WHERE "users"."id" = $1

The user has no tickets but the script claims that there are references to the ticket-table.

How to resolve this?

Thank you in advance.

Please provide the output of:

User.find(13)
User.find(13).roles

Those references can have various reasons.

Small edit: Not the script claims references, but the database being protective about its references. :slight_smile:

Ok :grinning:

irb(main):004:0> User.find(13)
=> #<User id: 13, organization_id: nil, login: "render.rodriguez@de", firstname: "render", lastname: "Rodriguez", email: "render.rodriguez@de", image: nil, image_source: nil, web: "", password: nil, phone: "", fax: "", mobile: "", department: "", street: "", zip: "", city: "", country: "", address: "", vip: false, verified: false, active: false, note: "", last_login: nil, source: nil, login_failed: 0, out_of_office: false, out_of_office_start_at: nil, out_of_office_end_at: nil, out_of_office_replacement_id: nil, preferences: {"tickets_closed"=>0, "tickets_open"=>1, "notification_config"=>{"matrix"=>{"create"=>{"criteria"=>{"owned_by_me"=>true, "owned_by_nobody"=>true, "no"=>false}, "channel"=>{"email"=>true, "online"=>true}}, "update"=>{"criteria"=>{"owned_by_me"=>true, "owned_by_nobody"=>true, "no"=>false}, "channel"=>{"email"=>true, "online"=>true}}, "reminder_reached"=>{"criteria"=>{"owned_by_me"=>true, "owned_by_nobody"=>false, "no"=>false}, "channel"=>{"email"=>true, "online"=>true}}, "escalation"=>{"criteria"=>{"owned_by_me"=>true, "owned_by_nobody"=>false, "no"=>false}, "channel"=>{"email"=>true, "online"=>true}}}}}, updated_by_id: 4, created_by_id: 13, created_at: "2019-07-17 09:07:56", updated_at: "2019-08-23 11:02:27", company: "", standort: "", kostenstelle: "">
irb(main):005:0> User.find(13).roles
=> #<ActiveRecord::Associations::CollectionProxy [#<Role id: 3, name: "Customer", preferences: {"not"=>["Agent", "Admin"]}, default_at_signup: true, active: true, note: "People who create Tickets ask for help.", updated_by_id: 4, created_by_id: 1, created_at: "2019-07-09 10:44:32", updated_at: "2019-07-09 10:44:32">]>
irb(main):006:0>

Has this user been an agent earlier?

Maybe the following helps you to find the references:

Ticket.where(customer_id:13).pluck(:id)
Ticket.where(owner_id:13).pluck(:id)
Ticket.where(created_by_id:13).pluck(:id)
Ticket.where(updated_by_id:13).pluck(:id)

If there is any reference within tickets (without articles), the above commands will return ticket ids that are affected. You then have to either remove them or assign them to someone else.

It was a testuser, therefore he could be an agent/admin/customer.

I am getting this error.

irb(main):001:0> Ticket.find_by(customer_id:13).pluck(:id)
Traceback (most recent call last):
        1: from (irb):1
NoMethodError (undefined method `pluck' for nil:NilClass)
irb(main):002:0> Ticket.find_by(owner_id:13).pluck(:id)
Traceback (most recent call last):
        1: from (irb):2
NoMethodError (undefined method `pluck' for nil:NilClass)
irb(main):003:0> Ticket.find_by(created_by_id:13).pluck(:id)
Traceback (most recent call last):
        1: from (irb):3
NoMethodError (undefined method `pluck' for #<Ticket:0x000055ecd1cda3e8>)
irb(main):004:0> Ticket.find_by(updated_by_id:13).pluck(:id)
Traceback (most recent call last):
        1: from (irb):4
NoMethodError (undefined method `pluck' for #<Ticket:0x000055ecd1c1a110>)
irb(main):005:0>

Did i something wrong?

Thank you for your help :sunglasses:

Doh!
Looks like I’ve been sleeping that day already :smiley: (or still… haha)

It was my bad, you just can’t use .pluck(...) when you’re searching for single entries, it’s for arrays <.<

I updated the commands above (from find_by to where) - that should work better now.
I promise, this time I verified that first :smiley:

Ahhhh, no problem :slightly_smiling_face:

Now i know which ticket i have to delete. Deleting because it is a test-ticket…
How to do?
Ticket Number 131 and 132…

Thank you in advance :+1::+1::+1:

Geht auch deutsch?

Nur falls ich die Worte nicht finde :wink:

Ticket.find(131).destroy
Will remove Ticket with ID 131.

Ensure that you’re fine with loosing the ticket.

Sorry, but I’ll only respond in english.
Reason for that is that we’d like all users to have the same possibilities to understand and solve problems (if there’s already a solution on the community). If we’d communicate in German, we’d block that process from other users that can’t understand German.

Now that’s a bit plumb, but if you would like German based support, you can get it via a service contract. :-X

Ok, no prolbem, english. Sounds resonable :sunglasses:

There is another message now:

ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR: update or delete on table "users" violates foreign key constraint "fk_rails_c2dbb9e7aa" on table "ticket_articles")

Can you once more help me to resolve this?

Thank you in advance :man_dancing:

Uh well welcome to the hell of foreign keys :smiley:

Below might help you identifying the issue:

Ticket::Article.where(created_by_id:13).pluck(:id)
Ticket::Articlewhere(updated_by_id:13).pluck(:id)

The following (slightly destructive) command helps you to remove single articles:

Ticket::Article.find({Article-ID}).destroy

Replace {Article-ID} with the returned ID.

I should have created a testmachine for testing, use my experience and create a new one.
But now, i really hated it for my whole admin-life, the testmachine is now the productivemachine :wink:

With your code i do not get an article-id :thinking:

Here is the output:

irb(main):119:0> User.where(active: false).where.not(id: 1).find_each do |user|
irb(main):120:1* puts "Customer #{user.login}/#{user.email}/#{user.id} has #{Ticket.where(customer_id: user.id).count} tickets"
irb(main):121:1>
irb(main):122:1> puts " Removing references for user with E-Mail #{user.email}..."
irb(main):123:1> ActivityStream.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):124:1> History.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):125:1> Ticket::Article.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):126:1> Ticket::Article.where(updated_by_id: user.id).update_all(updated_by_id: 1)
irb(main):127:1> Store.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):128:1> StatsStore.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):129:1> Tag.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):130:1> if OnlineNotification.find_by(user_id: user.id)==""
irb(main):131:2> OnlineNotification.find_by(user_id: user.id).destroy!
irb(main):132:2> end
irb(main):133:1>
irb(main):134:1> puts " Deleting user #{user.login}/#{user.email}..."
irb(main):135:1> user.destroy
irb(main):136:1> end
Customer render.rodriguez@amadamachinetools.de/render.rodriguez@amadamachinetools.de/13 has 0 tickets
 Removing references for user with E-Mail render.rodriguez@amadamachinetools.de...
 Deleting user render.rodriguez@amadamachinetools.de/render.rodriguez@amadamachinetools.de...
Traceback (most recent call last):
        2: from (irb):119
        1: from (irb):135:in `block in irb_binding'
ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR:  update or delete on table "users" violates foreign key constraint "fk_rails_c2dbb9e7aa" on table "ticket_articles")
DETAIL:  Key (id)=(13) is still referenced from table "ticket_articles".
: DELETE FROM "users" WHERE "users"."id" = $1
irb(main):137:0> Ticket::Article.where(created_by_id:13).pluck(:id)
=> []
irb(main):138:0> Ticket.::Articlewhere(updated_by_id:13).pluck(:id)
Traceback (most recent call last):
SyntaxError ((irb):138: syntax error, unexpected ::, expecting '(')
Ticket.::Articlewhere(updated_by_id:13...
       ^~
irb(main):139:0>

You might want to check that on the current user that fails:

User.find_by(email:'render.rodriguez@amadamachinetools.de').id

Use that ID then

Mhhh… it is id 13 :thinking:

irb(main):139:0> User.find_by(email:'render.rodriguez@amadamachinetools.de').id
=> 13
Ticket::Article.where(created_by_id:13).pluck(:id)
Ticket::Articlewhere(updated_by_id:13).pluck(:id)
Ticket::Articlewhere(origin_by_id:13).pluck(:id)
Ticket::Articlewhere(sender_id:13).pluck(:id)

It has to be one of these for.

Ok, great :crazy_face:

Now i got this:

irb(main):144:0> Ticket::Article.where(created_by_id:13).pluck(:id)
=> []
irb(main):145:0> Ticket::Article.where(created_by_id:13).pluck(:id)
=> []
irb(main):146:0> Ticket::Article.where(updated_by_id:13).pluck(:id)
=> []
irb(main):147:0> Ticket::Article.where(origin_by_id:13).pluck(:id)
=> [409]
irb(main):148:0> Ticket::Article.where(sender_id:13).pluck(:id)
=> []

Now what to do next?

Thank you in advance :man_dancing:

Ok, i found a ticket with this view:

Deleted the ticket and now the user with id 13 is removed :+1::sunglasses:

Thank you for all your good support :vulcan_salute:

Sorry, this is endless… I have now a notification reference to delete :roll_eyes:

Can you help me with this, once again? Pleaase :face_with_hand_over_mouth:

irb(main):363:0> # Actual deletion, requires overview run before
=> nil
irb(main):364:0> User.where(active: false).where.not(id: 1).find_each do |user|
irb(main):365:1* puts "Customer #{user.login}/#{user.email}/#{user.id} has #{Ticket.where(customer_id: user.id).count} tickets"
irb(main):366:1>
irb(main):367:1> puts " Removing references for user with E-Mail #{user.email}..."
irb(main):368:1> ActivityStream.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):369:1> History.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):370:1> Ticket::Article.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):371:1> Ticket::Article.where(updated_by_id: user.id).update_all(updated_by_id: 1)
irb(main):372:1> Store.where(created_by_id: user.id).update_all(created_by_id: 1)
StatsStore.where(created_by_id: user.id).update_all(created_by_id: 1)
Tag.where(created_by_id: user.id).update_all(created_by_id: 1)
if OnlineNotification.find_by(user_id: user.id)==""
OnlineNotification.find_by(user_id: user.id).destroy!
end

puts " Deleting user #{user.login}/#{user.email}..."
user.destroy
endirb(main):373:1> StatsStore.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):374:1> Tag.where(created_by_id: user.id).update_all(created_by_id: 1)
irb(main):375:1> if OnlineNotification.find_by(user_id: user.id)==""
irb(main):376:2> OnlineNotification.find_by(user_id: user.id).destroy!
irb(main):377:2> end
irb(main):378:1>
irb(main):379:1> puts " Deleting user #{user.login}/#{user.email}..."
irb(main):380:1> user.destroy
irb(main):381:1> end
Customer reperich@amadamachinetools.de/reperich@amadamachinetools.de/457 has 0 tickets
 Removing references for user with E-Mail reperich@amadamachinetools.de...
 Deleting user reperich@amadamachinetools.de/reperich@amadamachinetools.de...
Traceback (most recent call last):
        2: from (irb):364
        1: from (irb):380:in `block in irb_binding'
ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR:  update or delete on table "users" violates foreign key constraint "fk_rails_0c0055c5df" on table "online_notifications")
DETAIL:  Key (id)=(457) is still referenced from table "online_notifications".
: DELETE FROM "users" WHERE "users"."id" = $1
irb(main):382:0>

I finally deleted this last user with id 457.
Just with the script from here: https://docs.zammad.org/en/latest/console/dangerzone-for-experts.html#delete-one-or-more-users-with-all-their-related-information

My fault was… I have a working ldap integration. But i changed the ou and zammad imported suddenly all users as customer. When i relinked the roles to the groups in the new ou, zammad deactivated the previously created customers.
That was the point, why i wanted to mass delete inactive users and tried to change the provided script to do this.

I activated the user 457 and used the provided script to delete the user. Worked fine.

Sorry for messing up :roll_eyes:

1 Like

Glad you could finally solve it :slight_smile:

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.