1

when I'm preparing a report (Event Participants) through CiviCRM Report module and I want to sort the results by anything (age, name, etc) I am getting a fatal error: DB ERROR: No such field

I'm currently on Drupal 7.69 and CiviCRM 5.20.

Here is the full log of this issue:

     [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: unknown error", -1, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#3 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#4 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", "DB_Error", TRUE)
#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(-1, NULL, NULL, "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", "1055 ** Expression #21 of SELECT list is not in GROUP BY clause and contains ...")
#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 SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#9 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(2416): DB_common->query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#10 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#11 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(435): DB_DataObject->query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#12 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(1428): CRM_Core_DAO->query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", TRUE)
#13 /var/www/html/sites/all/modules/civicrm/CRM/Report/Form.php(3076): CRM_Core_DAO::executeQuery("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#14 /var/www/html/sites/all/modules/civicrm/CRM/Report/Form.php(3561): CRM_Report_Form->buildRows("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", (Array:0))
#15 /var/www/html/sites/all/modules/civicrm/CRM/Report/Form/Event/ParticipantListing.php(616): CRM_Report_Form->postProcess()
#16 /var/www/html/sites/all/modules/civicrm/CRM/Core/Form.php(495): CRM_Report_Form_Event_ParticipantListing->postProcess()
#17 /var/www/html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Submit.php(74): CRM_Core_Form->mainProcess()
#18 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Submit->perform(Object(CRM_Report_Form_Event_ParticipantListing), "submit")
#19 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Report_Form_Event_ParticipantListing), "submit")
#20 /var/www/html/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("submit")
#21 /var/www/html/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(114): CRM_Core_Controller->run()
#22 /var/www/html/sites/all/modules/civicrm/CRM/Report/Page/Instance.php(90): CRM_Utils_Wrapper->run("CRM_Report_Form_Event_ParticipantListing", NULL, (Array:0))
#23 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(284): CRM_Report_Page_Instance->run((Array:4), NULL)
#24 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
#25 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#26 /var/www/html/sites/all/modules/civicrm/drupal/civicrm.module(444): CRM_Core_Invoke::invoke((Array:4))
#27 /var/www/html/includes/menu.inc(527): civicrm_invoke("report", "instance", "26")
#28 /var/www/html/index.php(21): menu_execute_active_handler()
#29 {main}

Has anyone come across such an issue? Is my database missing a table header?

Improve this question
1

It's a bug in the report but you can work around it by turning off ONLY_FULL_GROUP_BY on the server. See https://docs.civicrm.org/sysadmin/en/latest/requirements/#mysql-configuration

Improve this answer
  • Thanks! It works, but only partially - I am still not able to sort the report by the age of the participants (I can show the column "Age", but in sorting options, I have a choice called "Sort by Age in the day of the event" (not "Age"). Here is the error I'm getting: #6 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-19, NULL, NULL, "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", "1054 ** Unknown column 'contact_civireport.age_at_event' in 'field list'") – pannga Feb 27 '20 at 12:50
  • 1
    Ah yet another bug! Can you file a report at lab.civicrm.org/dev/report/issues? – Demerit Feb 27 '20 at 13:04
  • Already done that, thanks! lab.civicrm.org/dev/report/issues/26 – pannga Feb 27 '20 at 13:16

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.