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
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
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.