phplist

NOTE:: Before reporting an issue, make sure you are running the latest version, currently 3.3.1


View Issue Details Jump to Notes ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0008478phplist applicationMessage Send Processpublic08-11-06 20:3621-05-12 20:25
Reporterorock 
PrioritynormalSeveritycrashReproducibilityalways
StatusresolvedResolutionfixed 
PlatformOSOS Version
Product Version2.10.3 
Target Version2.10.7Fixed in Version2.10.4 
Summary0008478: processqueue.php creeates bad query if $doneusers large
Descriptionwe were command line sending to a 60k+ list with batching set to 14k per hour. after first batch calls to processqueue produced:

Database error 0 while doing query
Lost connection to MySQL server during query

turning on VERBOSE revealed this query with an IN check against a 14,000 id list as the culprit:

select distinct user.id from
    phplist_listuser as listuser,
    phplist_user_user as user,
    phplist_listmessage as listmessage
    where
    listmessage.messageid = 31 and
    listmessage.listid = listuser.listid and
    user.id = listuser.userid and user.confirmed and !user.blacklisted and
    listuser.userid not in (14,15,16,17,18,19,...[truncated]..., 14xxx)

experimenting with this query directly in mysql (5.0.18) showed that it caused some sort of server reset and crashed parsing or processing this extremely long query. i determined this by the error message and repeatedly running "show processlist" and seeing it get an error after the query crashed that the server was unavailable. the server uptime also reset.

i rewrote this query using a LEFT JOIN effectively not including users in the usermessage table who had an entry for this messageid. this obviates the need for the long IN list. (this should also be employed for the exclude list processing in the future -- i guess its on my list since we will be exploring excludes for some criteria testing in the near future.)

SELECT DISTINCT user.id FROM
  (phplist_listuser AS listuser,
   phplist_user_user AS user,
   phplist_listmessage AS listmessage)
  LEFT JOIN phplist_usermessage AS usermessage
  ON (usermessage.messageid = 31 AND usermessage.userid = listuser.userid)
  WHERE
   listmessage.messageid = 31 AND
   listmessage.listid = listuser.listid AND
   user.id = listuser.userid AND
   user.confirmed AND
   !user.blacklisted AND
   usermessage.userid IS NULL
 
i incorporated this query into processqueue.php and it worked well on our run today - not without stress trying to get a time sensitive newsletter out the door on schedule.

i've included the diffs in the 'additional information' below and have uploaded my version of processqueue.php.
Additional Information> diff -w processqueue_orig.php processqueue.php
390a391
>
423a425
>
439,440c441,446
< if (sizeof($doneusers))
< $exclusion = " and listuser.userid not in (".join(",",$doneusers).")";
---
>
> ###
> ### OPR: 11.08.06 - don't use $doneusers - see query below
> ###
> ### if (sizeof($doneusers))
> ### $exclusion = " and listuser.userid not in (".join(",",$doneusers).")";
458,471c464,500
< $query = sprintf('select distinct user.id from
< %s as listuser,
< %s as user,
< %s as listmessage
< where
< listmessage.messageid = %d and
< listmessage.listid = listuser.listid and
< user.id = listuser.userid %s %s %s',
< $tables['listuser'],$tables["user"],$tables['listmessage'],
< $messageid,
< $userconfirmed,
< $exclusion,
< $user_attribute_query);
<
---
> ###
> ### OPR: 11.08.06 -- new query using LEFT JOIN instead of IN long list
> ###
> ### SELECT DISTINCT user.id FROM (phplist_listuser AS listuser, phplist_user_user AS user, phplist_listmessage AS listmessage) LEFT JOIN phplist_usermessage AS usermessage ON (usermessage.messageid=31 AND usermessage.userid=listuser.userid) WHERE listmessage.messageid =31 AND listmessage.listid = listuser.listid AND user.id = listuser.userid AND user.confirmed AND ! user.blacklisted AND usermessage.userid IS NULL;
>
> $query = sprintf('SELECT DISTINCT user.id FROM
> (%s AS listuser,
> %s AS user,
> %s AS listmessage)
> LEFT JOIN %s AS usermessage
> ON (usermessage.messageid = %d AND usermessage.userid = listuser.userid)
> WHERE
> listmessage.messageid = %d AND
> listmessage.listid = listuser.listid AND
> user.id = listuser.userid AND
> usermessage.userid IS NULL
> %s %s %s',
> $tables['listuser'], $tables['user'], $tables['listmessage'], $tables['usermessage'],
> $messageid, $messageid,
> $userconfirmed, $exclusion, $user_attribute_query);
>
> ###
> ### OPR: 11.08.06 -- old $query
> ###
> ###$query = sprintf('select distinct user.id from
> ### %s as listuser,
> ### %s as user,
> ### %s as listmessage
> ### where
> ### listmessage.messageid = %d and
> ### listmessage.listid = listuser.listid and
> ### user.id = listuser.userid %s %s %s',
> ### $tables['listuser'],$tables["user"],$tables['listmessage'],
> ### $messageid,
> ### $userconfirmed,
> ### $exclusion,
> ### $user_attribute_query);
TagsNo tags attached.
Attached Files? file icon processqueue.php [^] (34,578 bytes) 08-11-06 20:36

- Relationships Relation Graph ] Dependency Graph ]
related to 0002705closed PHPList v2.11 release 
related to 0003721closed phplist 2.10.x 
related to 0010058resolvedmichiel Many users not receiving email because of too-large SQL query in processqueue.php (with solution) 
related to 0011732closedmichiel Processqueue malfunctions in certain situations, with easy fix 

-  Notes
(0020815)
michiel (manager)
08-11-06 21:29

brilliant thanks
(0020816)
orock (reporter)
08-11-06 21:58

just glad we got it working ;)


Copyright © 2000 - 2017 MantisBT Team
Powered by Mantis Bugtracker