packets larger than max_allowed_packet are not allowed

Developer
Dec 7, 2012 at 6:28 AM

Hi adrianics

i exported my database with a blob column where i store my files some file may have 5mb in size , export ok, but when i restore the database

error:"packets larger than max_allowed_packet are not allowed"

Coordinator
Dec 7, 2012 at 8:18 AM

I see. crayzyivan, can you post the codes that you write on calling MySqlBackup for the backup & restore?

Developer
Dec 10, 2012 at 7:00 AM

the code that use to backup and restore is same with the demo app, i even use the demo app to restore it but same error.

Here is the link of the part of my sql database that i believe is causing the error.

https://hotfile.com/dl/183383129/4a38527/max_allowed_packet.sql.html

you can run the script to you sample database and export it and then restore it and you'll see the error.thanks

Coordinator
Dec 11, 2012 at 3:51 AM
Edited Dec 11, 2012 at 4:12 AM

Hi crayzyivan,

The sql script file contains some illegal characters in between the hexadecimal string of the exported blob data. The characters should consists of 0-9 and A-F.

However, regarding the error of packets larger than max_allowed_packet are not allowed.

I am able to identify the cause of problem and fixed it. :)

max_allowed_packet defines the maximum length of single SQL query. Default value is 1 MB. If the query is larger than 1MB, exception will raise.

In previous version of MySqlBackup.NET, it does has modify max_allowed_packet to maximum of 1GB, the maximum length allowed by MySQL in single query.

It is modified by executing this SQL query:

SET GLOBAL max_allowed_packet = 1024*1024*1024;

But the problem is, the modified max_allowed_packet will only take effect on new connection, not current connection. Therefore, 1 more step has to be taken: after executing the above query, the connection should be reset (Close & Re-Open).

This is fixed in MySqlBackup 1.5.5 beta. You may want to review it. 

I have tested it by inserting some files of 10MB into database and Backup & Restore it with MySqlBackup.NET 1.5.5. It does the job. The exported SQL file is about 90MB in size.

One more reminder, if the user used in the connection does not has the privilege to modify Global Variables (i.e. max_allowed_packet), MySqlBackup 1.5.5 will still raise the exception of packets larger than max_allowed_packet are not allowed.

Good luck.

Developer
Dec 11, 2012 at 6:19 AM

I'm sorry for that, anyway i'll download the new version and test the demo . this is an improvement for this tool because even MySQL workbench cant import the whole database if it encounters tables that contains blob columns with rows larger than 1 Mb.

Thanks

Developer
Dec 11, 2012 at 6:26 AM

i just tested it and fyi the first time i run the demo to import my backup database there is an error. then  i closed the app and run the import again and its ok.

Coordinator
Dec 12, 2012 at 3:34 AM

You're welcome.