[rt-users] MySQL->Oracle DB Migration & Binary Attachment data corruption - UTF8 issue?

[Todd Williams]

Hello,

Here are the pertinent details for our attempted migration:

Suse Linux 9.3 x86 32-bit
MIGRATING:  MySQL 4.0.18 to Oracle 9i 9.2.0.6.0
RT 3.6.3
Perl 5.8.3
DBIx::SearchBuilder 1.45, DBD::Oracle 1.19
Apache 2.0.49
Oracle SQL Developer & MySQL Migration Workbench plugin (latest)


Issue: Binary Attachments & MySQL->Oracle DB Migration

Does anyone have experience with a successful MySQL 4.x to Oracle 9x RT
migration?

1.  We have a perfectly functioning RT instance on this maching using either
MySQL or Oracle database type.  I have done both installs, and both work
independently.

2.  After some trial and error while migrating to the Oracle database,
things seem to function much better if I allow RT to create the initial DB
Schema, rather than attempt to have the Oracle tool migrate the schema from
MySQL.

3.  I have used the Oracle SQL Developer to migrate the data from the MySQL
to the Oracle instance.

4.  The database contents seem to have migrated without any issue, however,
a primary issue is the binary attachments appear to be corrupt coming from
the migrated Oracle DB.

5.  Upon comparing the contents of an attachment bit-for-bit from the MySQL
to Oracle tables, the data in each is identical post migration!!

6.  Text attachments work fine, migrated just fine.  Tickets, users, etc.
migrated successfully.

7.  There appears to be a decoding issue with the Oracle binary attachments
(from the migrated MySQL db) in that they seem to grow in size approximately
23% upon downloading, and are corrupted as a result.  The data obtained
through RT (downloading a ticket attachment, both WEB and CMDLINE) is NOT
the expected data stored in the Oracle table.  Perhaps this is a UTF8 issue
of some sort?

8. Hex dump (snippet) of data (Correct in the database, Incorrect produced
out of RT's migrated database):

Correct data:      d0 cf 11 e0 a1 b1 1a e1 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 3e 00 03 00 fe ff 09 00
Incorrect data:    ef bf bd ef bf bd 11 ef bf bd ef bf bd ef bf bd 1a ef bf
bd 00 00 00 00 00 00 00 00 00 00 00 00

Note the 8-bit characters from the correct data above being replaced by:
0xEF 0xBF 0xBD  in what is obtained from RT in the incorrect data line
above.

9.  How do we overcome this so that previous (migrated) attachments work
correctly, and going forward all new attachments appear correctly as well?


Any thoughts or suggestions?

Thanks!

Todd
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070612/4a5a0f75/attachment.htm>