copy mysql blob field from one database to the other

Question

Hi!

I happen to have a database with pictures stored as blob fields. Can't help it, it was the previous developer's choice.

Now I need that data in a new site and the provider won't let me copy the data the easy way (file has become 11Mb big - won't upload that and I don't have shell access). So I thought I'd write a script that opens a connection in db1, selects all the records, and then copies each into a table in the new db2.

Works all fine if I exclude the blobs. If I want to copy them too, it won't insert.

Anyone had something similar before? Should I treat the blobs differently when it comes to inserting?

Thanks for any ideas or help.

Answer 1

11MB isn't a huge file, I'm surprised your host has such a low max upload size.

Have you thought about exporting as SQL, splitting the file in two (in Notepad++ or something) then uploading it in smaller sections? Wouldn't take long.

Answer 2

Perhaps check to see if you can increase the max_allowed_packet setting on your mysql DB. I'm not sure if it affects inserts, but I remember having to adjust this setting when I worked on a web-app that allowed users to download 3-5MB binaries from blob fields in the DB.

This link may be helpful, from a quick google search: http://www.astahost.com/info.php/max_allowed_packet-mysql_t2725.html