DirectAdmin backup: Got packet bigger than 'max_allowed_packet'

[Harry Tang]

Hi,

I config my server to run daily backup, recently I get this error:

mysqldump error output: mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table...

I increased "max_allowed_packet" even to 1G but does not work.

Any help ?

 

[Richard G]

I Googled it for you and found a similar issue on another site, also with increasing the packet size without solution.
Maybe it can be of help to you:

I had a similar error and would fail with packet size 512M on row 0. It was an innodb table that was apparently damaged (mysqlcheck showed OK). I ended up re-creating the table and then it worked fine with a small packet size of just 128M.

Where or how did you increase the size to 1G?

 

[Harry Tang]

Hi,

My server use MariaDB and I edit this file /etc/my.cnf.d/server.cnf

I also check it by run those sql commands

SHOW VARIABLES LIKE 'max_allowed_packet';

and

SELECT @@global.max_allowed_packet

both show 1G in result.

 

[Richard G]

Ah oke, you probably don't have Centos 6 then.
Looks good.
Maybe recreating the table like the post said can fix it then?
Can't imagine a table being that big.

 

[Harry Tang]

Hi,

Finally I know what the problem is. I set max_allowed_packet in [mysqldump] section, not in [mysqld] and now it works fine.

 

[Richard G]

Ah I thought you had that in /etc/my.cnf.d/server.cnf set under mysqld.

Anyway, good you've fixed it!

 

[baranya]

My website is mysql problem
And I can not backup my database

http://moayyeri.ir/