0003416: Bounce testing not consecutive

(0005218)
michiel (administrator)
14-06-05 13:51

this may relate to your Mysql version. can you give the version you're using?

it's worked for a long time, so I can't imagine it broke just now, but maybe the mysql version (4.1 has had quite a few changes in date fields) might be a clue.

also, the consecutiveness is only tested for bounces that are more than 5 days old

and yes, the "bouncecount" field is informational only.

(0005223)
duncan (reporter)
14-06-05 14:17

Hi Michiel

Mysql is version 4.0.20

Phplist is V 2.8.12

I have only recently got complaints and noticed this behaviour

I think you are right - the query should pull all my messages withor without bounces but it doesn't. I get some of them but am a bit tired right now to work out which. Will come back to you
I think I also have an that the 5 day time out means the client is continually unsubbed if I send regular messages as they don't get counted as valid until after 5days - and I send messages every three days and process the bounces which ends up unconfirming them - it means maybe I need to only process bounces greater than 5 days - but this could be the date thing.

(0008434)
duncan (reporter)
05-11-05 22:22

Hi Michiel

Finally got the sql to do what I want

SELECT userid , messageid, message, bounce, entered
FROM `phplist_usermessage` , (select distinct user AS u from phplist_user_message_bounce) AS busers
LEFT JOIN phplist_user_message_bounce ON (messageid = message AND userid = user)
WHERE userid = busers.u
ORDER BY userid

This gives me a big table with all users who have had a bounce

I could also cut out all messages less than the date of say the threshold plus 10 as if they are not bouncing recently they are not important.

Still maybe its all better in 2.10 - but I get two emails sent so haven't played a lot with it yet.

I don't think the time thing mattered - maybe you have resolved this in 2.10 - haven't checked yet

(0008435)
duncan (reporter)
05-11-05 22:59

This is how I have adjusted processbounces.php
Line 340 - 348 - removed where clause and added it to join - works for me now
$msg_req = Sql_Query(sprintf('select * from
                %s left join %s on (%s.messageid = %s.message AND userid=user)
                where (date_add(entered,INTERVAL 5 day) < now())
                and userid = %d

                order by entered desc',
                $tables["usermessage"],$tables["user_message_bounce"],
                $tables["usermessage"],$tables["user_message_bounce"],
                $user[0]));

(0008436)
duncan (reporter)
05-11-05 23:22

Finally got it working for me as per below

The sql found me the consecutive bounces but the loop didn't care whether they were recent of 6 months ago. The below changes fixed it for me and sped everything up hugely. It looks like 2.10 is still the same so I would suggest looking at this as a change. I am using Mysql 4.1.11 Debian. Not sure how it works on earlier versions though

Cheers Duncan

  #$alive = 1;$removed = 0; DT 051105
  $alive = 1;$removed = 0; $msgokay=0;
        #while ($alive && !$removed && $bounce = Sql_Fetch_Array($msg_req)) { DT 051105
        while ($alive && !$removed && !$msgokay &&$bounce = Sql_Fetch_Array($msg_req)) {
    $alive = checkLock($process_id);
    if ($alive)
      keepLock($process_id);
    else
      ProcessError("Process Killed by other process");
                if (sprintf('%d',$bounce["bounce"]) == $bounce["bounce"]) {
                        $cnt++;
      if ($cnt >= $bounce_unsubscribe_treshold) {
        $removed = 1;
        output(sprintf('unsubscribing %d -> %d bounces',$user[0],$cnt));
        $userurl = PageLink2("user&id=$user[0]",$user[0]);
        logEvent("User $userurl has consecutive bounces ($cnt) over treshold, user marked unconfirmed");
        $emailreq = Sql_Fetch_Row_Query("select email from {$tables["user"]} where id = $user[0]");
        addUserHistory($emailreq[0],"Auto Unsubscribed","User auto unsubscribed for $cnt consecutive bounces");
        Sql_Query(sprintf('update %s set confirmed = 0 where id = %d',$tables["user"],$user[0]));
        $email_req = Sql_Fetch_Row_Query(sprintf('select email from %s where id = %d',$tables["user"],$user[0]));
        $unsubscribed_users .= $email_req[0] . " [$user[0]] ($cnt)\n";
      }
                } elseif ($bounce["bounce"] == "") {
                        #$cnt = 0; DT 051105
                        $cnt = 0;
                        $msgokay = 1; #DT 051105 - escaping loop if message received okay

(0008484)
duncan (reporter)
08-11-05 08:50

I have to say it now goes so much faster I will put it in cron now. Before it took forever (many minutes) to process all users, now less than a min

(0008818)
michiel (administrator)
22-11-05 01:14

I'm afraid your query in Note: 0008434 is Mysql 4.1 and up only. I wouldn't just yet want to create a dependency on 4.1, I'm using 4.0 myself mostly.

I'll try to look into the rest though, because I'm getting very long process bounces processes as well, so I'd like to make it faster. Thanks for all the effort to investigate this.

(0012683)
bevan (reporter)
17-04-06 04:41

Research on a related bug? http://www.phplist.com/forums/viewtopic.php?t=4568 [^]

Notes
(0005218) michiel (administrator) 14-06-05 13:51	this may relate to your Mysql version. can you give the version you're using? it's worked for a long time, so I can't imagine it broke just now, but maybe the mysql version (4.1 has had quite a few changes in date fields) might be a clue. also, the consecutiveness is only tested for bounces that are more than 5 days old and yes, the "bouncecount" field is informational only.

(0005223) duncan (reporter) 14-06-05 14:17	Hi Michiel Mysql is version 4.0.20 Phplist is V 2.8.12 I have only recently got complaints and noticed this behaviour I think you are right - the query should pull all my messages withor without bounces but it doesn't. I get some of them but am a bit tired right now to work out which. Will come back to you I think I also have an that the 5 day time out means the client is continually unsubbed if I send regular messages as they don't get counted as valid until after 5days - and I send messages every three days and process the bounces which ends up unconfirming them - it means maybe I need to only process bounces greater than 5 days - but this could be the date thing.

(0008434) duncan (reporter) 05-11-05 22:22	Hi Michiel Finally got the sql to do what I want SELECT userid , messageid, message, bounce, entered FROM `phplist_usermessage` , (select distinct user AS u from phplist_user_message_bounce) AS busers LEFT JOIN phplist_user_message_bounce ON (messageid = message AND userid = user) WHERE userid = busers.u ORDER BY userid This gives me a big table with all users who have had a bounce I could also cut out all messages less than the date of say the threshold plus 10 as if they are not bouncing recently they are not important. Still maybe its all better in 2.10 - but I get two emails sent so haven't played a lot with it yet. I don't think the time thing mattered - maybe you have resolved this in 2.10 - haven't checked yet

(0008435) duncan (reporter) 05-11-05 22:59	This is how I have adjusted processbounces.php Line 340 - 348 - removed where clause and added it to join - works for me now $msg_req = Sql_Query(sprintf('select * from %s left join %s on (%s.messageid = %s.message AND userid=user) where (date_add(entered,INTERVAL 5 day) < now()) and userid = %d order by entered desc', $tables["usermessage"],$tables["user_message_bounce"], $tables["usermessage"],$tables["user_message_bounce"], $user[0]));

(0008436) duncan (reporter) 05-11-05 23:22	Finally got it working for me as per below The sql found me the consecutive bounces but the loop didn't care whether they were recent of 6 months ago. The below changes fixed it for me and sped everything up hugely. It looks like 2.10 is still the same so I would suggest looking at this as a change. I am using Mysql 4.1.11 Debian. Not sure how it works on earlier versions though Cheers Duncan #$alive = 1;$removed = 0; DT 051105 $alive = 1;$removed = 0; $msgokay=0; #while ($alive && !$removed && $bounce = Sql_Fetch_Array($msg_req)) { DT 051105 while ($alive && !$removed && !$msgokay &&$bounce = Sql_Fetch_Array($msg_req)) { $alive = checkLock($process_id); if ($alive) keepLock($process_id); else ProcessError("Process Killed by other process"); if (sprintf('%d',$bounce["bounce"]) == $bounce["bounce"]) { $cnt++; if ($cnt >= $bounce_unsubscribe_treshold) { $removed = 1; output(sprintf('unsubscribing %d -> %d bounces',$user[0],$cnt)); $userurl = PageLink2("user&id=$user[0]",$user[0]); logEvent("User $userurl has consecutive bounces ($cnt) over treshold, user marked unconfirmed"); $emailreq = Sql_Fetch_Row_Query("select email from {$tables["user"]} where id = $user[0]"); addUserHistory($emailreq[0],"Auto Unsubscribed","User auto unsubscribed for $cnt consecutive bounces"); Sql_Query(sprintf('update %s set confirmed = 0 where id = %d',$tables["user"],$user[0])); $email_req = Sql_Fetch_Row_Query(sprintf('select email from %s where id = %d',$tables["user"],$user[0])); $unsubscribed_users .= $email_req[0] . " [$user[0]] ($cnt)\n"; } } elseif ($bounce["bounce"] == "") { #$cnt = 0; DT 051105 $cnt = 0; $msgokay = 1; #DT 051105 - escaping loop if message received okay

(0008484) duncan (reporter) 08-11-05 08:50	I have to say it now goes so much faster I will put it in cron now. Before it took forever (many minutes) to process all users, now less than a min

(0008818) michiel (administrator) 22-11-05 01:14	I'm afraid your query in Note: 0008434 is Mysql 4.1 and up only. I wouldn't just yet want to create a dependency on 4.1, I'm using 4.0 myself mostly. I'll try to look into the rest though, because I'm getting very long process bounces processes as well, so I'd like to make it faster. Thanks for all the effort to investigate this.

(0012683) bevan (reporter) 17-04-06 04:41	Research on a related bug? http://www.phplist.com/forums/viewtopic.php?t=4568 [^]