Custom Searches in events fail after 5.30.x upgrade
Overview
Please describe your problem or bug in detail.
After upgrading to CiviCRM 5.30 under Joomla, event based custom searches fail with: DB Error: value count on row and a stack trace. I have a development Drupal instance, but that failed to run CiviCRM after the update and I haven't managed to resolve that issue.
It is running under IIS, but we also see it under Linux.
Reproduction steps
- Click on Search and select Custom Searches
- Click on Event Aggregate
- Select an event
- Click Search
Current behaviour
What happens currently. Please provide error messages, screenshots or gifs (LICEcap, SilentCast) where appropriate. Stack trace:
#0 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\Error.php(148): CRM_Core_Error::backtrace()
#1 <Joomla directory>\administrator\components\com_civicrm\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php(922): CRM_Core_Error::handle(Object(DB_Error))
#2 <Joomla directory>\administrator\components\com_civicrm\civicrm\packages\DB.php(997): PEAR_Error->__construct("DB Error: value count on row", -22, 16, (Array:2), "\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...")
#3 <Joomla directory>\administrator\components\com_civicrm\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php(575): DB_Error->__construct(-22, 16, (Array:2), "\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...")
#4 <Joomla directory>\administrator\components\com_civicrm\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php(223): PEAR::_raiseError(Object(DB_mysqli), NULL, -22, 16, (Array:2), "\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...", "DB_Error", TRUE)
#5 <Joomla directory>\administrator\components\com_civicrm\civicrm\packages\DB\common.php(1920): PEAR->__call("raiseError", (Array:7))
#6 <Joomla directory>\administrator\components\com_civicrm\civicrm\packages\DB\mysqli.php(936): DB_common->raiseError(-22, NULL, NULL, "\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...", "1136 ** Column count doesn't match value count at row 1")
#7 <Joomla directory>\administrator\components\com_civicrm\civicrm\packages\DB\mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 <Joomla directory>\administrator\components\com_civicrm\civicrm\packages\DB\common.php(1229): DB_mysqli->simpleQuery("\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...")
#9 <Joomla directory>\administrator\components\com_civicrm\civicrm\packages\DB\DataObject.php(2696): DB_common->query("\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...")
#10 <Joomla directory>\administrator\components\com_civicrm\civicrm\packages\DB\DataObject.php(1829): DB_DataObject->_query("\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...")
#11 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\DAO.php(445): DB_DataObject->query("\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...")
#12 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\DAO.php(1551): CRM_Core_DAO->query("\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...", TRUE)
#13 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\PrevNextCache\Sql.php(43): CRM_Core_DAO::executeQuery("\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\n\n ...", (Array:0), FALSE)
#14 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Contact\Selector.php(1047): CRM_Core_PrevNextCache_Sql->fillWithSql("civicrm search f34df2377021b9d0d51da884d5a36bf1372d6d1cf25a00d692664ad719aadc...", "\n SELECT civicrm_participant.event_id as event_id,\n COUNT(civ...")
#15 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Contact\Selector.php(909): CRM_Contact_Selector->fillupPrevNextCache(Object(CRM_Utils_Sort), "civicrm search f34df2377021b9d0d51da884d5a36bf1372d6d1cf25a00d692664ad719aadc...", 0, 500)
#16 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Contact\Selector\Custom.php(358): CRM_Contact_Selector->buildPrevNextCache(Object(CRM_Utils_Sort))
#17 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\Selector\Controller.php(402): CRM_Contact_Selector_Custom->getRows(4, 0, 50, Object(CRM_Utils_Sort), 1, NULL)
#18 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\Selector\Controller.php(324): CRM_Core_Selector_Controller->getRows(Object(CRM_Contact_Selector_Controller))
#19 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Contact\Form\Search.php(867): CRM_Core_Selector_Controller->run()
#20 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Contact\Form\Search\Custom.php(157): CRM_Contact_Form_Search->postProcess()
#21 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\Form.php(507): CRM_Contact_Form_Search_Custom->postProcess()
#22 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\QuickForm\Action\Refresh.php(57): CRM_Core_Form->mainProcess()
#23 <Joomla directory>\administrator\components\com_civicrm\civicrm\packages\HTML\QuickForm\Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Contact_Form_Search_Custom), "refresh")
#24 <Joomla directory>\administrator\components\com_civicrm\civicrm\packages\HTML\QuickForm\Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Search_Custom), "refresh")
#25 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\Controller.php(347): HTML_QuickForm_Page->handle("refresh")
#26 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\Invoke.php(312): CRM_Core_Controller->run((Array:4), (Array:0))
#27 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\Invoke.php(68): CRM_Core_Invoke::runItem((Array:12))
#28 <Joomla directory>\administrator\components\com_civicrm\civicrm\CRM\Core\Invoke.php(36): CRM_Core_Invoke::_invoke((Array:4))
#29 <Joomla directory>\administrator\components\com_civicrm\civicrm.php(121): CRM_Core_Invoke::invoke((Array:4))
#30 <Joomla directory>\administrator\components\com_civicrm\civicrm.php(40): civicrm_invoke()
#31 <Joomla directory>\libraries\src\Component\ComponentHelper.php(402): require_once("<Joomla directory>\administrator\components\com_civicrm\civicrm.php")
#32 <Joomla directory>\libraries\src\Component\ComponentHelper.php(377): Joomla\CMS\Component\ComponentHelper::executeComponent("<Joomla directory>\administrator/components/com_civicrm/civicrm.php")
#33 <Joomla directory>\libraries\src\Application\AdministratorApplication.php(101): Joomla\CMS\Component\ComponentHelper::renderComponent("com_civicrm")
#34 <Joomla directory>\libraries\src\Application\AdministratorApplication.php(159): Joomla\CMS\Application\AdministratorApplication->dispatch()
#35 <Joomla directory>\libraries\src\Application\CMSApplication.php(196): Joomla\CMS\Application\AdministratorApplication->doExecute()
#36 <Joomla directory>\administrator\index.php(51): Joomla\CMS\Application\CMSApplication->execute()
#37 {main}
Expected behaviour
Environment information
What should happen. Something like:- Browser: Edge, but probably irrelevant_
- CiviCRM: 5.30.x... Upgrading from 5.29.1
- PHP: 7.3.23/7.4.11 but probably irrelevant
- CMS: Joomla 3.9.22 but may be irrelevant
- Database: MySQL 5.6.40-84.0-log/MySQL 5.7.31-log... but probably irrelevant
- Web Server: Apache/IIS but probably irrelevant.
Comments
Anything else you would like the reviewer to note. I have been attempting to research this, and it appears to be related to how CiviCRM is trying to populate civicrm_prevnext_cache where for the event based searches it is trying to add the wrong number of values. I am reporting it in case someone has a quick fix for the issue.