send_reminder causes database error
When running the send_reminder job on CiviCRM 4.7.31 on MariaDB 10.1.26-0+deb9u1 using:
cli.php -s my.3ca.org.uk -u admin -p*** -e Job -a send_reminder
It fails with:
DB Error: syntax error
Died during callApi
The full error log reads:
Mar 30 07:26:59 [info] $Fatal Error Details = Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => exceptionHandler
)
[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] => INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
SELECT e.contact_id as contact_id, e.id as entity_id, "civicrm_participant" as entity_table, 21 as action_schedule_id
FROM civicrm_participant e
INNER JOIN civicrm_event_en_GB r ON e.event_id = r.id
INNER JOIN civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0
LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = e.contact_id AND
reminder.entity_id = e.id AND
reminder.entity_table = 'civicrm_participant' AND
reminder.action_schedule_id = 21
WHERE (r.id IN ("84")) AND (r.is_active = 1) AND (r.is_template = 0) AND (e.status_id IN (1)) AND (reminder.id IS NULL) AND ('20180330072659' >= DATE_SUB(r.start_date, INTERVAL hour)) AND (DATE_SUB(20180330072659, INTERVAL 1 DAY ) <= r.start_date)
[nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) AND (DATE_SUB(20180330072659, INTERVAL 1 DAY ) <= r.start_date)' at line 10]
[type] => DB_Error
[user_info] => INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
SELECT e.contact_id as contact_id, e.id as entity_id, "civicrm_participant" as entity_table, 21 as action_schedule_id
FROM civicrm_participant e
INNER JOIN civicrm_event_en_GB r ON e.event_id = r.id
INNER JOIN civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0
LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = e.contact_id AND
reminder.entity_id = e.id AND
reminder.entity_table = 'civicrm_participant' AND
reminder.action_schedule_id = 21
WHERE (r.id IN ("84")) AND (r.is_active = 1) AND (r.is_template = 0) AND (e.status_id IN (1)) AND (reminder.id IS NULL) AND ('20180330072659' >= DATE_SUB(r.start_date, INTERVAL hour)) AND (DATE_SUB(20180330072659, INTERVAL 1 DAY ) <= r.start_date)
[nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) AND (DATE_SUB(20180330072659, INTERVAL 1 DAY ) <= r.start_date)' at line 10]
[to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
SELECT e.contact_id as contact_id, e.id as entity_id, "civicrm_participant" as entity_table, 21 as action_schedule_id
FROM civicrm_participant e
INNER JOIN civicrm_event_en_GB r ON e.event_id = r.id
INNER JOIN civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0
LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = e.contact_id AND
reminder.entity_id = e.id AND
reminder.entity_table = 'civicrm_participant' AND
reminder.action_schedule_id = 21
WHERE (r.id IN ("84")) AND (r.is_active = 1) AND (r.is_template = 0) AND (e.status_id IN (1)) AND (reminder.id IS NULL) AND ('20180330072659' >= DATE_SUB(r.start_date, INTERVAL hour)) AND (DATE_SUB(20180330072659, INTERVAL 1 DAY ) <= r.start_date)
[nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) AND (DATE_SUB(20180330072659, INTERVAL 1 DAY ) <= r.start_date)' at line 10]"]
)
Mar 30 07:26:59 [info] $backTrace = #0 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(948): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: syntax error", -2, 16, (Array:2), "INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...")
#3 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-2, 16, (Array:2), "INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...")
#4 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -2, 16, (Array:2), "INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...", "DB_Error", TRUE)
#5 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#6 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-2, NULL, NULL, "INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...", "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...")
#9 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2446): DB_common->query("INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...")
#10 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1635): DB_DataObject->_query("INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...")
#11 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(392): DB_DataObject->query("INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...")
#12 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1348): CRM_Core_DAO->query("INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...", TRUE)
#13 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/Civi/ActionSchedule/RecipientBuilder.php(215): CRM_Core_DAO::executeQuery("INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_s...")
#14 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/Civi/ActionSchedule/RecipientBuilder.php(156): Civi\ActionSchedule\RecipientBuilder->buildRelFirstPass()
#15 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/CRM/Core/BAO/ActionSchedule.php(360): Civi\ActionSchedule\RecipientBuilder->build()
#16 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/CRM/Core/BAO/ActionSchedule.php(375): CRM_Core_BAO_ActionSchedule::buildRecipientContacts(3, "20180330072659", (Array:3))
#17 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/api/v3/Job.php(186): CRM_Core_BAO_ActionSchedule::processQueue("20180330072659", (Array:3))
#18 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_job_send_reminder((Array:3))
#19 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/Civi/API/Kernel.php(169): Civi\API\Provider\MagicFunctionProvider->invoke((Array:9))
#20 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/Civi/API/Kernel.php(100): Civi\API\Kernel->runRequest((Array:9))
#21 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/api/api.php(23): Civi\API\Kernel->runSafe("Job", "send_reminder", (Array:2), NULL)
#22 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/bin/cli.class.php(113): civicrm_api("Job", "send_reminder", (Array:2))
#23 /var/www/my.3ca.org.uk/web/wp-content/plugins/civicrm/civicrm/bin/cli.php(32): civicrm_cli->callApi()
#24 {main}