0

I'm on Drupal 7.69 and CiviCRM 5.20.0.

As long as I remember I've been experiencing an error when I've tried to look up the bounced recipients of mailings through Advanced Search.

I can't look up any mailing recipients by advanced search. It works through reports though. In the advanced search, I'm only choosing the mailing title and status (completed).

I see that the error is about MySQL syntax but I don't know what could I do to fix this. Is there any way to rebuild the whole database in CiviCRM? here is the full error from logs:

Feb 17 09:53:36  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name
       FROM civicrm_contact contact_a   LEFT JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id  LEFT JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id  LEFT JOIN civ$
          civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id
          AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'
      WHERE  (  AND civicrm_mailing.id IN (407) AND  civicrm_mailing_job.status = 'Complete'  )  AND (contact_a.is_deleted = 0)

      GROUP BY sort_name
      ORDER BY sort_name asc [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 civicrm_mailing.id IN (407) AND  civicrm_mailing$
    [type] => DB_Error
    [user_info] => SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name
       FROM civicrm_contact contact_a   LEFT JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id  LEFT JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id  LEFT JOIN civ$
          civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id
          AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'
      WHERE  (  AND civicrm_mailing.id IN (407) AND  civicrm_mailing_job.status = 'Complete'  )  AND (contact_a.is_deleted = 0)

      GROUP BY sort_name
      ORDER BY sort_name asc [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 civicrm_mailing.id IN (407) AND  civicrm_mailing$
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name
       FROM civicrm_contact contact_a   LEFT JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id  LEFT JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id  LEFT JOIN civ$
          civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id
          AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'
      WHERE  (  AND civicrm_mailing.id IN (407) AND  civicrm_mailing_job.status = 'Complete'  )  AND (contact_a.is_deleted = 0)

      GROUP BY sort_name
      ORDER BY sort_name asc [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 civicrm_mailing.id IN (407) AND  civicrm_mailing$
)



Feb 17 09:53:36  [debug] $backTrace = #0 /var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php(208): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/html/sites/all/modules/civicrm/packages/DB.php(987): PEAR_Error->__construct("DB Error: syntax error", -2, 16, (Array:2), "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr...")
#3 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-2, 16, (Array:2), "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr...")
#4 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -2, 16, (Array:2), "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr..."$
#5 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1920): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-2, NULL, NULL, "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr...", "1064 ** You have an error in your SQL syn$
#7 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1229): DB_mysqli->simpleQuery("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr...")
#9 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(2416): DB_common->query("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr...")
#10 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr...")
#11 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(435): DB_DataObject->query("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr...")
#12 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(1428): CRM_Core_DAO->query("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr...", TRUE)
#13 /var/www/html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(5027): CRM_Core_DAO::executeQuery("SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n       FROM civicr...")
#14 /var/www/html/sites/all/modules/civicrm/CRM/Contact/Selector.php(1199): CRM_Contact_BAO_Query->alphabetQuery()
#15 /var/www/html/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(108): CRM_Contact_Selector->alphabetQuery()
#16 /var/www/html/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(136): CRM_Utils_PagerAToZ::getDynamicCharacters(Object(CRM_Contact_Selector), FALSE)
#17 /var/www/html/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(52): CRM_Utils_PagerAToZ::createLinks(Object(CRM_Contact_Selector), NULL, FALSE)
#18 /var/www/html/sites/all/modules/civicrm/CRM/Contact/Form/Search.php(869): CRM_Utils_PagerAToZ::getAToZBar(Object(CRM_Contact_Selector), NULL)
#19 /var/www/html/sites/all/modules/civicrm/CRM/Contact/Form/Search/Advanced.php(320): CRM_Contact_Form_Search->postProcess()
#20 /var/www/html/sites/all/modules/civicrm/CRM/Core/Form.php(495): CRM_Contact_Form_Search_Advanced->postProcess()
#21 /var/www/html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form->mainProcess()
#22 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Contact_Form_Search_Advanced), "refresh")
#23 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Search_Advanced), "refresh")
#24 /var/www/html/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("refresh")
#25 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(284): CRM_Core_Controller->run((Array:4), (Array:0))
#26 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
#27 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#28 /var/www/html/sites/all/modules/civicrm/drupal/civicrm.module(444): CRM_Core_Invoke::invoke((Array:4))
#29 /var/www/html/includes/menu.inc(527): civicrm_invoke("contact", "search", "advanced")
#30 /var/www/html/index.php(21): menu_execute_active_handler()
#31 {main}

I will appreciate any help towards the solution of this error.

0

Welcome to CiviCEM Emeryk, The issue you getting has been now fixed and will be part of 5.23 release. You can either wait for 5.23 release and upgrade CiviCRM or apply patch from here.

HTH

Pradeep

|improve this answer|||||
  • It worked like a charm, thank you very much – pangadev Feb 17 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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