Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-19885

Scheduled Reminders: lack of default form values leads to SQL syntax error

    Details

    • Type: Bug
    • Status: Won't Do
    • Priority: Minor
    • Resolution: Won't Do
    • Affects Version/s: 4.7.16
    • Fix Version/s: None
    • Component/s: None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Contributed Code
    • Verified?:
      No

      Description

      It looks like CRM-19853 caused a regression by changing some fields to text input without defining default values. Specifically, the integer fields start_action_offset, repetition_frequency_interval, end_frequency_interval, which aren't required fields (and perhaps they should be).

      Leaving these blank leads to NULL values in the database, which then leads to SQL syntax errors, e.g.

      Jan 16 14:15:01  [info] $Fatal Error Details = Array
      (
          [callback] => Array
              (
                  [0] => CRM_Core_Error
                  [1] => exceptionHandler
              )
      
          [code] => -2
          [message] => DB Error: syntax error
          [mode] => 16
          [debug_info] => SELECT r.contact_id as contact_id, e.id as entity_id, "civicrm_activity" as entity_table, 2 as action_schedule_id, MAX(reminder.action_date_time) as latest_log_time
      FROM civicrm_activity e
      INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = 1
      INNER JOIN civicrm_contact c ON c.id = r.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0 
      INNER JOIN civicrm_action_log reminder ON reminder.contact_id = r.contact_id AND
      reminder.entity_id          = e.id AND
      reminder.entity_table       = 'civicrm_activity' AND
      reminder.action_schedule_id = 2
      WHERE (e.activity_type_id IN (1)) AND (e.status_id IN (1)) AND (e.is_current_revision = 1 AND e.is_deleted = 0) AND ("20170116141500" <= DATE_SUB(e.activity_date_time, INTERVAL  hour)) AND ('
      20170116141500' >= DATE_ADD(e.activity_date_time, INTERVAL 1 hour))
      GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table
      HAVING (TIMESTAMPDIFF(HOUR, latest_log_time, CAST(20170116141500 AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL  HOUR)))
       [nativecode=1064 ** 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 ')) AND ('20170116141500' >= DATE_AD
      D(e.activity_date_time, INTERVAL 1 hour))
      GRO' at line 9]
          [type] => DB_Error
          [user_info] => SELECT r.contact_id as contact_id, e.id as entity_id, "civicrm_activity" as entity_table, 2 as action_schedule_id, MAX(reminder.action_date_time) as latest_log_time
      FROM civicrm_activity e
      INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = 1
      INNER JOIN civicrm_contact c ON c.id = r.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0 
      INNER JOIN civicrm_action_log reminder ON reminder.contact_id = r.contact_id AND
      reminder.entity_id          = e.id AND
      reminder.entity_table       = 'civicrm_activity' AND
      reminder.action_schedule_id = 2
      WHERE (e.activity_type_id IN (1)) AND (e.status_id IN (1)) AND (e.is_current_revision = 1 AND e.is_deleted = 0) AND ("20170116141500" <= DATE_SUB(e.activity_date_time, INTERVAL  hour)) AND ('
      20170116141500' >= DATE_ADD(e.activity_date_time, INTERVAL 1 hour))
      GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table
      HAVING (TIMESTAMPDIFF(HOUR, latest_log_time, CAST(20170116141500 AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL  HOUR)))
       [nativecode=1064 ** 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 ')) AND ('20170116141500' >= DATE_AD
      D(e.activity_date_time, INTERVAL 1 hour))
      GRO' at line 9]
          [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT r.contact_id as contact_id, e.id as entit
      y_id, "civicrm_activity" as entity_table, 2 as action_schedule_id, MAX(reminder.action_date_time) as latest_log_time
      FROM civicrm_activity e
      INNER JOIN civicrm_activity_contact r ON r.activity_id = e.id AND record_type_id = 1
      INNER JOIN civicrm_contact c ON c.id = r.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0 
      INNER JOIN civicrm_action_log reminder ON reminder.contact_id = r.contact_id AND
      reminder.entity_id          = e.id AND
      reminder.entity_table       = 'civicrm_activity' AND
      reminder.action_schedule_id = 2
      WHERE (e.activity_type_id IN (1)) AND (e.status_id IN (1)) AND (e.is_current_revision = 1 AND e.is_deleted = 0) AND ("20170116141500" <= DATE_SUB(e.activity_date_time, INTERVAL  hour)) AND ('
      20170116141500' >= DATE_ADD(e.activity_date_time, INTERVAL 1 hour))
      GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table
      HAVING (TIMESTAMPDIFF(HOUR, latest_log_time, CAST(20170116141500 AS datetime)) >= TIMESTAMPDIFF(HOUR, latest_log_time, DATE_ADD(latest_log_time, INTERVAL  HOUR)))
       [nativecode=1064 ** 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 ')) AND ('20170116141500' >= DATE_ADD(e.activity_date_time, INTERVAL 1 hour))
      GRO' at line 9]"]
      )
      
      
      Jan 16 14:15:01  [info] $backTrace = #0 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/Error.php(932): CRM_Core_Error::backtrace("backTrace", TRUE)
      #1 [internal function](): CRM_Core_Error::exceptionHandler(Object(DB_Error))
      #2 /var/www/d7cividev/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
      #3 /var/www/d7cividev/sites/all/modules/civicrm/packages/DB.php(976): PEAR_Error->PEAR_Error("DB Error: syntax error", -2, 16, (Array:2), "SELECT r.contact_id as contact_id, e.id as entity_id, \"civicrm_activity\" as...")
      #4 /var/www/d7cividev/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-2, 16, (Array:2), "SELECT r.contact_id as contact_id, e.id as entity_id, \"civicrm_activity\" as...")
      #5 /var/www/d7cividev/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -2, NULL, NULL, "SELECT r.contact_id as contact_id, e.id as entity_id, \"civicrm_activity\" as...", "DB_Error", TRUE)
      #6 /var/www/d7cividev/sites/all/modules/civicrm/packages/DB/mysql.php(895): DB_common->raiseError(-2, NULL, NULL, NULL, "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
      #7 /var/www/d7cividev/sites/all/modules/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
      #8 /var/www/d7cividev/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT r.contact_id as contact_id, e.id as entity_id, \"civicrm_activity\" as...")
      #9 /var/www/d7cividev/sites/all/modules/civicrm/packages/DB/DataObject.php(2435): DB_common->query("SELECT r.contact_id as contact_id, e.id as entity_id, \"civicrm_activity\" as...")
      #10 /var/www/d7cividev/sites/all/modules/civicrm/packages/DB/DataObject.php(1627): DB_DataObject->_query("SELECT r.contact_id as contact_id, e.id as entity_id, \"civicrm_activity\" as...")
      #11 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/DAO.php(354): DB_DataObject->query("SELECT r.contact_id as contact_id, e.id as entity_id, \"civicrm_activity\" as...")
      #12 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/DAO.php(1297): CRM_Core_DAO->query("SELECT r.contact_id as contact_id, e.id as entity_id, \"civicrm_activity\" as...", TRUE)
      #13 /var/www/d7cividev/sites/all/modules/civicrm/Civi/ActionSchedule/RecipientBuilder.php(295): CRM_Core_DAO::executeQuery("SELECT r.contact_id as contact_id, e.id as entity_id, \"civicrm_activity\" as...")
      #14 /var/www/d7cividev/sites/all/modules/civicrm/Civi/ActionSchedule/RecipientBuilder.php(163): Civi\ActionSchedule\RecipientBuilder->buildRelRepeatPass()
      #15 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/BAO/ActionSchedule.php(361): Civi\ActionSchedule\RecipientBuilder->build()
      #16 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/BAO/ActionSchedule.php(376): CRM_Core_BAO_ActionSchedule::buildRecipientContacts(1, "20170116141500", (Array:2))
      #17 /var/www/d7cividev/sites/all/modules/civicrm/api/v3/Job.php(186): CRM_Core_BAO_ActionSchedule::processQueue(NULL, (Array:2))
      #18 /var/www/d7cividev/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_job_send_reminder((Array:1))
      #19 /var/www/d7cividev/sites/all/modules/civicrm/Civi/API/Kernel.php(161): Civi\API\Provider\MagicFunctionProvider->invoke((Array:9))
      #20 /var/www/d7cividev/sites/all/modules/civicrm/Civi/API/Kernel.php(92): Civi\API\Kernel->runRequest((Array:7))
      #21 /var/www/d7cividev/sites/all/modules/civicrm/api/api.php(23): Civi\API\Kernel->runSafe("job", "send_reminder", (Array:1), NULL)
      #22 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/JobManager.php(134): civicrm_api("job", "send_reminder", (Array:1))
      #23 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/JobManager.php(113): CRM_Core_JobManager->executeJob(Object(CRM_Core_ScheduledJob))
      #24 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Admin/Page/Job.php(149): CRM_Core_JobManager->executeJobById("9")
      #25 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/Page/Basic.php(172): CRM_Admin_Page_Job->browse(NULL, NULL)
      #26 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Admin/Page/Job.php(136): CRM_Core_Page_Basic->run()
      #27 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/Invoke.php(310): CRM_Admin_Page_Job->run((Array:3), NULL)
      #28 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
      #29 /var/www/d7cividev/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
      #30 /var/www/d7cividev/sites/all/modules/civicrm/drupal/civicrm.module(451): CRM_Core_Invoke::invoke((Array:3))
      #31 [internal function](): civicrm_invoke("admin", "job")
      #32 /var/www/d7cividev/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:2))
      #33 /var/www/d7cividev/index.php(21): menu_execute_active_handler()
      #34 {main}
      

      PR coming soon.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              allenshaw Allen Shaw
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: