Jump to content
X2Community Forums

Error while trying to search an existing account


Recommended Posts

I encountered below error while trying to search an existing account:

 

Error 500
It 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 Report
Here's a quick list of what will be included in the report:

Error Code: 500
Error 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_by
Stack 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.0
PHP Version: 7.0.32-4+ubuntu18.04.1+deb.sury.org+1

 

Link to post
Share on other sites

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"
  • Like 1
Link to post
Share on other sites

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: 500
Error 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_by
Stack 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.0
PHP Version: 7.0.32-4+ubuntu18.04.1+deb.sury.org+1

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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!

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...