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

Scheduled reminder for Activities produces SQL error if configured with certain delivery options

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Minor
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.3.0
    • Fix Version/s: 4.3.0
    • Component/s: None
    • Labels:
      None

      Description

      It appears that the code that builds the list of recipents (CRM_Core_BAO_ActionSchedule::buildRecipientContacts()) is assuming that reminders for Activities will have one of the predefined recipient options (Activity Target, etc.) configured as the recipient, and it will break with an SQL syntax error when the schedule reminder is configured for Manual Recipients or for a Group recipient.

      Steps to reproduce:
      1. Open the form to Create a new scheduled reminder at /civicrm/admin/scheduleReminders?action=add&reset=1
      2. Select "Activity" in the Entity field; define a time that's likely to trigger a matching activity (e.g., 1 day before Activity Date and Time). Note: this may also reproducible by sending at a specific date and time; I didn't try.
      3. For the "Recipients" field, select "Choose Recipients", and then enter a contact in the "Manual Recipients" field. Note: this is known also the be reproducible by selecting "Select a Group" in the "Recipients" field, and selecting a group in the "Group" field.
      4. Save the scheduled reminder.
      5. Ensure there's an activity that will match the scheduled reminder, according to the timing configured in step 2.
      6. Navigate to "Settings - Scheduled Jobs" page at /civicrm/admin/job?reset=1, and locate the "send_reminders" job. Execute this job manually.
      7. Observe that the mail was not sent, no entry was made in civicrm_schedule_log, and the below SQL error and backtrace is reported in ConfigAndLog logging.
      8. If you have PHP configured to display E_NOTICE errors, observe the following Drupal error messages in the top of the page:
      Notice: Undefined index: in CRM_Core_BAO_ActionSchedule::buildRecipientContacts() (line 755 of /var/www/domains/devel/civicrm_versions/civicrm-trunk-4.3/civicrm/CRM/Core/BAO/ActionSchedule.php).
      Notice: Undefined variable: contactField in CRM_Core_BAO_ActionSchedule::buildRecipientContacts() (line 856 of /var/www/domains/devel/civicrm_versions/civicrm-trunk-4.3/civicrm/CRM/Core/BAO/ActionSchedule.php).
      Notice: Undefined variable: contactField in CRM_Core_BAO_ActionSchedule::buildRecipientContacts() (line 859 of /var/www/domains/devel/civicrm_versions/civicrm-trunk-4.3/civicrm/CRM/Core/BAO/ActionSchedule.php).
      Notice: Undefined variable: contactField in CRM_Core_BAO_ActionSchedule::buildRecipientContacts() (line 863 of /var/www/domains/devel/civicrm_versions/civicrm-trunk-4.3/civicrm/CRM/Core/BAO/ActionSchedule.php).
      Notice: Undefined variable: contactField in CRM_Core_BAO_ActionSchedule::buildRecipientContacts() (line 868 of /var/www/domains/devel/civicrm_versions/civicrm-trunk-4.3/civicrm/CRM/Core/BAO/ActionSchedule.php).

      MySQL error and backtrace:

      INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
      SELECT as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as action_schedule_id
      FROM civicrm_activity e
      INNER JOIN civicrm_contact c ON c.id =
      LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = AND
      reminder.entity_id = e.id AND
      reminder.entity_table = 'civicrm_activity' AND
      reminder.action_schedule_id = 1
      WHERE e.activity_type_id IN (1) AND e.is_current_revision = 1 AND e.is_deleted = 0 AND IN (2) AND c.is_deleted = 0 AND reminder.id IS NULL AND '20130401174723' >= DATE_SUB(e.activity_date_time, INTERVAL 1 day) AND DATE_SUB(20130401174723, INTERVAL 1 DAY ) <= DATE_SUB(e.activity_date_time, INTERVAL 1 day) = #0 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/Error.php(530): CRM_Core_Error::backtrace("\nINSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action...", TRUE)
      #1 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/packages/DB/DataObject.php(2359): CRM_Core_Error::debug_query("\nINSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action...")
      #2 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("\nINSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action...")
      #3 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/DAO.php(155): DB_DataObject->query("\nINSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action...")
      #4 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/DAO.php(922): CRM_Core_DAO->query("\nINSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action...", TRUE)
      #5 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/BAO/ActionSchedule.php(899): CRM_Core_DAO::executeQuery("\nINSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action...", (Array:1))
      #6 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/BAO/ActionSchedule.php(983): CRM_Core_BAO_ActionSchedule::buildRecipientContacts(1, "20130401174723")
      #7 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/api/v3/Job.php(120): CRM_Core_BAO_ActionSchedule::processQueue(NULL)
      #8 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/api/api.php(72): civicrm_api3_job_send_reminder((Array:1))
      #9 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/JobManager.php(127): civicrm_api("job", "send_reminder", (Array:1))
      #10 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/JobManager.php(108): CRM_Core_JobManager->executeJob(Object(CRM_Core_ScheduledJob))
      #11 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Admin/Page/Job.php(156): CRM_Core_JobManager->executeJobById("8")
      #12 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/Page/Basic.php(186): CRM_Admin_Page_Job->browse(NULL, NULL)
      #13 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Admin/Page/Job.php(141): CRM_Core_Page_Basic->run()
      #14 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/Invoke.php(220): CRM_Admin_Page_Job->run((Array:3), NULL)
      #15 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/CRM/Core/Invoke.php(51): CRM_Core_Invoke::_invoke((Array:3))
      #16 /var/www/domains/devel/civicrm_versions/civicrm-tarball-4.2.7/civicrm/drupal/civicrm.module(500): CRM_Core_Invoke::invoke((Array:3))
      #17 [internal function](): civicrm_invoke("admin", "job")
      #18 /var/www/domains/devel/www/d7/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:2))
      #19 /var/www/domains/devel/www/d7/index.php(21): menu_execute_active_handler()
      #20

      {main}

        Attachments

          Activity

            People

            • Assignee:
              ravish.nair Ravish Nair
              Reporter:
              allenshaw Allen Shaw
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: