0

We upgraded from 4.5.8 to 4.6.5 today (on Drupal 7.38). The only known issue at this point is on the Advanced Search screen. Using the Relationship search field "Target Contact(s) in Group" results in a DB Error: syntax error.

Here is the backtrace and debugging log from the error:

backTrace

#0 /sites/all/modules/contrib/civicrm/CRM/Core/Error.php(182): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /sites/all/modules/contrib/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /sites/all/modules/contrib/civicrm/packages/DB.php(976): PEAR_Error->PEAR_Error("DB Error: syntax error", -2, 16, (Array:2), "\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHE...")
#4 /sites/all/modules/contrib/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-2, 16, (Array:2), "\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHE...")
#5 /sites/all/modules/contrib/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -2, NULL, NULL, "\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHE...", "DB_Error", TRUE)
#6 /sites/all/modules/contrib/civicrm/packages/DB/mysql.php(899): DB_common->raiseError(-2, NULL, NULL, NULL, "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /sites/all/modules/contrib/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
#8 /sites/all/modules/contrib/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHE...")
#9 /sites/all/modules/contrib/civicrm/packages/DB/DataObject.php(2442): DB_common->query("\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHE...")
#10 /sites/all/modules/contrib/civicrm/packages/DB/DataObject.php(1634): DB_DataObject->_query("\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHE...")
#11 /sites/all/modules/contrib/civicrm/CRM/Core/DAO.php(325): DB_DataObject->query("\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHE...")
#12 /sites/all/modules/contrib/civicrm/CRM/Core/DAO.php(1179): CRM_Core_DAO->query("\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHE...", TRUE)
#13 /sites/all/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(2933): CRM_Core_DAO::executeQuery("\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHE...")
#14 /sites/all/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(4072): CRM_Contact_BAO_Query->addGroupContactCache((Array:1), "civicrm_relationship_group_contact_cache", "contact_b", "=")
#15 /sites/all/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(1820): CRM_Contact_BAO_Query->relationship((Array:5))
#16 /sites/all/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(1890): CRM_Contact_BAO_Query->whereClauseSingle((Array:5))
#17 /sites/all/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(499): CRM_Contact_BAO_Query->whereClause()
#18 /sites/all/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(461): CRM_Contact_BAO_Query->initialize()
#19 /sites/all/modules/contrib/civicrm/CRM/Contact/Selector.php(224): CRM_Contact_BAO_Query->__construct((Array:7), (Array:4), NULL, FALSE, FALSE, 1, FALSE, FALSE, FALSE, "", "AND")
#20 /sites/all/modules/contrib/civicrm/CRM/Contact/Form/Search.php(833): CRM_Contact_Selector->__construct(NULL, (Array:42), (Array:7), NULL, 512, FALSE, FALSE, "advanced", (Array:11))
#21 /sites/all/modules/contrib/civicrm/CRM/Contact/Form/Search/Advanced.php(330): CRM_Contact_Form_Search->postProcess()
#22 /sites/all/modules/contrib/civicrm/CRM/Core/Form.php(345): CRM_Contact_Form_Search_Advanced->postProcess()
#23 /sites/all/modules/contrib/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form->mainProcess()
#24 /sites/all/modules/contrib/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Contact_Form_Search_Advanced), "refresh")
#25 /sites/all/modules/contrib/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Search_Advanced), "refresh")
#26 /sites/all/modules/contrib/civicrm/CRM/Core/Controller.php(353): HTML_QuickForm_Page->handle("refresh")
#27 /sites/all/modules/contrib/civicrm/CRM/Core/Invoke.php(312): CRM_Core_Controller->run((Array:4), (Array:0))
#28 /sites/all/modules/contrib/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:13))
#29 /sites/all/modules/contrib/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:4))
#30 /sites/all/modules/contrib/civicrm/drupal/civicrm.module(489): CRM_Core_Invoke::invoke((Array:4))
#31 [internal function](): civicrm_invoke("contact", "search", "advanced")
#32 /includes/menu.inc(519): call_user_func_array("civicrm_invoke", (Array:3))
#33 /index.php(21): menu_execute_active_handler()
#34 {main}

Sorry but we are not able to provide this at the moment.
DB Error: syntax error
Error Details
Database Error Code: 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 '0 AND ( saved_search_id != 0 OR saved_search_id IS NOT NULL OR ch' at line 3, 1064
Additional Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => 
SELECT id, cache_date, saved_search_id, children
FROM   civicrm_group
WHERE  id 0
  AND  ( saved_search_id != 0
   OR    saved_search_id IS NOT NULL
   OR    children IS NOT NULL )
 [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 '0
  AND  ( saved_search_id != 0
   OR    saved_search_id IS NOT NULL
   OR    ch' at line 3]
    [type] => DB_Error
    [user_info] => 
SELECT id, cache_date, saved_search_id, children
FROM   civicrm_group
WHERE  id 0
  AND  ( saved_search_id != 0
   OR    saved_search_id IS NOT NULL
   OR    children IS NOT NULL )
 [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 '0
  AND  ( saved_search_id != 0
   OR    saved_search_id IS NOT NULL
   OR    ch' at line 3]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
SELECT id, cache_date, saved_search_id, children
FROM   civicrm_group
WHERE  id 0
  AND  ( saved_search_id != 0
   OR    saved_search_id IS NOT NULL
   OR    children IS NOT NULL )
 [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 '0
  AND  ( saved_search_id != 0
   OR    saved_search_id IS NOT NULL
   OR    ch' at line 3]"]
)

Return to home page.

Anybody know how to parse this out and isolate what the error might be? It appears to be a SQL error, but I'm afraid I don't know what to do with it or where to begin resolving it. We are using MySQL version: 5.1.54

If it's relevant, I tried to use the Target Contact(s) in Group on the Drupal demo site to see if it was just our install, and while it did return a successful "no search results" at the bottom of the search screen, it yielded this warning at the top:

Notice: Undefined variable: iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReport in CRM_Contact_BAO_Query::buildClause() (line 5193 of /srv/buildkit/build/d46/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php).

Thanks for any help!

  • The MySQL version doesn't seem to be relevant. Our development environment has MySQL 5.6 and the same happens. – Allen Hutchison Jul 23 '15 at 18:51
  • I'm a bit confused about your version. There WERE some search regressions in 4.6.5 - but i wouldn't expect that enotice to be in 4.6.5 - but in 4.6.6 -which hopefully also includes fixes for the search regressions (due out first week Aug) – eileen Jul 23 '15 at 21:08
  • 1
    OK - have a go at putting this version of 4.6.5 on your site dist.fudev.co.nz/by-date/latest/4.6.5rc1 It is the Fuzion repo & has some patches that are due to come out in 4.6.6 - it does contain one search fix. – eileen Jul 24 '15 at 3:05
  • 1
    Eileen, thank you again, I put in the 4.6.5rc1 from Fuzion's repo and that got us past the DB error. I now get the enotice, BUT the search now returns results! If you're willing to put your comment into an answer, I'll gladly mark it as the accepted one. Looks like 4.6.6 will fix the issue. – Allen Hutchison Jul 24 '15 at 15:55
  • 1
    I have stated the reason and fix in civicrm.stackexchange.com/a/4243/831. Please check my comment – Monish Deb Jul 28 '15 at 11:31
2

We reverted back to 4.6.4 for the sake of production. Then when 4.6.6 became available, an upgrade to 4.6.6 fixed this issue.

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.