View Issue Details

IDProjectCategoryView StatusLast Update
0015394phplist applicationMessage Send Processpublic20-01-10 14:47
Reporteradrian15 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Product VersionFutureDevelopments 
Target VersionFixed in Version4.0.x 
Summary0015394: processqueue database 1064 error near interval
DescriptionWe are using phplist svn revision 1703.

Whenever we try to process message queue (processqueue.php) we get the following error:

Database error 1064 while doing query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval) as newembargo , current_timestamp + cast(repeatinterval || ' minute' ' at line 1

However the message is sent succesfully.
Additional InformationI suppose the message has something to do with these lines of code at connect.php:
  $query
  = 'select *'
  . ' , embargo + cast(repeatinterval || \' minute\' as interval) as newembargo'
  . ' , current_timestamp + cast(repeatinterval || \' minute\' as interval) as newembargo2'
  . ' , current_timestamp < embargo + cast(repeatinterval || \'minute\' as interval) as isfuture'
  . ' from %s'
  . ' where id = ?'
  . ' and current_timestamp < repeatuntil';
  $query = sprintf($query, $GLOBALS['tables']['message']);
  $rs = Sql_Query_Params($query, array($msgid));
  $msgdata = Sql_Fetch_Array($rs);

PHP 5.2.4-2ubuntu5.9 with Suhosin-Patch 0.9.6.2 (cli) (built: Nov 26 2009 14:00:44)

mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
TagsNo tags attached.

Activities

20-01-10 09:09

 

newembargo_error_fixed_some_innodb_reverted.diff (1,761 bytes)
diff -urN svn_r1703_sin_punto_svn_cumulus_stage_7_utf8_in_subject_and_others_send_page/phplist/public_html/lists/admin/connect.php svn_r1703_sin_punto_svn_cumulus_stage_8_newembargo_error/phplist/public_html/lists/admin/connect.php
--- svn_r1703_sin_punto_svn_cumulus_stage_7_utf8_in_subject_and_others_send_page/phplist/public_html/lists/admin/connect.php	2009-11-29 16:51:22.000000000 +0100
+++ svn_r1703_sin_punto_svn_cumulus_stage_8_newembargo_error/phplist/public_html/lists/admin/connect.php	2010-01-19 18:02:35.000000000 +0100
@@ -930,17 +930,10 @@
   # get the future embargo, either "repeat" minutes after the old embargo
   # or "repeat" after this very moment to make sure that we're not sending the
   # message every time running the queue when there's no embargo set.
-  $query
-  = 'select *'
-  . '  , embargo + cast(repeatinterval || \' minute\' as interval) as newembargo'
-  . '  , current_timestamp + cast(repeatinterval || \' minute\' as interval) as newembargo2'
-  . '  , current_timestamp < embargo + cast(repeatinterval || \'minute\' as interval) as isfuture'
-  . ' from %s'
-  . ' where id = ?'
-  . '   and current_timestamp < repeatuntil';
-  $query = sprintf($query, $GLOBALS['tables']['message']);
-  $rs = Sql_Query_Params($query, array($msgid));
-  $msgdata = Sql_Fetch_Array($rs);
+$msgdata = Sql_Fetch_Array_Query(
+    sprintf('select *,date_add(embargo,interval repeatinterval minute) as newembargo,
+      date_add(now(),interval repeatinterval minute) as newembargo2, date_add(embargo,interval repeatinterval minute) > now() as isfuture
+      from %s where id = %d and repeatuntil > now()',$GLOBALS["tables"]["message"],$msgid));
   if (!$msgdata["id"] || !$msgdata["repeatinterval"]) return;
 
   # copy the new message

adrian15

20-01-10 09:10

reporter   ~0050818

I have attached a patch that reverses the conflictive sentence to its former mysql form.

If there has to be a rewritten to innodb I suppose it should be somewhat compatible with mysql syntax (as it was not here).

I hope that the new innodb implementation (branch) solves these kind of issues.

adrian15

michiel

20-01-10 14:47

manager   ~0050824

revision 1734

you mean adodb :-) not innodb, but yes, many adodb changes have broken the code, and things like this should get it back in shape. Thanks