Export/Import database with large blobs in MySQL

735 pts.
Tags:
BLOB
Database
MySQL
mysqldump

Hi, we are having trouble exporting and importing databases with blobs in mysql. We have a development environment and a production environment both running mysql-5.0.45 and want to dump databases with blobs from one to the other. We have tried with many options but nothing works. When importing the databases mysql partially imports data and suddenly stops and reports errors such as:

Error: Unknown Command '''

and

Unknown Mysql server host: '[random text from the mysql dump file]'

We did a lot on googling and found many suggestions that didn't work. Some suggestions included increasing the max_allowed_packet to 1GB and increasing the read_buffer_size but none works.

We also tried exporting using --hex-blob to no avail.

Our last dump statement:

mysqldump -uroot -ppword --skip-extended-insert --max_allowed_packet=1G --read_buffer_size=16M --hex-blob databasename> /dir/databasename.sql

Our last restore statement:

mysql -uroot --max_allowed_packet=1G --read_buffer_size=16M databasename < /dir/databasename.sql



Software/Hardware used:
IBMi V7R1, Zend Server 5.6.0, MySQL-5.0.45

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Are the databases stored in DB2 tables? (I.e., is the DB2 storage engine, IBMDB2I, used for actual data?) The mysqldump function might not work well with data not stored in the MySQL database itself. If you haven't already, this probably needs to be addressed in the Zend Server for IBM i forums. Any resolution will likely need to come from strong knowledge of the DBi internals. -- Tom
    125,585 pointsBadges:
    report
  • jailall101
    The data is stored in mysql databases itself and we do not have zend dbi installed yet. We are currently using mysql-5.0.45 incorporated with zend server.
    735 pointsBadges:
    report
  • CarlCioffi
    If you haven't found a solution yet. I haven't worked with MySql in a while but I had this problem doing large database queries. MySql has issues with large packets of data some that can be solved by adjusting MySql parameters others by committing your changes as you go to keep the buffers clear.
    345 pointsBadges:
    report
  • jailall101
    We found the problem and figured a way of solving it. The problem was that mysql dumps non-ascii characters in the mysql dump file which causes the import to fail. This we think has to do with drupal writing latin1 chars in utf8 tables or vice-versa. To solve this(or to work around this) we had to edit the dump file and find the characters that are causing the import to fail and then compare it with the original database to get it's ascii representation. This is the conversion that we came up with (for others that have the same problem): ů -> ? é -> é í -> í Ä -> extra char, just delete it. â?¦ -> extra char, just delete it. â?¢ -> 3 spaces(indent) ï?¼ -> ? ô -> ô ç -> ç à -> î â?? -> extra char, delete  -> extra char, just delete it. ¼ -> 1/4
    735 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following