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.
