MySQL Bugs: #8501: Backup/Restore does not work with Blob colums

Bug #8501	Backup/Restore does not work with Blob colums
Submitted:	14 Feb 2005 15:34	Modified:	16 Feb 2005 17:04
Reporter:	James Wilson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Administrator	Severity:	S1 (Critical)
Version:	1.0.19	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Backup/Restore does not work with Blob colums. While the Backup apears to produce a valid sql file Restore is unable to properly read it in.

How to repeat:
Backup the schema to a sql file. Restore the schema on another computer.

Suggested fix:
None found

I can consistently repeat this problem. Do you want me to send you the SQL file generated (36 MB)? While it is syntactically correct it cannot be restored. In fact MySQL Administrator will not consistently analyze it. When trying to restore it I get the error:
The MySQL Server returned this error: MySQL Server Error Nr: 2006 – MySQL Server has gone away.

The first row it chokes on is about 1 MB. I was able to process more records by turning off: Extended Inserts.

I had the same problem - and found the following solution:

I added the following entry to the my.ini (Ini-File for the
mySQL-Instance)

<snip>
[mysqld]

# 16 MB
# max_allowed_packet=16777216
# 128 MB
max_allowed_packet=134217728
</snip>

After that I restartet the mySQL-Server and had no problems with blobs.

Attention: The "mySQL Server Instance config wizard" will remove this
lines again!

May be the section has another name in your case. You can find
the section name in the "MySQL Administrator" in 
"Service Control\Configure Service", Group: "Configuration File"
Attribute "Section Name".

I am not sure if this will help you because your problem seem a little
bit old but perhaps some other user will find it helpful.

(After this problem I encountered another problem: "MySQL Error Nr.1302"
I solved this problem as follows:

1) Find out wich table is the problem
2) Change the Create table-Statement in the Backup-File:

<snip>
CREATE TABLE `bugs` (
...
) ENGINE=MyISAM;
-- ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
</snip>

As you can see, I just deleted the part "DEFAULT CHARSET=latin1"
(more or less I copied the line, commented the old version and deleted
the part in the new version).

After that I could restore all data without problems.

br
Martin