Home › Forums › implementation › Implementation discussion ›

Mysqldump max_allowed_packet

Submitted by Guest on Wed, 15/07/2015 - 07:21
in

I have just done a 1.7 to 1.8 upgrade on a system.

Prior to doing the upgrade I used mysqldump to take a backup of the database.  This failed part way through as follows:

The mysql ini file has max_allowed_packet=64M

The documents file has got some big stuff in it as follows:

Googling I found http://stackoverflow.com/questions/8815445/mysqldump-error-got-packet-bi...

This reveals that the problem can be avoided by specifying a bigger max_allowed_packet size either to mysqldump or in the mysqldump section of the mysql.ini file

Has anyone seen this problem before?

[I must admit that I thought we has some big documents (eg 5MB) but we don't have anything like those above.]

Regards, Tim G

‹ Document content blob format? Invoice Items Weight ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Mysqldump max_allowed_packet

Submitted by tanderson on Wed, 15/07/2015 - 10:49.

The doc_size column represents the uncompressed size of the document.

Try running:

select length(contents)
from documents
order by length(contents) desc limit 20;

to get the stored size of the documents.

According to http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysv... the max_allowed_packet in the .ini only applies to the server. The mysqldump tool doesn't use it, defaulting to 24MB.

Re: Mysqldump max_allowed_packet

Submitted by tgething (not verified) on Wed, 15/07/2015 - 11:34.

Interesting - your query timed out after 10 minutes so what I did was to rerun mine to pull the top 5 doc_size's and their doc_id's and then ran a second query to select the length of the the one with the max doc_size (the MPG movie) - its length was slightly shorter at 42335076 (vs doc_size of 42825732).

So mysqldump needs a max_allowed_packet of some 50MB.

I will give it a whirl early tomorrow morning during the night shift.

Regards, Tim G

Re: Mysqldump max_allowed_packet

Submitted by Ben_Charlton on Wed, 15/07/2015 - 11:40.

I had this same thing happen with our upgrade Tim - I did exactly as you suggested keep in mind that you need to change both the dump params and the mysql params because if you ever try to restore that backup you need to be able to insert the large packets.

Regards
 
Ben 
OpenVPMS Installer and Helper 
Ph: +61423044823 
Email: info[at]charltonit.com[dot]au

Re: Mysqldump max_allowed_packet

Submitted by tgething (not verified) on Wed, 15/07/2015 - 11:50.

Thanks Ben. MySQL already runs with max_allowed_packet set to 64MB so I will make a note for their systems administrator that mysqldump needs to be run with the same setting.

Regards, Tim G

Syndicate content