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