Solved: SQL Server to MySQL Migration

[RBehr]

We have been dev testing OTRS and OTRS::ITSM as follows:

• Windows 2003 Server
  OTRS version 3.0.5
  OTRS::ITSM version 2.1.92
  iPhone Handle version 1.0.2
  Apache version 2.2
  Strawberry version Perl 5.12.2.0
  ODBC link to Windows Server 2003 with Microsoft SQL Server 2005, 64-bit build 3790 SP2

We would like to migrate this installation to a Linux Server with MySQL (i.e. to the recommended environment for OTRS).

We expect an international user base initially in excess of 100 users, but we need to build a scalable solution.

What architecture is recommended for a production environment? What would be the process for migration of the data currently held in our Microsoft SQL Server 2005 database?

[ferrosti]

There is a migration whitepaper available at mysql.com for free (registration required).

Users are not the problem to scale in OTRS, its the number of tickets.

[MichaelR]

Fibre channel MySQL Cluster ftw

[RBehr]

We have succeeded in bringing data across from a SQL Server database into MySQL. We did this by creating an Access database (of all things!) with ODBC links to both the SQL and the MySQL data sources. OTRS runs fine, all the data has come across.

The exception is the "body" of ticket items - Notes, e-mails, etc. This appears to have come across in come sort of encoding, perhaps MIME or similar. An example is:

PGh0bWw+PGhlYWQ+PG1ldGEgaHR0cC1lcXVpdj0iQ29udGVudC1UeXBlIiBjb250ZW50PSJ0ZXh0 L2h0bWw7IGNoYXJzZXQ9dXRmLTgiLz48L2hlYWQ+PGJvZHkgc3R5bGU9ImZvbnQtZmFtaWx5Okdl bmV2YSxIZWx2ZXRpY2EsQXJpYWwsc2Fucy1zZXJpZjsgZm9udC1zaXplOiAxMnB4OyI+VW5pdCBm YWlsZWQgSW1wYWN0IFBESSwgYXdpdGluIHJlc3BvbnNlIGZyb20gQ3Jlc3Ryb248L2JvZHk+PC9o dG1sPg==

Are these items stored with some sort of encoding? Is there a means of decoding this?

[RBehr]

The odd thing is, looking at the tables, the text seems to be stored properly. It is OTRS itself which is BASE64 encoding the output. What would be the cause of this? (It is BASE64 - I can translate the output back into the original HTML). New tickets are displayed fine, but the migrated data is displayed in BASE64 encoded.

[RBehr]

I have found a workaround to this. The problem is in the BLOB field "content" in table "article_attachments". Something happens to the contents of this field during migration. Not sure what, since the BASE64 displayed in OTRS can be put through a translator and does return the correct HTML, so it is not being corrupted.

The workaround is to selectively repopulate article_attachments, leaving out the rows which refer to the actual body of an article (as opposed to genuine attachments). I can identify these from the "content_type" field (leave out all rows with content type "text/html", "text/html; charset="utf-8"" and similar). OTRS, lacking the BLOB field for HTML content of the article, instead refers to the "a_body" field in table "article". So the content of the article is displayed in plain text rather than HTML.

As I say, it is a workaround and I would value any input from anybody who has encountered similar issues during SQL2005->MySQL migration.

[cmadoery]

Hi Rod

We do have exactly the same problem whit migration form MSSQL to MySLQ. See this http://forums.otrs.org/viewtopic.php?f=33&t=12743
Reason to change DB where some UTF8 interface problems with MSSLQ.
I fail on checking your workaround, as I'm more or less not very experienced with databases. But I try to follow it hopefully in some free time during this days...

Christof

[cmadoery]

DB migration made some changes to tables like article_plain, article_attachments, web_upload_cache etc. instead of LONGBLOB something like VARCHAR... OTRS does not like this
anyhow I try to fix this manually.

Christof