Database failure - product view

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

What can I do to solve this problem?

Brand new installation 7.11.4
Create product -> enter these values Product Name, Product Type, Cost, Price, Description -> Save

Get an error

Database failure. Please refer to suitecrm.log for details.

I have disabled
Admin -> System Settings -> Configure AJAX User Interface Information.
Disabled modules -> Products

If I go to Products module -> klick on a product name in list view -> the “product” detail view -> below there is an error

Database failure. Please refer to suitecrm.log for details.

Every time I get the same error output in the suitecrm.log

— begin suitecrm.log —

Wed May 15 11:11:46 2019 [21063][1][FATAL] Mysqli_query failed.
Wed May 15 11:11:46 2019 [21063][1][FATAL] Error running count query for AOS_Products List:  Query Failed: (
 			SELECT count(*) c FROM (
 				SELECT
					aos_quotes.*,
					accounts.id AS account_id,
					accounts.name AS billing_account,

					opportunity_id AS opportunity,
					billing_contact_id AS billing_contact,
					'' AS created_by_name,
					'' AS modified_by_name,
					'' AS assigned_user_name
				FROM
					aos_products

				JOIN aos_products_quotes ON aos_products_quotes.product_id = aos_products.id AND aos_products.id = '78546d45-4565-bb77-669e-5cdbf302abbf' AND aos_products_quotes.deleted = 0 AND aos_products.deleted = 0
				JOIN aos_quotes ON aos_quotes.id = aos_products_quotes.parent_id AND aos_quotes.stage = 'Closed Accepted' AND aos_quotes.deleted = 0
				JOIN accounts ON accounts.id = aos_quotes.billing_account_id -- AND accounts.deleted = 0

				GROUP BY accounts.id
			) AS aos_quotes

		): MySQL error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'crm6fitm_SuiteCRM.aos_quotes.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Wed May 15 11:11:46 2019 [21063][1][FATAL] Exception handling in /home/crm6fitmedia/public_html/include/MVC/Controller/SugarController.php:400
Wed May 15 11:11:46 2019 [21063][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
Wed May 15 11:11:46 2019 [21063][1][FATAL] backtrace:
#0 /home/crm6fitmedia/public_html/include/database/DBManager.php(353): sugar_die('Database failur...')
#1 /home/crm6fitmedia/public_html/include/database/DBManager.php(328): DBManager->registerError('Error running c...', 'Error running c...', true)
#2 /home/crm6fitmedia/public_html/include/database/MysqliManager.php(179): DBManager->checkError('Error running c...', true)
#3 /home/crm6fitmedia/public_html/data/SugarBean.php(1504): MysqliManager->query('(\n \t\t\tSELECT co...', true, 'Error running c...')
#4 /home/crm6fitmedia/public_html/data/SugarBean.php(1255): SugarBean->_get_num_rows_in_query('(\n \t\t\tSELECT co...', false)
#5 /home/crm6fitmedia/public_html/data/SugarBean.php(1017): SugarBean->process_union_list_query(Object(AOS_Products), '(\n \t\t\tSELECT * ...', '', -1, 10, '', Object(aSubPanel), '(\n \t\t\tSELECT co...', Array)
#6 /home/crm6fitmedia/public_html/include/ListView/ListView.php(1151): SugarBean::get_union_related_list(Object(AOS_Products), 'aos_quotes.id a...', 'asc', '', '', -1, 10, '', Object(aSubPanel))
#7 /home/crm6fitmedia/public_html/include/ListView/ListViewSubPanel.php(142): ListView->processUnionBeans(Object(AOS_Products), Object(aSubPanel), 'aos_products_pu...', false)
#8 /home/crm6fitmedia/public_html/include/SubPanel/SubPanel.php(220): ListViewSubPanel->process_dynamic_listview('AOS_Products', Object(AOS_Products), Object(aSubPanel), false)
#9 /home/crm6fitmedia/public_html/include/SubPanel/SubPanel.php(236): SubPanel->ProcessSubPanelListView('include/SubPane...', Array, false)
#10 /home/crm6fitmedia/public_html/include/SubPanel/SubPanelViewer.php(96): SubPanel->display(false)
#11 /home/crm6fitmedia/public_html/include/MVC/Controller/SugarController.php(1087): require_once('/home/crm6fitme...')
#12 /home/crm6fitmedia/public_html/include/MVC/Controller/SugarController.php(468): SugarController->handleActionMaps()
#13 /home/crm6fitmedia/public_html/include/MVC/Controller/SugarController.php(373): SugarController->process()
#14 /home/crm6fitmedia/public_html/include/MVC/SugarApplication.php(113): SugarController->execute()
#15 /home/crm6fitmedia/public_html/index.php(52): SugarApplication->execute()
#16 {main}

— end suitecrm.log —

2

I know this is an old post but I hate it when questions go unanswered and it’s apparently is still an issue in Version 7.10.27. So just in case anyone else finds this question, the answer can be found in the following post. It looks like a SQL query rewrite should probably be done due to the default strict grouping function in MySQL but until then you can turn off (remove) ‘ONLY_FULL_GROUP_BY’ in the SQL_MODE variable like I did and it will start working.

https://community.suitecrm.com/t/ajax-error-on-product-details/63310

Also make sure you’re using a supported version of MySQL.
https://docs.suitecrm.com/admin/compatibility-matrix/

2 Likes