Date: 07/20/09 (MySQL Communtiy)    Keywords: php, mysql, xml, database, sql

hey again
i'm trying to convert an oracle db to mysql. so far everything is going ok except for the files in the db. the old admin was storing jpg, pdf, doc, xml files and whatever else you can think of into a blob field in the table on oracle.

is this the best way to do things? i mean sure all you need to do is back up the database and presto you have all the files. but i'm having a hell of a time getting a backup. 440 entries in the table comes up to a 180 megabyte text file. phpmyadmin won't process the file because it times out, sqlyog and navicat are having trouble with the file size saying they're running out of memory.


the closest i've come to completing this transfer is using toad for oracle and outputting the table to an mdb file. then in navicat i use the wizard and import the mdb, it sees the fields of the table perfectly, but the blobs come out having 0k. aside from that small issue i keep getting error 2006 when importing. and lose between 3 and 219 entries depending on the time i'm importing.

so, at a loss, am i doing things right? is it possible to convert oracle blobs to mysql blobs? personally if i had written the original site i would just save a link to the actual file in the db and not store the actual file in the db. does that make sense?


*UPDATE*
so i tried using the migration tool, no luck. i'm missing libraries and my tech guy wasn't here yesterday. the site for the oracle libraries didn't want to work either.
so this is what i ended up doing:
in toad i saved a csv/txt file of the table without the blobs in the csv. cut the size down to 88k.
i uploaded that via sqlyog into the db. no problems.
then in toad i saved the blobs themselves as individual .dat files. so at least i have the files this way.
the db actually has the filename in it, so it was just a simple extracting the filename, copying the .dat files from one folder to another on the server with a simple php script and changing the names to reflect their actual names. all the files can be opened no problem once the name have been changed.

now i just need to link to said files from db to directory on the server.

thanks again for the input :)

Source: http://mysql.livejournal.com/136836.html