0

Scheduled email reminders were working fine on my Drupal/CiviCRM server, then suddenly stopped working, with the last successful mail being sent on 2/11. I don't recall making any significant changes around that time. This is not a cron issue, as the job is executing regularly, it's just returning the following error when it does run.

Mar 24 13:12:33  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => SELECT *
FROM civicrm_event r
INNER JOIN civicrm_event 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
WHERE (r.id IN ("456")) AND (r.is_active = 1) AND (r.is_template = 0) AND ("20210324131233" <= DATE_SUB(start_date, INTERVAL 11 hour))
LIMIT 1
OFFSET 0
 [nativecode=1066 ** Not unique table/alias: 'r']
    [type] => DB_Error
    [user_info] => SELECT *
FROM civicrm_event r
INNER JOIN civicrm_event 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
WHERE (r.id IN ("456")) AND (r.is_active = 1) AND (r.is_template = 0) AND ("20210324131233" <= DATE_SUB(start_date, INTERVAL 11 hour))
LIMIT 1
OFFSET 0
 [nativecode=1066 ** Not unique table/alias: 'r']
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT *
FROM civicrm_event r
INNER JOIN civicrm_event 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
WHERE (r.id IN ("456")) AND (r.is_active = 1) AND (r.is_template = 0) AND ("20210324131233" <= DATE_SUB(start_date, INTERVAL 11 hour))
LIMIT 1
OFFSET 0
 [nativecode=1066 ** Not unique table/alias: 'r']"]
)


Mar 24 13:12:33  [debug] $backTrace = #0 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/Error.php(942): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB.php(997): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "SELECT *\nFROM civicrm_event r\nINN\
ER JOIN civicrm_event r ON e.event_id = r....")
#3 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "SELECT *\nFROM civicrm_event r\nINNER JOIN \
civicrm_event r ON e.event_id = r....")
#4 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR::_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "SELECT *\nFROM civicrm\
_event r\nINNER JOIN civicrm_event r ON e.event_id = r....", "DB_Error", TRUE)
#5 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1928): PEAR->__call("raiseError", (Array:7))
#6 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(936): DB_common->raiseError(-1, NULL, NULL, "SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r O\
N e.event_id = r....", "1066 ** Not unique table/alias: 'r'")
#7 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1234): DB_mysqli->simpleQuery("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id =\
 r....")
#9 /home/vectorspace/www/www/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common->query("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id = r....")
#10 /home/vectorspace/www/www/sites/all/modules/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id = r\
....")
#11 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/DAO.php(457): DB_DataObject->query("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id = r....")
#12 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/DAO.php(1564): CRM_Core_DAO->query("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id = r....", TRUE)
#13 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/ActionSchedule/RecipientBuilder.php(251): CRM_Core_DAO::executeQuery("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r\
 ON e.event_id = r....")
#14 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/ActionSchedule/RecipientBuilder.php(150): Civi\ActionSchedule\RecipientBuilder->buildAddlRepeatPass()
#15 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/BAO/ActionSchedule.php(353): Civi\ActionSchedule\RecipientBuilder->build()
#16 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/BAO/ActionSchedule.php(371): CRM_Core_BAO_ActionSchedule::buildRecipientContacts("3", "20210324131233", (Array:2))
#17 /home/vectorspace/www/www/sites/all/modules/civicrm/api/v3/Job.php(207): CRM_Core_BAO_ActionSchedule::processQueue("20210324131233", (Array:2))
#18 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_job_send_reminder((Array:2))
#19 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Kernel.php(150): Civi\API\Provider\MagicFunctionProvider->invoke((Array:8))
#20 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Kernel.php(81): Civi\API\Kernel->runRequest((Array:8))
#21 /home/vectorspace/www/www/sites/all/modules/civicrm/api/api.php(22): Civi\API\Kernel->runSafe("Job", "send_reminder", (Array:1))
#22 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/JobManager.php(134): civicrm_api("Job", "send_reminder", (Array:1))
#23 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/JobManager.php(66): CRM_Core_JobManager->executeJob(Object(CRM_Core_ScheduledJob))
#24 /home/vectorspace/www/www/sites/all/modules/civicrm/api/v3/Job.php(118): CRM_Core_JobManager->execute(FALSE)
#25 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_job_execute((Array:1))
#26 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Kernel.php(150): Civi\API\Provider\MagicFunctionProvider->invoke((Array:8))
#27 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Kernel.php(81): Civi\API\Kernel->runRequest((Array:8))
#28 /home/vectorspace/www/www/sites/all/modules/civicrm/api/api.php(22): Civi\API\Kernel->runSafe("job", "execute", (Array:1))
#29 phar:///home/vectorspace/private/bin/cv/src/Command/ApiCommand.php(74): civicrm_api("job", "execute", (Array:1))
#30 phar:///home/vectorspace/private/bin/cv/vendor/symfony/console/Command/Command.php(257): Civi\Cv\Command\ApiCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Sy\
mfony\Component\Console\Output\ConsoleOutput))
#31 phar:///home/vectorspace/private/bin/cv/vendor/symfony/console/Application.php(850): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Ob\
ject(Symfony\Component\Console\Output\ConsoleOutput))
#32 phar:///home/vectorspace/private/bin/cv/vendor/symfony/console/Application.php(193): Symfony\Component\Console\Application->doRunCommand(Object(Civi\Cv\Command\ApiCommand), Object(Symfo\
ny\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#33 phar:///home/vectorspace/private/bin/cv/src/Application.php(46): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\
\Console\Output\ConsoleOutput))
#34 phar:///home/vectorspace/private/bin/cv/vendor/symfony/console/Application.php(124): Civi\Cv\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Compone\
nt\Console\Output\ConsoleOutput))
#35 phar:///home/vectorspace/private/bin/cv/src/Application.php(15): Symfony\Component\Console\Application->run()
#36 phar:///home/vectorspace/private/bin/cv/bin/cv(27): Civi\Cv\Application::main("phar:///home/vectorspace/private/bin/cv/bin")
#37 /home/vectorspace/private/bin/cv(14): require("phar:///home/vectorspace/private/bin/cv/bin/cv")
#38 {main}

When I disable event 455 and manually execute the scheduled reminders, the same type of error occurs but for event 454, so I don't believe it has to do with event 455. Is it something to do with cv? I installed cv many more months ago and haven't made any changes to it. Your help is appreciated!

2
  • What is you Civi version? Mar 24, 2021 at 18:40
  • Civi version 5.32.2, Drupal version 7.77 Mar 24, 2021 at 21:35

1 Answer 1

3

This appears when you have schedule reminder with is repeat ON and 'Also Include' Recipient set. Try applying below patch

diff --git a/CRM/Event/ActionMapping.php b/CRM/Event/ActionMapping.php
index 9c8aaae7a5..0874099eb0 100644
--- a/CRM/Event/ActionMapping.php
+++ b/CRM/Event/ActionMapping.php
@@ -136,7 +136,7 @@ class CRM_Event_ActionMapping extends \Civi\ActionSchedule\Mapping {
     $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status);
 
     $query = \CRM_Utils_SQL_Select::from("{$this->entity} e")->param($defaultParams);
-    $query['casAddlCheckFrom'] = 'civicrm_event r';
+    $query['casAddlCheckFrom'] = 'civicrm_participant e';
     $query['casContactIdField'] = 'e.contact_id';
     $query['casEntityIdField'] = 'e.id';
     $query['casContactTableAlias'] = NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.