Importing from OTRS into Zammad

[aelsing]

This is not per se a technical assistance thread but more of a writeup of my experiences importing the OTRS instance I am responsible for into Zammad. I had to redo the import many times until I figured out all the errors and I wanted to post my experiences so others don’t have to go through the trouble if they encounter it.

• Used Zammad version: 2.8
• Used Zammad installation source: package RPM
• Operating system: OpenSuse 42.3

1. ElasticSearch attachment size

ElasticSearch attachment size is set to 10MB in the default installation. Depeding on your existing setup you may have to change that with the commands below. As I knew that the settings in our system have changed over the years and we had the attachments stored in the filesystem on the OTRS I used

find ./ -size +10M -exec ls -lh {} ;

in /opt/otrs/var/article. Adjust path to wherever your attachments are stored. With this information I changed the es_attachment_max_size_in_mb variable to the appropriate value:

zammad run rails c
Setting.set(‘es_attachment_max_size_in_mb’, 50)

2. UTF encoding errors

Tickets with UTF-16 characters encoded in UTF-8 had the effect that the JSON module of OTRS returned a 500 Error message that was presented by the Zammad exporter and shows up in the logs. It could be that this is fixed in later releases of OTRS. I ended up modifying the articles in the OTRS backend (how I briefly describe below after the proper warning message).

Error message

thread#5: ERROR: Server Error: #<Net::HTTPInternalServerError 500 Internal Server Error readbody=true>!
/opt/zammad/lib/import/otrs/requester.rb:132:in post': Zammad Migrator returned an error (RuntimeError) from /opt/zammad/lib/import/otrs/requester.rb:92:in request_json’
from /opt/zammad/lib/import/otrs/requester.rb:79:in request_result' from /opt/zammad/lib/import/otrs/requester.rb:34:in load’
from /opt/zammad/lib/import/otrs.rb:143:in import_action' from /opt/zammad/lib/import/otrs.rb:137:in imported?’
from /opt/zammad/lib/import/otrs.rb:101:in block (3 levels) in threaded_import' from /opt/zammad/lib/import/otrs.rb:95:in loop’
from /opt/zammad/lib/import/otrs.rb:95:in block (2 levels) in threaded_import' from /opt/zammad/vendor/bundle/ruby/2.4.0/gems/logging-2.2.2/lib/logging/diagnostic_context.rb:474:in block in create_with_logging_context’

The Apache log on the OTRS Server says:

> [Thu Jan 03 11:48:15 2019] [error] malformed or illegal unicode character in string [\xed\xa0\xbd\xed\xb8\x89\n \nGr], cannot convert to JSON at /opt/otrs/Kernel/cpan-lib/JSON.pm line 154.\n > [Thu Jan 03 11:53:26 2019] [error] malformed or illegal unicode character in string [\xed\xa0\xbd\xed\xb8\x8a. Du], cannot convert to JSON at /opt/otrs/Kernel/cpan-lib/JSON.pm line 154.\n

To identify the culprit I manually executed the Zammad exporter in the range of the tickets that caused the problem (check production.log). The Offset is calculated from the last ticketnumber in the OTRS System. The Limit can be set bigger at first and the reduced until you find the culprit with Limit=1. If the link below shows an 500 error you identified the ticket that causes the import to fail.

http://helpdesk.XXXX.com/otrs/public.pl?Action=ZammadMigrator&Key=XXXX&Subaction=Export&Object=Ticket&Limit=1&Offset=1236&Diff=0

Luckily I only had a handfull of those but still this one is troublesome. I could think that a SQL search for the affected characters in the article body table can also lead to the results but I did not try that. The possibilities are just to big. In my case however they were all smileys… contained in OTRS articles.

3. 1,5 Mio Character limitation

Identify articles in OTRS which have more than 1.5 Mio characters as this is the limit. In our OTRS we had some MIME data written into the articles so we unfortunatly hit the mark on those tickets.

SELECT * FROM otrs.article WHERE LENGTH(a_body)>1500000;

I used 1 450 000 just to be sure.

4. Correcting issues on the database

The following steps include operating directly on the database of the OTRS system so please be careful and always have a backup!

I used MySQL workbench to edit the field a_body in the tabel article. This is the field that Zammad exporter uses to generate the import data.

I hope this helps a litte when you encounter these issues. I will edit this article if I encounter more problems.

[MrGeneration]

Thanks for sharing your experiences, I’m sure this will help others