[fix] for MySQL error 1153 -- 'max_allowed_packet' exceeded

Solutions for other phpList version 2 users
Forum rules
Please do not ask questions here, this is for Solutions you have discovered or come across.

[fix] for MySQL error 1153 -- 'max_allowed_packet' exceeded

Postby thdyck » 3:18pm, Thu 21 Feb, 2008

Hi all,

When processing bounces, I was getting a database error of:

Database error 1153 while doing query Got a packet bigger than 'max_allowed_packet' bytes


for every bounce in my queue and the bounce was being retrieved but not stored in the database (that is, lost).

Our list sends out attachments with e-mails and so the bounces are 3-5 MB in size.

We are using MySQL 5.0.45 in a shared hosting environment. The documented solution is to change the max_allowed_packet server parameter to allow for larger inserts (the default limits SQL commands to 1048576 bytes). Because I do not control the server, I cannot change the server settings.

The MySQL documentation says that clients can also set this dynamically by issuing a "set session" command. e.g.

Code: Select all
  $increase = mysql_query("set session max_allowed_packet=10000000");
  if ($increased!=FALSE) echo "Can not increase packet size";


However, I did this right before the insert into the bounce table that causes the problem and it didn't prevent the error. I also read the value back to ensure it had been changed and the session setting value had indeed been changed, but the solution still did not work. Some others have posted messages on MySQL forums saying the same. Changing the server parameters in other ways does appear to work, if you can do that.

So, as another approach, I edited the code inserting bounced messages into the database to limit the message body to 1000000 bytes in size, which avoids the issue entirely. They still process correctly as all the header information is still included.

In processbounces.php:

Code: Select all
  ...

  Sql_Query(sprintf('insert into %s (date,header,data)
    values("%s","%s","%s")',
    $tables["bounce"],
    date("Y-m-d H:i",@strtotime($headerinfo->date)),
    addslashes($header),
-    addslashes($body)));
+    addslashes(substr($body,0,1000000)))); # limit body of bounced message to 1 million bytes to avoid a MySQL max_allowed_packet limit

    ...


This fixes the problem for me. Hope this helps someone else.

Regards,
Tim Miller Dyck
Editor/Publisher
Canadian Mennonite
thdyck
phpList newbie
 
Posts: 4
Joined: 5:30am, Sun 06 Jan, 2008

Postby H2B2 » 8:45pm, Thu 21 Feb, 2008

Thanks for posting this! Should be quite useful info for the development team.
H2B2
Moderator
 
Posts: 7188
Joined: 1:51am, Wed 15 Mar, 2006

Re: [fix] for MySQL error 1153 -- 'max_allowed_packet' exceeded

Postby stanman » 8:22am, Tue 16 Nov, 2010

In processbounces.php:

Code: Select all
  ...

  Sql_Query(sprintf('insert into %s (date,header,data)
    values("%s","%s","%s")',
    $tables["bounce"],
    date("Y-m-d H:i",@strtotime($headerinfo->date)),
    addslashes($header),
-    addslashes($body)));
+    addslashes(substr($body,0,1000000)))); # limit body of bounced message to 1 million bytes to avoid a MySQL max_allowed_packet limit

    ...


This fixes the problem for me. Hope this helps someone else.

Regards,
Tim Miller Dyck
Editor/Publisher
Canadian Mennonite


Tim, thank you for this. I was wondering if the "-" (minus) and the "+" (plus) means, "remove this line and add this one."?? Or do I just copy and paste the entire thing over the old code? Sorry, a bunch of non programmers in here but I really want to use your fix here.

Thanks!
Stanman
stanman
phpList newbie
 
Posts: 2
Joined: 10:38pm, Wed 13 Oct, 2010

Re: [fix] for MySQL error 1153 -- 'max_allowed_packet' exceeded

Postby Dragonrider » 12:40pm, Tue 16 Nov, 2010

stanman wrote:
In processbounces.php:

Code: Select all
  ...

  Sql_Query(sprintf('insert into %s (date,header,data)
    values("%s","%s","%s")',
    $tables["bounce"],
    date("Y-m-d H:i",@strtotime($headerinfo->date)),
    addslashes($header),
-    addslashes($body)));
+    addslashes(substr($body,0,1000000)))); # limit body of bounced message to 1 million bytes to avoid a MySQL max_allowed_packet limit

    ...


This fixes the problem for me. Hope this helps someone else.

Regards,
Tim Miller Dyck
Editor/Publisher
Canadian Mennonite


Tim, thank you for this. I was wondering if the "-" (minus) and the "+" (plus) means, "remove this line and add this one."?? Or do I just copy and paste the entire thing over the old code? Sorry, a bunch of non programmers in here but I really want to use your fix here.

Thanks!
Stanman

Think you have it right Stanman, replace the first line (-) with the second (+).
My sites:- http://wharfedalefestival.co.uk, http://ilkleygardeners.org.uk, http://emergencyaid.net, http://dragonrider.co.uk
Latest phpList version is now 3.0.12 (3 February 2015) and requires a minimum of MySQL 5.0 and PHP 5.3.x
Dragonrider
Moderator
 
Posts: 3460
Joined: 6:58am, Sun 02 Jul, 2006
Location: Ilkley, West Yorkshire, United Kingdom


Return to Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 0 guests