SQL Error when Processing Bounces

Appletech · September 9, 2017, 1:25pm

Hello,

I have posted bug https://mantis.phplist.org/view.php?id=18848 in the bug tracker for this issue, but I thought I should post about it here too.

I am setting up a new system, and I have configured processbounces to run as a cron job, using:

phplistusr /usr/bin/php /var/webs/campaign/www/admin/index.php -pprocessbounces -c/var/webs/campaign/www/config/config.php

I sent a test campaign to an experimental subscriber list with 6 members, one of which is fake, so as to test bounces.

When the cron job ran, I received the following cron job report:

phpList version 3.3.1 (c) 2000-2017 phpList Ltd, http://www.phplist.com [^]
PHPlist - bounces to fetch from the mailbox
PHPlist - Please do not interrupt this process
PHPlist - UID0 MSGID0
PHPlist - Database error 1406 while doing query Data too long for column 'data' at row 1
PHPlist - Sql error insert into phplist_bounce (date,header,data)
    values("2017-09-06 15:24","Return-Path:
Delivered-To: noabounce2@noa.gr
Received: from vmail2.noa.gr
    by vmail2.noa.gr with LMTP id ODL2K+vor1n5BQAAcV+qjQ
    for ; Wed, 06 Sep 2017 15:24:11 +0300
Received: from campaign.noa.gr (vweb2.noa.gr [IPv6:2001:648:2011:15::164])
    (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits))
    (No client certificate requested)
    by vmail2.noa.gr (IC-XC-NI-KA) with ESMTPSA id 3049080004F2F
    for ; Wed, 6 Sep 2017 15:24:11 +0300 (EEST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=noa.gr; s=default;
    t=1504700651; bh=ciq4wSn3COUw0dY4S7zz7BDnc6PbTD3uAQ3+lr06lBY=;
    h=Date:To:From:Subject:List-Unsubscribe;
    b=RdratLrhK51wzfrf16NU8/XxbBKRpPEo7AciMqMnA2zf4a/+V6GIphLpHaQyZWCFo
     5TOD9gnRPJmNe+qLYaC6iBUO2AatyUZtYjc9A5M4VDk73KD/Qz1hiIyEGC029SRU2L
     TkCeWPdSNq4UjHymUUiXoinZlLf+Zs7p9VLWNqUc=
Date: Wed, 6 Sep 2017 15:24:11 +0300
To: noabounce2@noa.gr
From: PHPList Master
Subject: PHPlist Message queue processing report
Message-ID:
X-Mailer: PHPMailer 5.2.22 (https://github.com/PHPMailer/PHPMailer [^])
X-phpList-version: 3.3.1
X-MessageID: systemmessage
X-ListMember: noabounce2@noa.gr
Precedence: bulk
Bounces-To: listsender2@noa.gr
List-Unsubscribe:
MIME-Version: 1.0
Content-Type: multipart/alternative;
    boundary=\"b1_4cba289a9bf61c9523cd9c91d4846634\"

","This is a multi-part message in MIME format.

--b1_4cba289a9bf61c9523cd9c91d4846634
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

The following events occured while processing the message queue:

[Wed 6 Sep 2017 15:24] [CL] Started
[Wed 6 Sep 2017 15:24] [CL] Sending in batches of 200 emails
[Wed 6 Sep 2017 15:24] [CL] select id from phplist_message where status not
in (\"draft\", \"sent\", \"prepared\", \"suspended\") and embargo=20


--b1_4cba289a9bf61c9523cd9c91d4846634
Content-Type: multipart/related;
    boundary=\"b2_4cba289a9bf61c9523cd9c91d4846634\"

--b2_4cba289a9bf61c9523cd9c91d4846634
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable




 
=20




The following events occured while processing the message queue:=0A=0A[Wed =
6 Sep 2017 15:24] [CL] Started=0A[Wed 6 Sep 2017 15:24] [CL] Sending in bat=
ches of 200 emails=0A[Wed 6 Sep 2017 15:24] [CL] select id from phplist_me=
ssage where status not in (\"draft\", \"sent\", \"prepared\", \"suspended\") and em=
bargo
PHPlist - Deleting message 1
PHPlist - UID0 MSGID0
PHPlist - Deleting message 2
PHPlist - UID0 MSGID0
PHPlist - Deleting message 3
PHPlist - UID0 MSGID0
PHPlist - Deleting message 4
PHPlist - Closing mailbox, and purging messages
PHPlist - reprocessing
PHPlist - 3 bounces to reprocess
PHPlist - 3 out of 3 processed
PHPlist - 0 bounces were re-processed and 0 bounces were re-identified
PHPlist - Identifying consecutive bounces
PHPlist - Nothing to do
PHPlist - total of 0 subscribers processed

Can you please advise me on whether this is a bug (as I think) and what I can do about it?

What does the indicated error (“Database error 1406 while doing query Data too long for column ‘data’ at row 1”) mean?

Thanks a lot,
Nick

splaquet · January 16, 2018, 4:44pm

I’m having the same error on my end. Did you find a resolution for the error:
“Database error 1406 while doing query Data too long for column ‘data’ at row 1”?

luison · July 13, 2018, 7:30pm

Same here:

phpList version 3.3.3-RC2 © 2000-2018 phpList Ltd, https://www.phplist.comPHPlist-GIS - 23 bounces to fetch from the mailbox
PHPlist-GIS - Please do not interrupt this process
PHPlist-GIS - Database error 1406 while doing query Data too long for column ‘data’ at row 1
PHPlist-GIS - Sql error insert into phplist_bounce (date,header,data)

splaquet · July 13, 2018, 10:52pm

funny… i came across this error again today on an install. i ended up at this page, but saw that it was still the original question and mine. i went to close the tab and then saw your reply! …so random.

i cannot recall how i fixed it last time, but i ‘feel’ that it might have had something to do with the table not being utf8_general_ci… and taking it apart and putting it back together. it was too many years back for me to remember though

luison · July 14, 2018, 8:16am

There are quite a fee of those questions arround here
Thanks for the feedback. The process actually seems to follow on, so no major issue. I just dislike touching database definitions thinking of future updates issues.

Dragonrider · July 14, 2018, 8:54am

If this is indeed the cause and solution, the way forward is to go to your Cpanel (NOT phpList admin pages) and access the phpMyAdmin, locate the database your phpList is using, Export all database tables, for safety.

Next, in the left hand side of the screen, select the database you wish to work on.
Then, on the right hand side of the screen, select Operations tab.
Now, find the Collation section. Using the dropdown menu under Collation, find utf8_general_ci, select this. Now click Go.

This will change your database from a mixture of formats (mine included 6 x latin1_swedish_ci) to uti8_general_ci

Hopefully this will correct the issue.

splaquet · July 15, 2018, 3:43am

I had already made sure that the database was all correct, using utf8_general_ci. It was still throwing errors on me.

After reading a few other articles, I changed the “data” column from blob to blobmedium. That seemed to do the trick!

Dragonrider · July 15, 2018, 6:20am

Glad to read you have sorted it, also many thanks for posting your solution, it may help someone else in the future.

luison · July 16, 2018, 9:04am

Thanks. Done, seems to work. Our BBDD was already UTF8. I’ve written it donw in Mantis hoping it will get somewhere there! Thanks.
https://mantis.phplist.org/view.php?id=19324

luison · July 18, 2018, 4:57pm

For future references, seems to be included on next release:
https://mantis.phplist.org/view.php?id=19324

samtuke · July 19, 2018, 4:16pm

@luison That’s true, thanks to your report it was included at the last minute by @xheni.

luison · July 19, 2018, 5:08pm

Glad to be of help, credit goes to @splaquet. Maybe on a future release @xheni can also include Error when creating advance bounces rules
Thanks.

xheni · July 19, 2018, 5:37pm

@luison There is already an open Pull Request for that

splaquet · July 20, 2018, 4:05pm

thanks @luison, but i actually can’t take credit for it. i stumbled upon that (or saw a clue implying that) elsewhere. …just can’t remember where or when. hah

so… sorry to be a downer, but i’m not sure if that ended up working for me. i have a super old base install, so i’m not sure if that’s the root of my issue.

i dumped out (mysqldump) the old DB and imported it back in (it’s 1.2gb )
i converted blob to blobmedium (which appeared to initially work for me)

…but am still seeing that error line pop up. sometimes, it’s even showing twice.
– “Database error 1406 while doing query Data too long for column ‘data’ at row 1”

is there any specific reason why blob is being used? i don’t know enough about MySQL to know that answer, but i can’t help but wonder if the issue would go away if the table was converted.

i figured, “well hey… why not simply remove row 1 from the spreadsheet?” …but, I believe that might be what caused the line to show twice.

i’m also randomly seeing “the mysql database has gone away”, which is kind of new. i have a feeling that might be attributed to CloudLinux’s CageFS system. it was set to kill off slow queries that took longer than 30 seconds to execute. i added an exception for the account and also raised the timeout to 60, but i haven’t seen the results of everything yet.

i cleaned up some of my tables, as they were nearly 2gb in size, and that appears to have fixed things up a little bit. everything is running faster now (bounce processing wise), but the error is still showing up.

splaquet · July 20, 2018, 4:13pm

randomly enough… i received this error, just after i submitted my last reply:

Database error 2006 while doing query MySQL server has gone away
Database error 2006 while doing query MySQL server has gone away
Database error 2006 while doing query MySQL server has gone away
Database error 2006 while doing query MySQL server has gone away
Database error 2006 while doing query MySQL server has gone away
Database error 2006 while doing query MySQL server has gone away
.... and on and on and on and on

i looked into the MySQL error log and saw this error line:

host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)

it was set to 32M. I’m going to change:
[mysql]
max_allowed_packet = 32M
[mysqld]
max_allowed_packet = 64M

keeenone · August 27, 2018, 3:22pm

After switching from PHP 7.0.x to PHP 7.1.x I am now seeing many of these errors when I process bounces:

Database error 1406 while doing query Data too long for column ‘data’ at row 1

and this one time error:

Database error 1366 while doing query Incorrect string value: ‘\xE9 ause…’ for column ‘header’ at row 1

My tables are all utf8_general_ci, however, for some reason, the database itself is latin1_swedish_ci. I am not sure if this could cause the issues, but would appreciate any input here. My max_allowed_packet = 256M so I doubt that is the issue.