2

When I try and do any kind of search for cases and I select the box for "deleted cases" I get a page saying: DB Error: syntax error

I am using Civi 5.3.1 and Drupal 7.59.

The log gives the following error:

    Aug 26 11:16:52  [info] $backTrace = #0 /sites/all/modules/civicrm/CRM/Core/Error.php(232): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /sites/all/modules/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
#3 /sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: syntax error", -2, 16, (Array:2), "SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#4 /sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-2, 16, (Array:2), "SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -2, NULL, NULL, "SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...", "DB_Error", TRUE)
#6 /sites/all/modules/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))
#7 /sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#8 /sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -2, NULL, NULL, "SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...", "DB_Error", TRUE)
#9 /sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-2, NULL, NULL, NULL, "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#10 /sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#11 /sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#12 /sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#13 /sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#14 /sites/all/modules/civicrm/CRM/Core/DAO.php(415): DB_DataObject->query("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#15 /sites/all/modules/civicrm/CRM/Core/DAO.php(1371): CRM_Core_DAO->query("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...", TRUE)
#16 /sites/all/modules/civicrm/CRM/Case/BAO/Case.php(1591): CRM_Core_DAO::executeQuery("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#17 /sites/all/modules/civicrm/CRM/Case/Selector/Search.php(368): CRM_Case_BAO_Case::getNextScheduledActivity((Array:3), "upcoming")
#18 /sites/all/modules/civicrm/CRM/Core/Selector/Controller.php(420): CRM_Case_Selector_Search->getRows(4, 0, 50, Object(CRM_Utils_Sort), 1, NULL)
#19 /sites/all/modules/civicrm/CRM/Core/Selector/Controller.php(340): CRM_Core_Selector_Controller->getRows(Object(CRM_Core_Selector_Controller))
#20 /sites/all/modules/civicrm/CRM/Case/Form/Search.php(307): CRM_Core_Selector_Controller->run()
#21 /sites/all/modules/civicrm/CRM/Core/Form.php(466): CRM_Case_Form_Search->postProcess()
#22 /sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form->mainProcess()
#23 /sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Case_Form_Search), "refresh")
#24 /sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Case_Form_Search), "refresh")
#25 /sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("refresh")
#26 /sites/all/modules/civicrm/CRM/Core/Invoke.php(309): CRM_Core_Controller->run((Array:3), NULL)
#27 /sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
#28 /sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#29 /sites/all/modules/civicrm/drupal/civicrm.module(445): CRM_Core_Invoke::invoke((Array:3))
#30 [internal function](): civicrm_invoke("case", "search")
#31 /includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:2))
#32 /index.php(21): menu_execute_active_handler()
#33 {main}

EDIT:

Query that is causing the error is:

Aug 26 11:16:52  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id, c_type.title as case_type, civicrm_case.case_type_id as case_type_id, cov_status.label as case_status, cov_status.label as case_status_name, t_act.status_id, civicrm_case.start_date as case_start_date, case_relation_type.label_b_a as case_role, t_act.desired_date as case_scheduled_activity_date, t_act.id as case_scheduled_activity_id, t_act.act_type_name as case_scheduled_activity_type_name, t_act.act_type AS case_scheduled_activity_type  FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id  INNER JOIN
(
  SELECT case_id, act.id, activity_date_time AS desired_date, activity_type_id, status_id, aov.name AS act_type_name, aov.label AS act_type
  FROM (
    SELECT *
    FROM (
      SELECT *
      FROM civicrm_view_case_activity_upcoming
      ORDER BY activity_date_time ASC, id ASC
      ) AS upcomingOrdered
    ) AS act
  LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
  LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
) AS t_act

        ON t_act.case_id = civicrm_case.id
 LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
 LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 4197
      AND case_relationship.case_id = civicrm_case.id )

 LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )

 LEFT JOIN civicrm_case_type c_type
 ON civicrm_case.case_type_id = c_type.id

 LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

 LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
 WHERE (1) AND  civicrm_case_contact.contact_id IN( 1502 )
 AND civicrm_case.id IN( 41)
 AND civicrm_case.is_deleted     = 1  GROUP BY case_id  ORDER BY case_scheduled_activity_date ASC 1 [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 '1' at line 39]
    [type] => DB_Error
    [user_info] => SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id, c_type.title as case_type, civicrm_case.case_type_id as case_type_id, cov_status.label as case_status, cov_status.label as case_status_name, t_act.status_id, civicrm_case.start_date as case_start_date, case_relation_type.label_b_a as case_role, t_act.desired_date as case_scheduled_activity_date, t_act.id as case_scheduled_activity_id, t_act.act_type_name as case_scheduled_activity_type_name, t_act.act_type AS case_scheduled_activity_type  FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id  INNER JOIN
(
  SELECT case_id, act.id, activity_date_time AS desired_date, activity_type_id, status_id, aov.name AS act_type_name, aov.label AS act_type
  FROM (
    SELECT *
    FROM (
      SELECT *
      FROM civicrm_view_case_activity_upcoming
      ORDER BY activity_date_time ASC, id ASC
      ) AS upcomingOrdered
    ) AS act
  LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
  LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
) AS t_act

        ON t_act.case_id = civicrm_case.id
 LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
 LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 4197
      AND case_relationship.case_id = civicrm_case.id )

 LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )

 LEFT JOIN civicrm_case_type c_type
 ON civicrm_case.case_type_id = c_type.id

 LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

 LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
 WHERE (1) AND  civicrm_case_contact.contact_id IN( 1502 )
 AND civicrm_case.id IN( 41)
 AND civicrm_case.is_deleted     = 1  GROUP BY case_id  ORDER BY case_scheduled_activity_date ASC 1 [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 '1' at line 39]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id, c_type.title as case_type, civicrm_case.case_type_id as case_type_id, cov_status.label as case_status, cov_status.label as case_status_name, t_act.status_id, civicrm_case.start_date as case_start_date, case_relation_type.label_b_a as case_role, t_act.desired_date as case_scheduled_activity_date, t_act.id as case_scheduled_activity_id, t_act.act_type_name as case_scheduled_activity_type_name, t_act.act_type AS case_scheduled_activity_type  FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id  INNER JOIN
(
  SELECT case_id, act.id, activity_date_time AS desired_date, activity_type_id, status_id, aov.name AS act_type_name, aov.label AS act_type
  FROM (
    SELECT *
    FROM (
      SELECT *
      FROM civicrm_view_case_activity_upcoming
      ORDER BY activity_date_time ASC, id ASC
      ) AS upcomingOrdered
    ) AS act
  LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
  LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
) AS t_act

        ON t_act.case_id = civicrm_case.id
 LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
 LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 4197
      AND case_relationship.case_id = civicrm_case.id )

 LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )

 LEFT JOIN civicrm_case_type c_type
 ON civicrm_case.case_type_id = c_type.id

 LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

 LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
 WHERE (1) AND  civicrm_case_contact.contact_id IN( 1502 )
 AND civicrm_case.id IN( 41)
 AND civicrm_case.is_deleted     = 1  GROUP BY case_id  ORDER BY case_scheduled_activity_date ASC 1 [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 '1' at line 39]"]
)

EDIT:

I have included a screenshot of the case search options I selected to help clarify:

enter image description here

  • Can you also include the query? – Pradeep Nayak Aug 26 '18 at 10:35
  • How do I get the query? – Ben Aug 26 '18 at 10:50
  • I have added a screenshot of the search options I used for searching the cases. I tried various options but none of them worked if I selected "deleted". If I didn't select "deleted" it worked fine. – Ben Aug 26 '18 at 11:12
  • 1
    you can see CiviCRM log to find the query. – Pradeep Nayak Aug 26 '18 at 11:17
3

I could replicate this on 5.4.0. This is fixed at https://github.com/civicrm/civicrm-core/pull/12560

Can you please QA after applying the patch. And also please do post your QA results on PR.

Pradeep

  • Many thanks Pradeep. I applied the patch you gave and it fixed my error. I left a comment on the issue saying it worked. Is this what you meant by QA? Sorry I'm not sure what QA and PR stand for. – Ben Aug 26 '18 at 15:51
  • QA = Quality Assurance, PR = Pull Request (to pull the fix in to the main code base) – petednz - fuzion Aug 27 '18 at 23:12

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.