Error by migration OTRS 6 to Zammad

  • Used Zammad version: 5.0.x
  • Used Zammad installation type: package
  • Operating system: Ubuntu 20.04
  • Browser + version: Chrome Version 94.0.4606.81

Expected behavior:

Migration data from OTRS is completed

Actual behavior:

When starting import I shortly got the message “ActiveRecord::InvalidForeignKey (Mysql2::Error: Cannot add or update a child row: a foreign key constraint fails (zammad.ticket_priorities, CONSTRAINT fk_rails_d43af6872e FOREIGN KEY (updated_by_id) REFERENCES users (id)))”

Tested from cli and here is the full log:
From Command Line :

sudo zammad run rails c
Loading production environment (Rails 6.0.4.6)
irb(main):001:0>  Setting.set('import_otrs_endpoint', 'https://xxx/otrs/public.pl?Action=ZammadMigrator')                                                                                                             Setting.reset_change_id: set new cache, bff13acb-cf29-46b4-a6a3-45d66c2339d3
Setting.set('import_otrs_endpoint', "https://xxx/otrs/public.pl?Action=ZammadMigrator")
=> true
irb(main):002:0> Setting.set('import_otrs_endpoint_key', 'xxx')                                                                                                                                         Setting.reset_change_id: set new cache, 5b6672e0-0328-4cf1-be08-41e5c903230d
Setting.set('import_otrs_endpoint_key', "xxx")
irb(main):003:0> Setting.set('import_mode', true)                                                                                                                                                                                            Setting.reset_change_id: set new cache, b4bff099-35f7-4b6e-b0af-5381ba40d522
Setting.set('import_mode', true)
=> true
irb(main):004:0> Import::OTRS.start                                                                                                                                                                                                          thread#-: Start import...
thread#-: POST: https://xxx/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Action=>"ZammadMigrator", :Key=>"xxx"}
thread#-: loading SysConfig...
thread#-: POST: https://xxx/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Subaction=>"Export", :Object=>"SysConfig", :Limit=>"", :Offset=>"", :Diff=>0, :Action=>"ZammadMigrator", :Key=>"xxx"}
Setting.reset_change_id: set new cache, 82163fd9-fe24-4f20-bed1-fbb12c25acd5
Setting.set('http_type', "https")
Setting.reset_change_id: set new cache, d5816c35-5794-4002-9095-861addf9d5dd
Setting.set('organization', "XXX")
Setting.reset_change_id: set new cache, 8e636ee9-6273-4afd-9bf9-7679bb0862b6
Setting.set('system_id', "04")
Setting.reset_change_id: set new cache, 12229fe7-b973-4c4a-8d73-65d7dcf6e832
Setting.set('ticket_hook', "Ticket#")
Setting.reset_change_id: set new cache, 0b4583af-4016-4663-8153-490380c95975
Setting.set('ticket_number', "Ticket::Number::Date")
Setting.reset_change_id: set new cache, ff4f97e8-ed20-4116-8ec7-da36c61aec89
Setting.set('ticket_number_date', {:checksum=>true})
thread#-: loading DynamicField...
thread#-: POST: https://xxx/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Subaction=>"Export", :Object=>"DynamicField", :Limit=>"", :Offset=>"", :Diff=>0, :Action=>"ZammadMigrator", :Key=>"xxx"}
thread#-: loading State...
thread#-: POST: https://xxx/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Subaction=>"Export", :Object=>"State", :Limit=>"", :Offset=>"", :Diff=>0, :Action=>"ZammadMigrator", :Key=>"xxx"}
thread#-: update Ticket::State.find_by(id: 1)
thread#-: update Ticket::State.find_by(id: 10)
thread#-: update Ticket::State.find_by(id: 2)
thread#-: update Ticket::State.find_by(id: 3)
thread#-: update Ticket::State.find_by(id: 4)
thread#-: update Ticket::State.find_by(id: 5)
thread#-: update Ticket::State.find_by(id: 6)
thread#-: update Ticket::State.find_by(id: 7)
thread#-: update Ticket::State.find_by(id: 8)
thread#-: update Ticket::State.find_by(id: 9)
thread#-: loading Priority...
thread#-: POST: https://xxx/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Subaction=>"Export", :Object=>"Priority", :Limit=>"", :Offset=>"", :Diff=>0, :Action=>"ZammadMigrator", :Key=>"xxx"}
thread#-: update Ticket::Priority.find_by(id: 1)
Traceback (most recent call last):
       16: from (irb):4
       15: from lib/import/otrs.rb:28:in `start'
       14: from lib/import/otrs.rb:149:in `updateable_objects'
       13: from lib/import/otrs.rb:58:in `import'
       12: from lib/import/otrs.rb:134:in `import_action'
       11: from lib/import/base_factory.rb:9:in `import_action'
       10: from lib/import/otrs/priority_factory.rb:12:in `import_loop'
        9: from lib/import/base_factory.rb:40:in `import_loop'
        8: from lib/import/base_factory.rb:40:in `each'
        7: from lib/import/base_factory.rb:12:in `block in import_action'
        6: from lib/import/base_factory.rb:36:in `create_instance'
        5: from lib/import/base_factory.rb:36:in `new'
        4: from lib/import/otrs/priority.rb:20:in `initialize'
        3: from lib/import/otrs/priority.rb:26:in `import'
        2: from lib/import/otrs/priority.rb:30:in `create_or_update'
        1: from lib/import/otrs/priority.rb:40:in `updated?'
ActiveRecord::InvalidForeignKey (Mysql2::Error: Cannot add or update a child row: a foreign key constraint fails (`zammad`.`ticket_priorities`, CONSTRAINT `fk_rails_d43af6872e` FOREIGN KEY (`updated_by_id`) REFERENCES `users` (`id`)))
irb(main):005:0> exit

I understand that something in the database is not working but how exactly I can solve it is unclear to me as I have very little experience with SQL. Any suggestions? I would appreciate it if anyone could help :slight_smile: )

Steps to reproduce the behavior:

What OTRS version are you migrating from?
Could you check what values ChangeBy and CreateBy the priorities in the OTRS installation have?

This may be the root of your issue.

The Problem was solved with a workaround found by github.

@irina, why not share the workaround or a link to it here? It could help other users of this forum in the future

Hier ist the link : Link

And the most important part is:

“As a workaround you can go to your OTRS Priority Admin Interface and update all affected Priorities with the root@localhost user.”
After I did that (and it wasn’t easy to find the credentials :smiley:) migration ran through. And it took a 2.5 hours by over 13000 Tickets.