MySQL Bugs: #37253: Unable to restore backup file containing BLOBs

Bug #37253	Unable to restore backup file containing BLOBs
Submitted:	6 Jun 2008 19:57	Modified:	7 Jun 2008 7:31
Reporter:	darrell duffy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.2.12	OS:	Windows (Vista Ultimate x86 SP1)
Assigned to:		CPU Architecture:	Any
Tags:	Backup, BLOB, restore

Description:
When building a database of my DVD collection which includes BLOB images of png images of the covers, the database can be backed up, but it cannot be restored. This of course makes the backups useless. The images are 475×475 pixel PNG images.

I’m using MySQL administrator 1.2.12, MySQL 5.0.51b-Community-nt, MySQL client version 5.1.11 running on Windows Vista Ultimate x86 SP1.

The database was built using OpenOffice 2.4.

Using the default backup settings, the error on restore was:

    Error while executing this query:INSERT INTO `dvd-collection` (`dvd_ID`,`MovieTitle`,`Director`,`Producer`,`Actor`,`Genre`,
    `Notes`,`Rating`,`Review`,`Subject`,`Writer`,`Format`,
    `Source`,`DateAcquired`,`ReleaseYear`,`MPAARating`,
    `Length`,`disk_image`) VALUES
    (1,’Step Into Liquid.’,'Dana Brown’,NULL,’Robert August,
    Rochelle Ballard, Shawn Barron, Dave Kalama, Gerry Lopez,
    Robert \”Wingnut\” Weaver’,'Documentary’,
    ‘No stuntmen. No stereotypes. No other feeling comes close’,
    5,NULL,’Surfing’,'Dana Brown’,'DVD’,'DishNetwork’,2006,2003,
    ‘PG’,88,0×89504E…44AE426082);
    The server has returned this error message:MySQL server has gone away
    MySQL Error.

Using these additional settings:

    * InnoDB Online Backup
    * No EXTENDED INSERTS
    * Add DROP Statements
    * Complete INSERTS
    * Comment
    * Compatibility Mode
    * Disable Keys

The error was:

    Unknown object in backup file

See details and example SQL backup files here:
http://www.windyweather.net/wp/2008/06/05/mysql-unable-to-restore/

How to repeat:
Attempt to restore using files here:
http://www.windyweather.net/wp/2008/06/05/mysql-unable-to-restore/

@darell ..

you should probably raise the "max_allowed_packet" setting in server configuration! If there is no explicit setting, default with most recent server versions is (as ridiculously low as) 1M.

Insert in the configuration file [mysqld] section for instance

max_allowed_packet = 100M

restart the server and try again.

Pretty much a shame that the good old MySQL 3.23 'got a packet too large' error practically never is returned any more.  Now we get the meaningless 'gone away' error message!

But there is probably nothing wrong with the dump.  Only there is an INSERT statement larger than max_allowed_packet, I think! 

I do not know MA backups too well (I use mysqldump or SQLyog depending on the situation) and do not know if MA uses BULK INSERTS (more than one row of data for every INSERT) or not - or if it is configurable.  If it is you can try turning it off or set a lower BULK value.

Peter
(not a MySQL person)

Peter,
Thanks. That was the problem. I changed the max_allowed_packet to 10M and all is well. I had assumed that the parameters would fail going both ways and that the backup would fail, or queries with OpenOffice would fail if something this fundamental was a problem. The backup was set to only use two records in an INSERT statement.
Thanks very much,
darrell

Error message was not clear about why restore failed.
Changing max_allowed_packet size to 10M fixed problem of the restore for my database.

Thank you for the report.

Closed as "Not a Bug", because found cause of the problem is not result of a bug.