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.
UPDATE: Problem solved. Parameter max_allowed_packet is the packet size for requests. This defaults to 1M and needs to be larger to allow this restore to work. Setting the max_allowed_packet parameter to 10M allowed the restore to complete successfully.
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,0x89504E…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
Download excerpts of the SQL files that fail to restore:
mysql_restore_bug 1.4MB
DVD_Backup_2_40_Rows 14MB
These excerpts fail as well. The complete SQL backup file is 270MB, so not only is it large but I don’t really want to share the entire file. By the way, “Analyze Backup File Content” works fine for all the files.
The mysql command fails as well:
C:DarrellVideo_Databasebackups>mysql --user=root --password=mysecret dvd_video < DVD_Backup_2_40_Rows.sql
ERROR 2006 (HY000) at line 89: MySQL server has gone away
C:DarrellVideo_Databasebackups>mysql --user=root --password=mysecret dvd_video < DVD_Video_Two_Rows_Second_Try.sql
C:DarrellVideo_Databasebackups>
It works for 2 rows, but not for the 40 row file.
Thanks,
Windy
Windy Weather 