Keyoung 3 Posted November 9, 2018 Report Share Posted November 9, 2018 I encountered below error while trying to search an existing account: Error 500It looks like the application ran into an unexpected error. We apologize for the inconvenience and would like to do our best to fix this issue. If you would like to make a post on our forums we can actively interact with you in getting this resolved. If not, simply sending the error report helps us immensely and will only improve the quality of the software. Thanks! Send Error ReportHere's a quick list of what will be included in the report:Error Code: 500Error Message: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'x2crm.x2_actions.complete' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byStack Trace: [click to toggle display]CDbException in /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(941)#0 /var/www/html/x2crm/framework/db/CDbCommand.php(396): CDbCommand->queryInternal('fetchAll', Array, Array)#1 /var/www/html/x2crm/protected/components/sortableWidget/recordViewWidgets/ActionHistoryChartWidget.php(122): CDbCommand->queryAll()#2 /var/www/html/x2crm/protected/components/sortableWidget/recordViewWidgets/ActionHistoryChartWidget.php(303): ActionHistoryChartWidget::getChartData('1392184795', 1541902011, '14', 'accounts', NULL)#3 /var/www/html/x2crm/protected/components/sortableWidget/recordViewWidgets/ActionHistoryChartWidget.php(140): ActionHistoryChartWidget->getInitialChartData('1392184795', '14', 'Accounts', NULL)#4 /var/www/html/x2crm/framework/base/CComponent.php(111): ActionHistoryChartWidget->getSetupScript()#5 /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(941): CComponent->__get('setupScript')#6 /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(909): SortableWidget->renderWidgetContents()#7 /var/www/html/x2crm/protected/components/sortableWidget/views/recordViewWidget.php(42): SortableWidget->renderWidget()#8 /var/www/html/x2crm/framework/web/CBaseController.php(131): require('/var/www/html/x...')#9 /var/www/html/x2crm/framework/web/CBaseController.php(96): CBaseController->renderInternal('/var/www/html/x...', Array, false)#10 /var/www/html/x2crm/protected/components/X2Widget.php(81): CBaseController->renderFile('/var/www/html/x...', Array, false)#11 /var/www/html/x2crm/framework/web/widgets/CWidget.php(244): X2Widget->renderFile('/var/www/html/x...', Array, false)#12 /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(1054): CWidget->render('application.com...', Array)#13 /var/www/html/x2crm/protected/controllers/x2base.php(831): SortableWidget->run()#14 /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(396): x2base->widget('application.com...', Array)#15 /var/www/html/x2crm/protected/components/sortableWidget/RecordViewWidgetManager.php(124): SortableWidget::instantiateWidget('ActionHistoryCh...', Object(Profile), 'recordView', Array)#16 /var/www/html/x2crm/protected/components/sortableWidget/views/_twoColumnSortableWidgetLayout.php(53): RecordViewWidgetManager->displayWidgets(1)#17 /var/www/html/x2crm/framework/web/CBaseController.php(131): require('/var/www/html/x...')#18 /var/www/html/x2crm/framework/web/CBaseController.php(96): CBaseController->renderInternal('/var/www/html/x...', NULL, false)#19 /var/www/html/x2crm/protected/components/X2Widget.php(81): CBaseController->renderFile('/var/www/html/x...', NULL, false)#20 /var/www/html/x2crm/framework/web/widgets/CWidget.php(244): X2Widget->renderFile('/var/www/html/x...', NULL, false)#21 /var/www/html/x2crm/protected/components/sortableWidget/TwoColumnSortableWidgetManager.php(108): CWidget->render('_twoColumnSorta...')#22 /var/www/html/x2crm/protected/controllers/x2base.php(831): TwoColumnSortableWidgetManager->run()#23 /var/www/html/x2crm/protected/components/X2WidgetList.php(79): x2base->widget('RecordViewWidge...', Array)#24 /var/www/html/x2crm/protected/controllers/x2base.php(831): X2WidgetList->run()#25 /var/www/html/x2crm/protected/modules/accounts/views/accounts/view.php(144): x2base->widget('X2WidgetList', Array)#26 /var/www/html/x2crm/framework/web/CBaseController.php(127): require('/var/www/html/x...')#27 /var/www/html/x2crm/framework/web/CBaseController.php(96): CBaseController->renderInternal('/var/www/html/x...', Array, true)#28 /var/www/html/x2crm/framework/web/CController.php(869): CBaseController->renderFile('/var/www/html/x...', Array, true)#29 /var/www/html/x2crm/protected/controllers/x2base.php(729): CController->renderPartial('view', Array, true)#30 /var/www/html/x2crm/protected/controllers/x2base.php(265): x2base->render('view', Array)#31 /var/www/html/x2crm/protected/modules/accounts/controllers/AccountsController.php(138): x2base->view(Object(Accounts), 'accounts')#32 [internal function]: AccountsController->actionView('14')#33 /var/www/html/x2crm/framework/web/actions/CAction.php(109): ReflectionMethod->invokeArgs(Object(AccountsController), Array)#34 /var/www/html/x2crm/framework/web/actions/CInlineAction.php(47): CAction->runWithParamsInternal(Object(AccountsController), Object(ReflectionMethod), Array)#35 /var/www/html/x2crm/framework/web/CController.php(308): CInlineAction->runWithParams(Array)#36 /var/www/html/x2crm/framework/web/filters/CFilterChain.php(133): CController->runAction(Object(CInlineAction))#37 /var/www/html/x2crm/protected/controllers/x2base.php(579): CFilterChain->run()#38 /var/www/html/x2crm/framework/web/filters/CInlineFilter.php(58): x2base->filterSetPortlets(Object(CFilterChain))#39 /var/www/html/x2crm/framework/web/filters/CFilterChain.php(130): CInlineFilter->filter(Object(CFilterChain))#40 /var/www/html/x2crm/framework/web/filters/CFilter.php(40): CFilterChain->run()#41 /var/www/html/x2crm/framework/web/filters/CFilterChain.php(130): CFilter->filter(Object(CFilterChain))#42 /var/www/html/x2crm/framework/web/filters/CFilter.php(40): CFilterChain->run()#43 /var/www/html/x2crm/framework/web/filters/CFilterChain.php(130): CFilter->filter(Object(CFilterChain))#44 /var/www/html/x2crm/framework/web/CController.php(291): CFilterChain->run()#45 /var/www/html/x2crm/framework/web/CController.php(265): CController->runActionWithFilters(Object(CInlineAction), Array)#46 /var/www/html/x2crm/framework/web/CWebApplication.php(282): CController->run('view')#47 /var/www/html/x2crm/protected/components/X2WebApplication.php(63): CWebApplication->runController('accounts/accoun...')#48 /var/www/html/x2crm/framework/base/CApplication.php(185): X2WebApplication->processRequest()#49 /var/www/html/x2crm/index.php(65): CApplication->run()#50 {main}X2Engine Version: 7.0PHP Version: 7.0.32-4+ubuntu18.04.1+deb.sury.org+1 Quote Link to post Share on other sites
Keyoung 3 Posted November 13, 2018 Author Report Share Posted November 13, 2018 Any update?? Quote Link to post Share on other sites
X2Peter 43 Posted November 13, 2018 Report Share Posted November 13, 2018 This is interesting, it appears to be coming from the 'Action History Chart' widget, does this happen every time you search for an account? Quote Link to post Share on other sites
Keyoung 3 Posted November 14, 2018 Author Report Share Posted November 14, 2018 No, just "some accounts". Most of the accounts can be searched but just some accounts cannot be searched properly. Quote Link to post Share on other sites
Noime 7 Posted November 15, 2018 Report Share Posted November 15, 2018 That's a really old bug with X2. It has to do with how some GROUP-BY statements are constructed by the framework, and the problem can raise its head at all different locations. I silenced the problem by activating some backward-compatibility-options for the mysql daemon. My mysqld.cnf for mysql-version 5.7 has the following lines in the [mysqld] section : # # this is to silence X2 group-by errors # sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Older mysql versions don't know "sql_mode", but instead have just "mode". In that case the line must look like this. From memory, must be tested. mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 1 Quote Link to post Share on other sites
Keyoung 3 Posted November 16, 2018 Author Report Share Posted November 16, 2018 It makes no difference after I put the above sql_mode into my.cnf like this and restarted MySQL: MySQL version 5.7.24 $ cat my.cnf## The MySQL database server configuration file.## You can copy this to one of:# - "/etc/mysql/my.cnf" to set global options,# - "~/.my.cnf" to set user-specific options.# # One can use all long options that the program supports.# Run program with --help to get a list of available options and with# --print-defaults to see which it would actually understand and use.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html ## * IMPORTANT: Additional settings that can override those from this file!# The files must end with '.cnf', otherwise they'll be ignored.# !includedir /etc/mysql/conf.d/!includedir /etc/mysql/mysql.conf.d/ [mysqld]## this is to silence X2 group-by errors# sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION X2CRM error:Here's a quick list of what will be included in the report:Error Code: 500Error Message: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'x2crm.x2_actions.complete' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byStack Trace: [click to toggle display]CDbException in /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(941)#0 /var/www/html/x2crm/framework/db/CDbCommand.php(396): CDbCommand->queryInternal('fetchAll', Array, Array)#1 /var/www/html/x2crm/protected/components/sortableWidget/recordViewWidgets/ActionHistoryChartWidget.php(122): CDbCommand->queryAll()#2 /var/www/html/x2crm/protected/components/sortableWidget/recordViewWidgets/ActionHistoryChartWidget.php(303): ActionHistoryChartWidget::getChartData('1392184795', 1542524057, '14', 'accounts', NULL)#3 /var/www/html/x2crm/protected/components/sortableWidget/recordViewWidgets/ActionHistoryChartWidget.php(140): ActionHistoryChartWidget->getInitialChartData('1392184795', '14', 'Accounts', NULL)#4 /var/www/html/x2crm/framework/base/CComponent.php(111): ActionHistoryChartWidget->getSetupScript()#5 /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(941): CComponent->__get('setupScript')#6 /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(909): SortableWidget->renderWidgetContents()#7 /var/www/html/x2crm/protected/components/sortableWidget/views/recordViewWidget.php(42): SortableWidget->renderWidget()#8 /var/www/html/x2crm/framework/web/CBaseController.php(131): require('/var/www/html/x...')#9 /var/www/html/x2crm/framework/web/CBaseController.php(96): CBaseController->renderInternal('/var/www/html/x...', Array, false)#10 /var/www/html/x2crm/protected/components/X2Widget.php(81): CBaseController->renderFile('/var/www/html/x...', Array, false)#11 /var/www/html/x2crm/framework/web/widgets/CWidget.php(244): X2Widget->renderFile('/var/www/html/x...', Array, false)#12 /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(1054): CWidget->render('application.com...', Array)#13 /var/www/html/x2crm/protected/controllers/x2base.php(831): SortableWidget->run()#14 /var/www/html/x2crm/protected/components/sortableWidget/SortableWidget.php(396): x2base->widget('application.com...', Array)#15 /var/www/html/x2crm/protected/components/sortableWidget/RecordViewWidgetManager.php(124): SortableWidget::instantiateWidget('ActionHistoryCh...', Object(Profile), 'recordView', Array)#16 /var/www/html/x2crm/protected/components/sortableWidget/views/_twoColumnSortableWidgetLayout.php(53): RecordViewWidgetManager->displayWidgets(1)#17 /var/www/html/x2crm/framework/web/CBaseController.php(131): require('/var/www/html/x...')#18 /var/www/html/x2crm/framework/web/CBaseController.php(96): CBaseController->renderInternal('/var/www/html/x...', NULL, false)#19 /var/www/html/x2crm/protected/components/X2Widget.php(81): CBaseController->renderFile('/var/www/html/x...', NULL, false)#20 /var/www/html/x2crm/framework/web/widgets/CWidget.php(244): X2Widget->renderFile('/var/www/html/x...', NULL, false)#21 /var/www/html/x2crm/protected/components/sortableWidget/TwoColumnSortableWidgetManager.php(108): CWidget->render('_twoColumnSorta...')#22 /var/www/html/x2crm/protected/controllers/x2base.php(831): TwoColumnSortableWidgetManager->run()#23 /var/www/html/x2crm/protected/components/X2WidgetList.php(79): x2base->widget('RecordViewWidge...', Array)#24 /var/www/html/x2crm/protected/controllers/x2base.php(831): X2WidgetList->run()#25 /var/www/html/x2crm/protected/modules/accounts/views/accounts/view.php(144): x2base->widget('X2WidgetList', Array)#26 /var/www/html/x2crm/framework/web/CBaseController.php(127): require('/var/www/html/x...')#27 /var/www/html/x2crm/framework/web/CBaseController.php(96): CBaseController->renderInternal('/var/www/html/x...', Array, true)#28 /var/www/html/x2crm/framework/web/CController.php(869): CBaseController->renderFile('/var/www/html/x...', Array, true)#29 /var/www/html/x2crm/protected/controllers/x2base.php(729): CController->renderPartial('view', Array, true)#30 /var/www/html/x2crm/protected/controllers/x2base.php(265): x2base->render('view', Array)#31 /var/www/html/x2crm/protected/modules/accounts/controllers/AccountsController.php(138): x2base->view(Object(Accounts), 'accounts')#32 [internal function]: AccountsController->actionView('14')#33 /var/www/html/x2crm/framework/web/actions/CAction.php(109): ReflectionMethod->invokeArgs(Object(AccountsController), Array)#34 /var/www/html/x2crm/framework/web/actions/CInlineAction.php(47): CAction->runWithParamsInternal(Object(AccountsController), Object(ReflectionMethod), Array)#35 /var/www/html/x2crm/framework/web/CController.php(308): CInlineAction->runWithParams(Array)#36 /var/www/html/x2crm/framework/web/filters/CFilterChain.php(133): CController->runAction(Object(CInlineAction))#37 /var/www/html/x2crm/protected/controllers/x2base.php(579): CFilterChain->run()#38 /var/www/html/x2crm/framework/web/filters/CInlineFilter.php(58): x2base->filterSetPortlets(Object(CFilterChain))#39 /var/www/html/x2crm/framework/web/filters/CFilterChain.php(130): CInlineFilter->filter(Object(CFilterChain))#40 /var/www/html/x2crm/framework/web/filters/CFilter.php(40): CFilterChain->run()#41 /var/www/html/x2crm/framework/web/filters/CFilterChain.php(130): CFilter->filter(Object(CFilterChain))#42 /var/www/html/x2crm/framework/web/filters/CFilter.php(40): CFilterChain->run()#43 /var/www/html/x2crm/framework/web/filters/CFilterChain.php(130): CFilter->filter(Object(CFilterChain))#44 /var/www/html/x2crm/framework/web/CController.php(291): CFilterChain->run()#45 /var/www/html/x2crm/framework/web/CController.php(265): CController->runActionWithFilters(Object(CInlineAction), Array)#46 /var/www/html/x2crm/framework/web/CWebApplication.php(282): CController->run('view')#47 /var/www/html/x2crm/protected/components/X2WebApplication.php(63): CWebApplication->runController('accounts/accoun...')#48 /var/www/html/x2crm/framework/base/CApplication.php(185): X2WebApplication->processRequest()#49 /var/www/html/x2crm/index.php(65): CApplication->run()#50 {main}X2Engine Version: 7.0PHP Version: 7.0.32-4+ubuntu18.04.1+deb.sury.org+1 Quote Link to post Share on other sites
Keyoung 3 Posted November 20, 2018 Author Report Share Posted November 20, 2018 Does anyone have any update on this?Many thanks in advance! Quote Link to post Share on other sites
X2Peter 43 Posted November 20, 2018 Report Share Posted November 20, 2018 Hello Keyoung, We are currently trying to replicate this error in our own environment in order to derive the solution. I will update this thread with information as it comes in. Quote Link to post Share on other sites
X2Peter 43 Posted November 20, 2018 Report Share Posted November 20, 2018 Hello Keyoung, We were able to replicate the issue and are working on a fix for our next release. In the meantime please follow Noime's advice and set your sql_mode string in your my.cnf file to the following: sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Also, please make sure to restart your MySQL server afterwards to allow the settings to take effect. Quote Link to post Share on other sites
Keyoung 3 Posted November 21, 2018 Author Report Share Posted November 21, 2018 Yes, problem solved!Previously Noime's solution has no quotes for the items after sql_mode and now it works with this:sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Thank you for all the help! Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.