Answers.Body: <p>Welcome to CiviCEM Emeryk, The issue you getting has been now <a href="https://github.com/civicrm/civicrm-core/pull/16534" rel="nofollow noreferrer">fixed</a> and will be part of 5.23 release. You can either wait for 5.23 release and upgrade CiviCRM or apply patch from <a href="https://patch-diff.githubusercontent.com/raw/civicrm/civicrm-core/pull/16534.patch" rel="nofollow noreferrer">here</a>. </p>
  
  <p>HTH</p>
  
  <p>Pradeep</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-17T11:28:58.877Z
Answers.Text: It worked like a charm, thank you very much
Answers.UserDisplayName: pannga
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-17T11:09:39.880Z
Answers.LastActivityDate: 2020-02-17T11:09:39.880Z
Answers.UserDisplayName: Pradeep Nayak
Body: <p>I'm on <strong>Drupal 7.69</strong> and <strong>CiviCRM 5.20.0</strong>. </p>
  
  <p>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. </p>
  
  <p>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).</p>
  
  <p>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:</p>
  
  <pre><code>Feb 17 09:53:36  [error] $Fatal Error Details = Array
  (
      [callback] =&gt; Array
          (
              [0] =&gt; CRM_Core_Error
              [1] =&gt; handle
          )
  
      [code] =&gt; -2
      [message] =&gt; DB Error: syntax error
      [mode] =&gt; 16
      [debug_info] =&gt; 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] =&gt; DB_Error
      [user_info] =&gt; 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] =&gt; [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-&gt;__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-&gt;__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-&gt;_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-&gt;__call("raiseError", (Array:7))
  #6 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common-&gt;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-&gt;mysqliRaiseError()
  #8 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1229): DB_mysqli-&gt;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-&gt;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-&gt;_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-&gt;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-&gt;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-&gt;alphabetQuery()
  #15 /var/www/html/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(108): CRM_Contact_Selector-&gt;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-&gt;postProcess()
  #20 /var/www/html/sites/all/modules/civicrm/CRM/Core/Form.php(495): CRM_Contact_Form_Search_Advanced-&gt;postProcess()
  #21 /var/www/html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form-&gt;mainProcess()
  #22 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh-&gt;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-&gt;handle(Object(CRM_Contact_Form_Search_Advanced), "refresh")
  #24 /var/www/html/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page-&gt;handle("refresh")
  #25 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(284): CRM_Core_Controller-&gt;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}
  </code></pre>
  
  <p>I will appreciate any help towards the solution of this error.</p>
  
Tags: <error><search><mysql><mailing>
Title: Mailings advanced search error - Invalid mysql syntax
ThreadText: # Mailings advanced search error - Invalid mysql syntax
  
  (Asked by: username_0 on 2020-02-17)
  
  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.
  
  **Tags:** error, search, mysql, mailing
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2020-02-17. Score: 0 | Accepted answer
  
  Welcome to CiviCEM Emeryk, The issue you getting has been now [fixed](https://github.com/civicrm/civicrm-core/pull/16534) and will be part of 5.23 release. You can either wait for 5.23 release and upgrade CiviCRM or apply patch from [here](https://patch-diff.githubusercontent.com/raw/civicrm/civicrm-core/pull/16534.patch).
  
  HTH
  
  username_1
  
  > **Comments:**
  > **username_0**: It worked like a charm, thank you very much  
  
  ---
  
  