Mysql database copy fails with packet bigger than allowed

I started with RT-3.0.11 on a fedora FC2 box, upgraded through rt-3.2.2
and added RTFM-2.0.4. Then I moved this database to a Centos4 box
using mysqldump --opt rt3 >rt.sql on the old server and
mysql rt3 <rt.sql on the new one. Then I upgraded the new server
to 3.4.2, then 3.4.3 and RTFM 2.1-TESTING. All of that appeared to go
well and is working although I haven’t done much with RTFM yet.

My problem is, I am trying to repeat the process of moving the
database to yet another server and the file I now get from
mysql --opt rt3 on the working server gives an error:
“ERROR 1153 (08S01) at line 76: Got a packet bigger than
‘max_allowed_packet’ bytes”
when I try to recreate it on the new box with “mysql rt3 <file”.
At the moment I’m just trying to make a test version, but now I’m
concerned that I won’t be able to restore backups of the main
system unless I resolve this. I’ve tried adding:
max_allowed_packet = 1000M
to /etc/my.cnf without any change. Can something be wrong with
the database?

Les Mikesell
les@futuresource.com

Hello,
I had this a few days ago and retaliated by removing a word document
from the attachments table, which obviously is not the clean way to
proceed.

You should check for the biggest attachment in the attachments table
by the following statement:

select max(length(Content)) from Attachments;

This should give You the actual size of the largest attachment in the
database in bytes. Setting max_allowed_packet to something slightly
larger than this should help.

If this does not help, there might be a problem with the attachment
itself.

Regards,
Harald

I just ran into this problem yesterday. It is my understanding that this
problem occurs when mysqldump has a max_allowed_packet that is larger than
the mysqld’s max_allowed packet here’s a couple snippets from my my.cnf.

[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
log = /var/log/mysql/mysql.log
skip-locking
key_buffer = 256M
max_allowed_packet = 16M



[mysqldump]
quick
max_allowed_packet = 16M

I set the max_allowed_packet under the mysqld section to be equal to the
mysqldump section and everything worked like a champ. Good Luck.-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Harald
Wagener
Sent: Tuesday, August 30, 2005 9:15 AM
To: Les Mikesell
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] mysql database copy fails with packet bigger than
allowed

Hello,
I had this a few days ago and retaliated by removing a word document
from the attachments table, which obviously is not the clean way to
proceed.

You should check for the biggest attachment in the attachments table
by the following statement:

select max(length(Content)) from Attachments;

This should give You the actual size of the largest attachment in the
database in bytes. Setting max_allowed_packet to something slightly
larger than this should help.

If this does not help, there might be a problem with the attachment
itself.

Regards,
Harald
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Buy your copy of our new book, RT Essentials, today!

Download a free sample chapter from http://rtbook.bestpractical.com

I just ran into this problem yesterday. It is my understanding that this
problem occurs when mysqldump has a max_allowed_packet that is larger than
the mysqld’s max_allowed packet here’s a couple snippets from my my.cnf.
[mysqld]

Thanks - I was putting it in the [mysql.server] section of the my.cnf
included with Centos and it needs to be in [mysqld]. The defaults
seem to be different now and none of the example *.cnf files under
/usr/share/doc/mysql-server-4.1.10a seem to work - mysql won’t even
start if you try to use them.

Oddly, my biggest attachment was 800k which seems like it should
have worked with the default 1M setting. Could some other item
be bigger than 1M?

Les Mikesell
les@futuresource.com