User Comments

2006: MySQL server has gone away

Another way that this problem can manifest itself is when trying to load data greater than the prescribed size (say a 1.5MB file) and you get the error shown above. I can confirm that increasing the value in the my.ini file (Windows ME, mySQL 3.23.38) as described corrects the problem.

I had the same problem. I use mysqldump to create large .sql backups of my databases. Recently when I tried to restore one of these backups using the "mysql" command, I got this error:

"ERROR 1153 at line 42: Got a packet bigger than 'max_allowed_packet'"

It turned out that the SQL "INSERT" statements created by mysqldump were exceeding the "max_allowed_packet" limit on the target server. Since I didn't have administrative permissions, my initial solution was to write a Perl script that split up the SQL commands into smaller chunks.

Later, though, I realized that mysqldump also has a "--set-variable" option. If you use this to set a "max_allowed_packet" size matching the target server, then mysqldump will generate smaller commands. So the recommended solution I guess is to import the oversized SQL dump to a local server (where you can set a large "max_allowed_packet"), and then re-export it using mysqldump with the correct settings.

However, I fail to understand why the MySQL protocol cannot handle this gracefully, e.g. by automatically breaking up the "packets". End users should not have to waste their time learning about and accomodating low-level protocol issues.

"ERROR 1153 at line 42: Got a packet bigger than 'max_allowed_packet'"

I have also had this problem. What a pain.

The easiest way around the problem is to to open the dump file and use find & replace (See below). This reduced the packet size down small enough to over come the server restrictions.

Find: ),

Replace with: ); INSERT INTO ` phpbb_posts_text` VALUES

I used Dreamweaver MX to do the editing.

HTH

Gareth

On the positive side, although this "error" is a bit of a pain, it actually makes MySQL more refractory to buffer overrun attacks. With the increasing popularity of MySQL, security attacks against MySQL servers are likely to become much more common in the future, so we may need to learn to live with minor restrictions like these. :-)

I had this problem and apparently it applies to a single insert statement with many small records as well.

I should also point out that I was loading about 15,000 records as a subset of a 287K file and I would get it no matter what for the --max_allowed_packet setting, even if it were larger than the WHOLE file being loaded. I'm on version 4.0.18 - apparently it still doesn't accept > 32M, or at least it allows you to ask for a value and it silently declines to honor the request.

Actually if the description of the message were better, indicating that it didn't only apply to single blobs, then I may have saved a couple of hours.......

Had to edit file and put in duplicate insert header lines every couple thousand records.....

Is it me or should an database that generates a "dump" make it in a manner that would actually be loadable without me having to waste my time editing it ?

I didn't try changing the value on mysqld - that might work.....

If you're looking for various different dump formatting, just use phpmyadmin.

yeah. had a similar problem in the installation (from source) of 4.1.5-gamma

Stated clearly in the docs.
--------------------------------------------------------------------------------
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> cd /usr/local/mysql
shell> bin/mysql_install_db --user=mysql
--------------------------------------------------------------------------------
However, when i run the script it gives a message:
--------------------------------------------------------------------------------
[root@bfc1 mysql]# bin/mysql_install_db --user=mysql
Installing all prepared tables
Fill help tables
ERROR: 1153 Got a packet bigger than 'max_allowed_packet' bytes
040930 15:54:33 [ERROR] Aborting

040930 15:54:33 [NOTE] /usr/local/mysql/libexec/mysqld: Shutdown complete

WARNING: HELP FILES ARE NOT COMPLETELY INSTALLED!
The "HELP" command might not work properly
-------------------------------------------------------------------------------
apparently the value in the script bin/mysql_install_db
uses max_allowed_packet to 8mb

Clearly an installation bug.

yb.

I had the same problem with installing the 4.1.7 stable version as Yashesh Bhatia had! My solution was to copy support-files/my-huge.cnf to /etc/my.cnf.

There are more versions of my.cnf in support-files. These are: my-large.cnf, my-medium.cnf, my-small.cnf and my-innodb-heavy-4G.cnf. I did not test if these maybe worked also. My-medium.cnf seamed to be equal to the standard installed version.
So maybe my-large.cnf would have worked also.

Yashesh Bhatia stated:
"apparently the value in the script bin/mysql_install_db
uses max_allowed_packet to 8mb

Clearly an installation bug."

I have tried altering the standard max_allowed_packet value in scripts/mysql_install_db (which indeed is set to 8MB in the standard version) to several increasing values to no avail. I resolved the problem only after I copied support-files/my-huge.cnf to /etc/my.cnf.

The funny thing is that the value of max_allowed_packet in support-files/my-huge.cnf is still 1M. So it is proberly not the max_allowed_packet value which is the problem but 1 of the other values which did change in support-files/my-huge.cnf.

Yeah, I've been having the same problem with installing 4.1.7 from source too. I too tried incrementing the packet limit upwards (right up to 1GB) and it still say the packet was too large.

The solution is a simple one though thankfully.

In the my.cnf file, there is a line that reads:

net_buffer_length = 8K

It was on line 34 in the my.cnf file on my system. Simply remove that line and you will no longer have the error message. Whether or not this will cause problems elsewhere or not remains to be seen, as I've just this minute found this solution.

Hi,

The easiest way to solve this issue is on the slave system, log into mysql and issue the following statement:

SET GLOBAL max_allowed_packet=10000000;

Hi,

In Linux:

Copy the my-xxx.cnf file from /usr/share/mysql to /etc as my.cnf

xxx can be small, medium, large, huge ... depending on the requirement.

$ cp /usr/share/mysql/my-xxx.cnf /etc/my.cnf

In the my.cnf file, change the default
max_allowed_packet = 1M
to
max_allowed_packet = 16M

Save the file and restart MySQL server.

In Windows:

In the MySQL server installation directory,
in my.ini file, add the following line under [mysqld] in SERVER SECTION.

max_allowed_packet = 16M

Save the file and restart MySQL server.

Harsha.

Havent tried this code, but using a buffered approach sounds like a sensible solution to PHP handling of the MySQL issue when reading from a stream of uknown size. In reality, its not great because of the string based interface, but it should do the job.

function safeInsert($table, $column, $valueHandle, $idField){
mysql_query("insert into $table set $column='' ");
$lastID = mysql_insert_id();
while( $content = fread( $valueHandle, 65536 ) != feof ){
$value = mysql_escapechars($content);
mysql_query("update $table set $column=$column+'$value' where $idField=$lastId");
}
}

The only idea i have apart from that is a direct global setting using mysql_query() such as:

$increase = mysql_query("SET GLOBAL max_allowed_packet=10000000");
if ($increased!=FALSE) echo "Can not increase packet size";

If you're using replication, don't forget to update the max_allowed_packet variable on your slaves, as well. Failure to do so will cause the slaves to repeatedly request the same failed update over and over again in the case of a packet that exceed's the slave's lower maximum packet size.

According to http://dev.mysql.com/doc/internals/en/the-packet-header.html the largest possible packet should be only 16M. Please do not try to follow the example here of setting the packet size to 32M as I do not believe it will work.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

I'm running my local test server on a Windows machine and my remote on a unix server, for our new program we needed to use really large files, I wanted to see how MySQL 5.0 handled it, so I increased the packet size to 4GB:

max_allowed_packet=4000M

I did this becuase the largest LONGBLOB is (2^32 -1) or 4.295GB

It allows u to have files up too 1GB in size, but no larger, as per the article above :(

no errors under 1GB so far ;)

I wonder why the LONGBLOB is 4GB if there is no way to use 3GB of it??

Signed: 3Pc

I had to add max_allowed_packet=32M to my my.inf file in /etc

Using Version 5.0.22 on FC5 RH

Add your own comment.