Perhaps someone out there has already written something similar to do this, so that we need not re-invent the wheel? <br><br>On another note, I cannot seem to find the RT3 MySQL to Oracle conversion script/info that has been mentioned in some older posts. It is not on the BestPractical site that I can find, and Google apparently used to come up with a cached copy of this script/info, but that seems to be no longer available as well. (Google for "RT3MySQL2Oracle")
<br><br>Any guidance would be much appreciated.<br><br>Thanks and regards.<br><br><div><span class="gmail_quote">On 6/14/07, <b class="gmail_sendername">Kenneth Marshall</b> <<a href="mailto:ktm@rice.edu">ktm@rice.edu</a>
> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">On Wed, Jun 13, 2007 at 04:26:08PM -0400, Todd Williams wrote:<br>> Hi Joop,
<br>><br>> Yes, that is what we found -- all new attachments to the Oracle DB are<br>> indeed Base64 encoded. Not sure why RT sees Oracle as binary unsafe<br>> (perhaps earlier versions were determined as unsafe) but I suspected this
<br>> might be the case. However, in our case, the migrated attachment data can<br>> be manually extracted from Oracle verbatim and compared with what was in the<br>> MySQL database, binary compatible bit for bit. It's all in how RT is
<br>> interpreting the attachment data coming from the newly migrated Oracle<br>> database tables.<br>><br>> Could we easily address the interpretation issue within the RT code somehow,<br>> or should we address the issue at the database level where there are 700+
<br>> tickets with multiple migrated attachments (automate the recoding of all<br>> attachments)? I have looked at several places in the RT code and it is not<br>> painfully obvious where one might attempt to work around this issue.
<br>><br>> Thanks for all your help and guidance in advance,<br>><br>> Todd<br>><br><br>Todd,<br><br>PostgreSQL suffers from the same classification as Oracle in this<br>regard. It should be very easy to run through your attachments table
<br>and just re-encode your contents to base64.<br><br>Ken<br><br>> On 6/13/07, Joop van de Wege <<a href="mailto:JoopvandeWege@mococo.nl">JoopvandeWege@mococo.nl</a>> wrote:<br>> ><br>> >Todd Williams wrote:
<br>> >> Hello,<br>> >><br>> >> Here are the pertinent details for our attempted migration:<br>> >><br>> >> Suse Linux 9.3 x86 32-bit<br>> >> MIGRATING: MySQL 4.0.18 to Oracle 9i
9.2.0.6.0<br>> >> RT 3.6.3<br>> >> Perl 5.8.3<br>> >> DBIx::SearchBuilder 1.45, DBD::Oracle 1.19<br>> >> Apache 2.0.49<br>> >> Oracle SQL Developer & MySQL Migration Workbench plugin (latest)
<br>> >><br>> >><br>> >> Issue: Binary Attachments & MySQL->Oracle DB Migration<br>> >><br>> >> Does anyone have experience with a successful MySQL 4.x to Oracle 9x RT<br>
> >> migration?<br>> >><br>> >> 1. We have a perfectly functioning RT instance on this maching using<br>> >> either MySQL or Oracle database type. I have done both installs, and<br>> >> both work independently.
<br>> >><br>> >> 2. After some trial and error while migrating to the Oracle database,<br>> >> things seem to function much better if I allow RT to create the initial<br>> >> DB Schema, rather than attempt to have the Oracle tool migrate the
<br>> >> schema from MySQL.<br>> >><br>> >> 3. I have used the Oracle SQL Developer to migrate the data from the<br>> >> MySQL to the Oracle instance.<br>> >><br>> >> 4. The database contents seem to have migrated without any issue,
<br>> >> however, a primary issue is the binary attachments appear to be corrupt<br>> >> coming from the migrated Oracle DB.<br>> >><br>> >> 5. Upon comparing the contents of an attachment bit-for-bit from the
<br>> >> MySQL to Oracle tables, the data in each is identical post migration!!<br>> >><br>> >> 6. Text attachments work fine, migrated just fine. Tickets, users,<br>> >> etc. migrated successfully.
<br>> >><br>> >> 7. There appears to be a decoding issue with the Oracle binary<br>> >> attachments (from the migrated MySQL db) in that they seem to grow in<br>> >> size approximately 23% upon downloading, and are corrupted as a result.
<br>> >> The data obtained through RT (downloading a ticket attachment, both WEB<br>> >> and CMDLINE) is NOT the expected data stored in the Oracle table.<br>> >> Perhaps this is a UTF8 issue of some sort?
<br>> >><br>> >> 8. Hex dump (snippet) of data (Correct in the database, Incorrect<br>> >> produced out of RT's migrated database):<br>> >><br>> >> Correct data: d0 cf 11 e0 a1 b1 1a e1 00 00 00 00 00 00 00 00 00 00
<br>> >> 00 00 00 00 00 00 3e 00 03 00 fe ff 09 00<br>> >> Incorrect data: ef bf bd ef bf bd 11 ef bf bd ef bf bd ef bf bd 1a ef<br>> >> bf bd 00 00 00 00 00 00 00 00 00 00 00 00<br>> >>
<br>> >> Note the 8-bit characters from the correct data above being replaced by:<br>> >> 0xEF 0xBF 0xBD in what is obtained from RT in the incorrect data line<br>> >> above.<br>> >><br>
> >> 9. How do we overcome this so that previous (migrated) attachments work<br>> >> correctly, and going forward all new attachments appear correctly as<br>> >well?<br>> ><br>> >I'm using Oracle too, Oracle XE. The way rows in the attachments table
<br>> >look is that they are base64 encoded. You can check that by starting<br>> >from scratch and entering one ticket with a picture of word document<br>> >attachment. You'll see that the content column contains the base64
<br>> >encoded data and contenttype and contentencoding columns contain the<br>> >values needed to reconstruct the attachment. What probably is happening<br>> >is that the migration wizard has transferred all the data from MySQL to
<br>> >Oracle but didn't take into account that RT sees Oracle as a binary<br>> >unsafe database, thats why the content column is base64 encoded.<br>> >Depending on what is actually in the attachments table you might be able
<br>> >to transform it to the right values.<br>> ><br>> >If you need help, let me know.<br>> ><br>> >Joop<br>> ><br>> ><br><br>> _______________________________________________
<br>> <a href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users</a><br>><br>> Community help: <a href="http://wiki.bestpractical.com">
http://wiki.bestpractical.com</a><br>> Commercial support: <a href="mailto:sales@bestpractical.com">sales@bestpractical.com</a><br>><br>><br>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
<br>> Buy a copy at <a href="http://rtbook.bestpractical.com">http://rtbook.bestpractical.com</a><br></blockquote></div><br>