2

Since updating my CiviCRM 4.7.30 on WordPress 4.9.5, I am getting "DB Error: No such field" when doing queries through Advanced Search or Search Builder. Some of the problems where when I was trying to use a Profile to view an Advanced Search and others were related to fields that I was trying to search by (my assumption is that they are fields that need lookups, but I haven't been able to narrow this down yet). Below is an example Search Builder query that I tried and the resulting error that was in the CiviCRM log file located in the /wp-content/uploads/civicrm/ConfigAndLog directory.

The query was to find Organizations that were Not In two designated groups shown in the image below: enter image description here

Not sure if it helps but when I used the Back Key to get back to review my previous query the query screen looked like the following:

enter image description here

The related error in the CiviCRM log file was:

> Apr 27 10:25:32  [info] $Fatal Error Details = Array (
>     [callback] => Array
>         (
>             [0] => CRM_Core_Error
>             [1] => handle
>         )
> 
>     [code] => -19
>     [message] => DB Error: no such field
>     [mode] => 16
>     [debug_info] => SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type
> as `contact_sub_type`, contact_a.sort_name as `sort_name`, 
>             CONCAT_WS(',',
>             GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
>             GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
>           )
>           as groups   FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-43,47` ON (contact_a.id =
> `civicrm_group_contact-43,47`.contact_id AND
> `civicrm_group_contact-43,47`.status IN ('Added'))  WHERE  (  (  ( (
> `civicrm_group_contact-43,47`.group_id NOT IN ( 43,47 )  ) )  AND
> contact_a.contact_type = 'organization' )  )  AND
> (contact_a.is_deleted = 0)   GROUP BY contact_a.id  ORDER BY
> UPPER(LEFT(contact_a.sort_name, 1)) asc  [nativecode=1054 ** Unknown
> column 'civicrm_group_contact.status' in 'field list']
>     [type] => DB_Error
>     [user_info] => SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type
> as `contact_sub_type`, contact_a.sort_name as `sort_name`, 
>             CONCAT_WS(',',
>             GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
>             GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
>           )
>           as groups   FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-43,47` ON (contact_a.id =
> `civicrm_group_contact-43,47`.contact_id AND
> `civicrm_group_contact-43,47`.status IN ('Added'))  WHERE  (  (  ( (
> `civicrm_group_contact-43,47`.group_id NOT IN ( 43,47 )  ) )  AND
> contact_a.contact_type = 'organization' )  )  AND
> (contact_a.is_deleted = 0)   GROUP BY contact_a.id  ORDER BY
> UPPER(LEFT(contact_a.sort_name, 1)) asc  [nativecode=1054 ** Unknown
> column 'civicrm_group_contact.status' in 'field list']
>     [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix=""
> info="SELECT contact_a.id as contact_id, contact_a.contact_type as
> `contact_type`, contact_a.contact_sub_type as `contact_sub_type`,
> contact_a.sort_name as `sort_name`, 
>             CONCAT_WS(',',
>             GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
>             GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
>           )
>           as groups   FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-43,47` ON (contact_a.id =
> `civicrm_group_contact-43,47`.contact_id AND
> `civicrm_group_contact-43,47`.status IN ('Added'))  WHERE  (  (  ( (
> `civicrm_group_contact-43,47`.group_id NOT IN ( 43,47 )  ) )  AND
> contact_a.contact_type = 'organization' )  )  AND
> (contact_a.is_deleted = 0)   GROUP BY contact_a.id  ORDER BY
> UPPER(LEFT(contact_a.sort_name, 1)) asc  [nativecode=1054 ** Unknown
> column 'civicrm_group_contact.status' in 'field list']"] )
> 
> 
> Apr 27 10:25:32  [info] $backTrace = #0
> /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(232):
> CRM_Core_Error::backtrace("backTrace", TRUE)
> #1 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921):
> CRM_Core_Error::handle(Object(DB_Error))
> #2 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB.php(985):
> PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2),
> "SELECT contact_a.id as contact_id, contact_a.contact_type as
> `contact_type`, ...")
> #3 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575):
> DB_Error->__construct(-19, 16, (Array:2), "SELECT contact_a.id as
> contact_id, contact_a.contact_type as `contact_type`, ...")
> #4 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223):
> PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), "SELECT
> contact_a.id as contact_id, contact_a.contact_type as `contact_type`,
> ...", "DB_Error", TRUE)
> #5 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905):
> PEAR->__call("raiseError", (Array:7))
> #6 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933):
> DB_common->raiseError(-19, NULL, NULL, "SELECT contact_a.id as
> contact_id, contact_a.contact_type as `contact_type`, ...", "1054 **
> Unknown column 'civicrm_group_contact.status' in 'field list'")
> #7 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403):
> DB_mysqli->mysqliRaiseError()
> #8 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216):
> DB_mysqli->simpleQuery("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #9 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2446):
> DB_common->query("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #10 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1635):
> DB_DataObject->_query("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #11 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(392):
> DB_DataObject->query("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #12 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1348):
> CRM_Core_DAO->query("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...", TRUE)
> #13 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4942):
> CRM_Core_DAO::executeQuery("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #14 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Contact/Selector.php(1181):
> CRM_Contact_BAO_Query->searchQuery(NULL, NULL, NULL, FALSE, FALSE,
> TRUE)
> #15 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(108):
> CRM_Contact_Selector->alphabetQuery()
> #16 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(136):
> CRM_Utils_PagerAToZ::getDynamicCharacters(Object(CRM_Contact_Selector),
> FALSE)
> #17 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(52):
> CRM_Utils_PagerAToZ::createLinks(Object(CRM_Contact_Selector), NULL,
> FALSE)
> #18 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Contact/Form/Search.php(839):
> CRM_Utils_PagerAToZ::getAToZBar(Object(CRM_Contact_Selector), NULL)
> #19 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Contact/Form/Search/Builder.php(414):
> CRM_Contact_Form_Search->postProcess()
> #20 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(447):
> CRM_Contact_Form_Search_Builder->postProcess()
> #21 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75):
> CRM_Core_Form->mainProcess()
> #22 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203):
> CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Contact_Form_Search_Builder),
> "refresh")
> #23 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103):
> HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Search_Builder), "refresh")
> #24 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(351):
> HTML_QuickForm_Page->handle("refresh")
> #25 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(309):
> CRM_Core_Controller->run((Array:4), (Array:0))
> #26 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(84):
> CRM_Core_Invoke::runItem((Array:13))
> #27 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52):
> CRM_Core_Invoke::_invoke((Array:4))
> #28 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm.php(1246):
> CRM_Core_Invoke::invoke((Array:4))
> #29 /home/coloradomaker/www/www/wp-includes/class-wp-hook.php(286): CiviCRM_For_WordPress->invoke("")
> #30 /home/coloradomaker/www/www/wp-includes/class-wp-hook.php(310): WP_Hook->apply_filters("", (Array:1))
> #31 /home/coloradomaker/www/www/wp-includes/plugin.php(453): WP_Hook->do_action((Array:1))
> #32 /home/coloradomaker/www/www/wp-admin/admin.php(224): do_action("toplevel_page_CiviCRM")
|improve this question
  • Or rerun your group cache rebuild since i see "Unknown > column 'civicrm_group_contact.status'" in your error – petednz - fuzion♦ Apr 27 '18 at 21:14
  • I assume you mean removing all files in my template_c directory. Will give it a try...be back as it takes awhile. – ChaosBuster Apr 27 '18 at 21:54
  • While waiting...I checked my Clean-up Temporary Data and Files scheduled job and it ran with no errors. It executes hourly. – ChaosBuster Apr 27 '18 at 21:59
  • After deleting all files under the templates_c directory, I went to Administer > System Settings > Cleanup Caches and Update Paths and selected Cleanup Caches then Reset Paths. Same DB Error using the same Search Builder query above. – ChaosBuster Apr 27 '18 at 22:05
  • While in the Scheduled Jobs area decided to run some of them related to cache and updates. The following finished execution with Success (1): Update Check and Disable expired relationships. – ChaosBuster Apr 27 '18 at 22:26
2

It should be fixed in the latest rc (5.2) https://download.civicrm.org/latest

From ChaosBuster: Was able to install the latest at this time, 5.1.2, and I am now able to query with the Search Builder for groups for the situation that was mentioned above.

|improve this answer
  • Also note - this affects sites with mysql mode FULL GROUP BY enabled - you can disable that mode in your sql config – user4278 May 8 '18 at 22:10
1

This may be a bug in 5.0.0. The Drupal Demo site (and our 5.0.0 + Drupal 7 site) fails with a similar message if you try to do a search builder search for Contacts > group = or <>. In our site, the error message also involves 'civicrm_group_contact.status' as an unknown field. Naturally, I can't see the log in the demo site, but the error reported on screen is the same.

I don't know how to post a bug... but hope this might help raise one! (I tried going to the issue tracker, but it's been moved to git hub and I couldn't spot how to report a bug there.)

|improve this answer
  • I just tried this on our recently updated 5.0.2rc and same behaviour. – Andyg8 May 8 '18 at 17:59
  • Andyg8, you aren't on rc 5.2, correct? You indicated 5.0.2rc..same as my version. user4278 indicated a few minutes ago that this was fixed in 5.2. I will be out all day tomorrow and not willing to do the change the day before, but willing to give it a go later this week. – ChaosBuster May 8 '18 at 21:53
  • Yes, I am on 5.0.2rc. And there is indeed a bug associated with this behaviour - at least in our case: lab.civicrm.org/dev/core/issues/106. I believe it has been fixed for 5.2rc. – Andyg8 May 9 '18 at 20:29
0

If logging is enabled, try disabling logging and re-enabling logging. I had a somewhat similar error, but it's different enough that it may not be the same issue.

|improve this answer
  • I tried this (+ resetting session) before posting. Thanks for helping me remember though, so others will know. ;-) – ChaosBuster Apr 27 '18 at 21:53
0

This may be related to my issue too - in the meantime, i found (due to the great answer Jon gave) that doing search -> custom search -> include / exclude search - I was able to get the results I wanted.

|improve this answer

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.