OTRS migration - Validation failed

[BNC]

Infos:

Hi Zammad Community

Trying to migrate from an old OTRS to Zammad using the Zammad - rails console.

I am already grateful for any help or advice from the community to successfully complete the migration.

• Used Zammad version: 5.1.1
• Used OTRS version: 5.0.26
• installed OTRS packages: FAQ, TimeAccounting
• Used Zammad installation type: package (zammad_5.1.1-1651218076.e3861908.bullseye_amd64.deb)
• Operating system: Debian 11.3.0
• Browser + version: / via Zammad - rails console

Expected behavior:

• That the OTRS migration will be completed successfully

Actual behavior:

• After starting the migration the following error message is thrown after a short time: ActiveRecord::RecordInvalid (Validation failed: Data option must have integer for :maxlength)

The full zammad cli log (FQDN, Key and organization were replaced):

root@Hostname:# systemctl stop zammad
root@Hostname:# zammad run rails c
Loading production environment (Rails 6.0.4.8)
irb(main):001:0>
irb(main):002:0> Delayed::Worker.max_run_time = 7.days
=> 7 days
irb(main):003:0> Setting.set('import_otrs_endpoint', 'https://internFQDN/otrs/public.pl?Action=ZammadMigrator')
Setting.reset_change_id: set new cache, baf53d76-569b-4c05-9a00-c9b517f79f0d
Setting.set('import_otrs_endpoint', "https://internFQDN/otrs/public.pl?Action=ZammadMigrator")
=> true
irb(main):004:0> Setting.set('import_otrs_endpoint_key', 'KEY')
Setting.reset_change_id: set new cache, 42498711-e6a4-4f40-9a60-8be0c647c676
Setting.set('import_otrs_endpoint_key', "KEY")
=> true
irb(main):005:0> Setting.set('import_mode', true)
Setting.reset_change_id: set new cache, e749bdd5-ef1a-4777-b9b1-35fc4023719e
Setting.set('import_mode', true)
=> true
irb(main):006:0> Import::OTRS.start
thread#-: Start import...
thread#-: POST: https://internFQDN/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Action=>"ZammadMigrator", :Key=>"KEY"}
thread#-: loading SysConfig...
thread#-: POST: https://internFQDN/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Subaction=>"Export", :Object=>"SysConfig", :Limit=>"", :Offset=>"", :Diff=>0, :Action=>"ZammadMigrator", :Key=>"KEY"}
Setting.reset_change_id: set new cache, e8f03c1c-4b3a-4380-ba07-0190fa1ab064
Setting.set('http_type', "https")
Setting.reset_change_id: set new cache, 91051987-75a8-44c7-a3c3-e662f6f6e985
Setting.set('organization', "company Name")
Setting.reset_change_id: set new cache, 300557a9-c641-4f41-992c-284222b6e6d4
Setting.set('system_id', "35")
Setting.reset_change_id: set new cache, 96e3f931-7d84-451e-abbd-f42f89405dd3
Setting.set('ticket_hook', "Ticket#")
Setting.reset_change_id: set new cache, 83549fa7-f2d5-4670-b391-01170e4c1757
Setting.set('ticket_number', "Ticket::Number::Date")
Setting.reset_change_id: set new cache, 77923c9b-3be8-4bda-959f-e94eb17516ad
Setting.set('ticket_number_date', {:checksum=>true})
thread#-: loading DynamicField...
thread#-: POST: https://internFQDN/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Subaction=>"Export", :Object=>"DynamicField", :Limit=>"", :Offset=>"", :Diff=>0, :Action=>"ZammadMigrator", :Key=>"KEY"}
-- add_column("tickets", "pre_proc_application_recorded", :string, {:limit=>255, :null=>true})
   -> 0.0684s
-- add_column("tickets", "pre_proc_days_remaining", :string, {:limit=>255, :null=>true})
   -> 0.0028s
-- add_column("tickets", "pre_proc_vacation_start", :date, {:default=>nil, :null=>true})
   -> 0.0023s
-- add_column("tickets", "pre_proc_vacation_end", :date, {:default=>nil, :null=>true})
   -> 0.0021s
-- add_column("tickets", "pre_proc_days_used", :string, {:limit=>255, :null=>true})
   -> 0.0017s
-- add_column("tickets", "pre_proc_emergency_telephone", :string, {:limit=>255, :null=>true})
   -> 0.0016s
Traceback (most recent call last):
       14: from (irb):6
       13: from lib/import/otrs.rb:26:in `start'
       12: from lib/import/otrs.rb:144:in `base_objects'
       11: from lib/import/otrs.rb:58:in `import'
       10: from lib/import/otrs.rb:134:in `import_action'
        9: from lib/import/base_factory.rb:9:in `import_action'
        8: from lib/import/base_factory.rb:40:in `import_loop'
        7: from lib/import/base_factory.rb:40:in `each'
        6: from lib/import/base_factory.rb:12:in `block in import_action'
        5: from lib/import/base_factory.rb:36:in `create_instance'
        4: from lib/import/base_factory.rb:36:in `new'
        3: from lib/import/otrs/dynamic_field.rb:15:in `initialize'
        2: from lib/import/otrs/dynamic_field.rb:58:in `add'
        1: from app/models/object_manager/attribute.rb:395:in `add'
ActiveRecord::RecordInvalid (Validation failed: Data option must have integer for :maxlength)
irb(main):007:0>
irb(main):008:0>

After cross-reading the community topics is it possible, that custom dynamic fields in the OTRS instance could be causing this issue?

If needed i have extracted the following (log-)files, before restoring the original system states

Zammad

• production.log

OTRS

• mariadb.log
• Apache error_log, access_log, ssl_*_log

HTTP POST - https://internFQDN/otrs/public.pl?Action=ZammadMigrator&Subaction=Export&Key=KEY&Object=DynamicField&Diff=0&Limit=&Offset=

• public.pl.json

Unfortunately, I have no experience as far as Zammad is concerned and my knowledge of the OTRS instance at hand is also limited. Therefore i am grateful for any help and advice.

[MrGeneration]

Have a look what field comes after pre_proc_emergency_telephone. Due to the error it’s not telling you the name so it’s kinda fishing in the dark. Can’t speak for OTRS - but Zammad is expecting field size limits which is why you receive above error.

I guess the easiest way to solve this is to either update the OTRS field to meet the requirements or drop it. Depending on your options and how the actual original field looks like.

[BNC]

@MrGeneration: Thank you very much for the support. It was indeed the dynamic field which comes after the pre_proc_emergency_telephone field.
After resolving some additional (minor) Problems we managed to import the OTRS data to our new Zammad instance.

For those, interested in additional information:
In our OTRS instance there were two causal field-types causing the ActiveRecord::RecordInvalid (Validation failed: Data option must have integer for :maxlength) error. The field-type of both dynamic fields (PreProcRepresentationBy and PreProcVacationInfo) was set as textfield. Deleting (only) these two dynamic fields resolved this error.

Note: The remaining dynamic “Text” fields that were set as text were imported seamlessly.

After fixing the error with the dynamic fields, an additional Error occurred during the Zammad import.

thread#-: loading State…

    …

    1: from lib/import/otrs/state.rb:65:in `state_type_id'

NoMethodError (undefined method `id’ for nil:NilClass)

After looking at the following Zammad Issue

Issue: Migrating OTRS 5.x to Zammad 2.9.0 #2019

Hi @imam32 - I can see from the result of the API endpoint that you have a custom state type: { "ChangeTime": "2016-04-21 15:52:54", "ID": "10", "ValidID": "1", "TypeID": "8", "CreateTime": "2016-04-21 15:52:54", "TypeName": "ready close", "Comment": "own state type Ready to Close", "Name": "ready to close" }, In a regular OTRS there is no StateType called ready close. The import doesn’t know how to handle that and will fail. You have to remove this State and…

I followed the Advice from @thorsteneckel, migrated all affected Tickets (Using the OTRS –> Admin –> GenericAgent –> New Job –> selecting Tickets –> Status) to a predefined state and afterwards deleting all custom State-Types

OTRS –> Admin –> State Management –> select custom state –>

1. Validity –> invalid –> safe
2. State type –> removed –> safe (a predefined state)

Following this adjustment, the Zammad import completed without further errors.

The full zammad cli log (FQDN, Key and organization were replaced):

irb(main):001:0>
irb(main):002:0> Import::OTRS.start
thread#-: Start import…
thread#-: POST: https://internFQDN/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Action=>“ZammadMigrator”, :Key=>“KEY”}
thread#-: loading SysConfig…
thread#-: POST: https://internFQDN/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Subaction=>“Export”, :Object=>“SysConfig”, :Limit=>“”, :Offset=>“”, :Diff=>0, :Action=>“ZammadMigrator”, :Key=>“KEY”}
Setting.reset_change_id: set new cache, 3d11535f-3050-4220-b0a7-b916bd327151
Setting.set(‘http_type’, “https”)
Setting.reset_change_id: set new cache, e80b4a4c-5ef3-49fc-ac0c-6701b7b9b5e1
Setting.set(‘organization’, “BNC Netcare”)
Setting.reset_change_id: set new cache, fe64dd81-5629-492e-b3a2-a53dd3b28603
Setting.set(‘system_id’, “35”)
Setting.reset_change_id: set new cache, ab4d4576-b1d5-4ec3-97f2-53a54486dc6a
Setting.set(‘ticket_hook’, “Ticket#”)
Setting.reset_change_id: set new cache, 96b7790d-b436-4ec4-b6de-eba5025dfd1c
Setting.set(‘ticket_number’, “Ticket::Number::Date”)
Setting.reset_change_id: set new cache, affcdb47-dcc0-4c35-8c56-29963e84e586
Setting.set(‘ticket_number_date’, {:checksum=>true})
thread#-: loading DynamicField…
thread#-: POST: https://internFQDN/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Subaction=>“Export”, :Object=>“DynamicField”, :Limit=>“”, :Offset=>“”, :Diff=>0, :Action=>“ZammadMigrator”, :Key=>“KEY”}
thread#-: loading State…
thread#-: POST: https://internFQDN/otrs/public.pl?Action=ZammadMigrator
thread#-: PARAMS: {:Subaction=>“Export”, :Object=>“State”, :Limit=>“”, :Offset=>“”, :Diff=>0, :Action=>“ZammadMigrator”, :Key=>“KEY”}
thread#-: update Ticket::State.find_by(id: 1)
thread#-: add Ticket::State.find_by(id: 10)
Traceback (most recent call last):
16: from lib/import/otrs.rb:148:in updateable_objects' 15: from lib/import/otrs.rb:58:in import’
14: from lib/import/otrs.rb:134:in import_action' 13: from lib/import/transaction_factory.rb:11:in import’
12: from lib/import/transaction_factory.rb:12:in block in import' 11: from lib/import/base_factory.rb:9:in import_action’
10: from lib/import/otrs/state_factory.rb:24: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/state.rb:21:in initialize' 3: from lib/import/otrs/state.rb:27:in import’
2: from lib/import/otrs/state.rb:58:in map' 1: from lib/import/otrs/state.rb:65:in state_type_id’
NoMethodError (undefined method `id’ for nil:NilClass)
irb(main):003:0>


Thank you for your help and perfect support not only in solving this problem, but also for your efforts and technical support in this community.

[Martin-Bonn]

Out of curiousity: I did an import from OTRS yesterday and for tickets with a pending close or pending reminder state, the import set the correct ticket state, but the pending date is empty.

Did it work for you?

I am going to force a pending date of today and everyone will have to go through their tickets and update the pending date.

I will also do some cleanup like correcting the author of notes. Right now, they are all shown as created by the system (created_by 1). It only shows the real author in the To header when you click the note.

Similar for some (maybe all) mail replies received for a ticket.

[BNC]

I used a copy of the original OTRS system for the Zammad import (changed all needed settings [webserver, ip]) and migrated all (not closed) Tickets to the predefined state open… So I really don’t know about the pending dates of the tickets.

Since there is a known bug (since 08/2018!) concerning the visible list of users (far less than 50 users are shown in the users overview),

https://admin-docs.zammad.org/en/latest/manage/users/via-the-admin-panel.html

Zammad limits the number of users shown within admin panel

I first need to get a proper List off all Users for my upper management before the Zammad instance can go online. (A second differential and hopefully not a full import). Therefore, I did not look too closely at the imported data… But the author of each ticket with the state open was correctly imported and is displayed correctly.

For the Users-List: I am currently working on an Ansible Playbook (looping through the Zammad REST-API) which will hopefully soon generate a useful csv list containing all users with their corresponding Information.

But thank you for the information. When the time comes, I will have to look at the imported data for the second import…

[Martin-Bonn]

The customer of the ticket was correctly set by the import, but the user of the ticket’s articles was not. Articles were set to created_by_id 1 instead of the user who wrote that article.

As indicated by the Zammad bird profile pictures:

Did an update run with this:

Ticket::Article.where.not(from: "").where(created_by_id: 1).find_each do |article|
  mail = Mail.new
  mail.from(article.from)
  from = mail.from
  if not from.empty? and not mail.from[0].nil?
    user = User.find_by(email: mail.from[0].downcase)
    if not user.nil?
      puts "Setze Ticket #{article.ticket_id}, Artikel #{article.id}, Email #{article.from} auf User ID #{user.id} - #{user.firstname} #{user.lastname}"
      article.update(created_by_id: user.id)
    else
      puts "USER NICHT GEFUNDEN für Ticket #{article.ticket_id}, Artikel #{article.id}, Email #{article.from} = #{mail.from[0].downcase}"
    end
  else
    puts "FEHLER PARSING DER ADRESSE für Ticket #{article.ticket_id}, Artikel #{article.id}, Email #{article.from}"    
  end
 end

Ran this of hours, so not tuned for performance and probably not very ruby-esk, but I am not a programmer anymore and never touched Ruby before Zammad

[MrGeneration]

Depending on your systems memory available and the number of articles the system has, above is a very very bad idea. The reason: Above occupies the required memory for all ticket articles at once. This is quite expensive so to say.

Better do it like this:

Ticket::Article.where ... ).find_in_batches do |batch|
   batch.each |article|
      ... magic
   end
end