MySQL error 1055: '.....p.products_type' isn't in GROUP BY on MariaDB

[torvista]

php 8.2.8 MySQL Mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

Did an upgrade to the current main.

[27-Jul-2023 11:42:22 Europe/Madrid] Shopfront Navigation: [0][index] Request URI: /tienda/ajax.php?act=ajaxInstantSearch&method=instantSearch IP address: 127.0.0.1, Language id 2

0 [internal function]: zen_debug_error_handler()

1 includes/classes/db/mysql/query_factory.php(714): trigger_error()

2 includes/classes/db/mysql/query_factory.php(659): queryFactory->show_error()

3 includes/classes/db/mysql/query_factory.php(299): queryFactory->set_error()

4 zc_plugins/InstantSearch/v4.0.1/classes/SearchEngineProviders/MysqlSearchEngineProvider.php(173): queryFactory->Execute()

5 zc_plugins/InstantSearch/v4.0.1/classes/SearchEngineProviders/MysqlSearchEngineProvider.php(96): Zencart/Plugins/Catalog/InstantSearch/SearchEngineProviders/MysqlSearchEngineProvider->searchProducts()

6 zc_plugins/InstantSearch/v4.0.1/classes/InstantSearch.php(66): Zencart/Plugins/Catalog/InstantSearch/SearchEngineProviders/MysqlSearchEngineProvider->search()

7 includes/classes/ajax/zcAjaxInstantSearch.php(166): Zencart/Plugins/Catalog/InstantSearch/InstantSearch->runSearch()

8 ajax.php(92): zcAjaxInstantSearch->instantSearch()

--> PHP Fatal error: MySQL error 1055: 'DB_NAME.p.products_type' isn't in GROUP BY :: SELECT p.*, pd.products_name, m.manufacturers_name, SUM(cpv.views) AS total_views FROM products p JOIN products_description pd ON (p.products_id = pd.products_id) LEFT JOIN manufacturers m ON (m.manufacturers_id = p.manufacturers_id) LEFT JOIN count_product_views cpv ON ( p.products_id = cpv.product_id AND cpv.language_id = 2 ) WHERE p.products_status <> 0
AND p.products_model = 'gipro' AND pd.language_id = 2 AND p.products_id NOT IN (0) GROUP BY p.products_id, pd.products_name, m.manufacturers_name ORDER BY total_views DESC, p.products_sort_order, pd.products_name LIMIT 15 ==> (as called by) ...\tienda\zc_plugins\InstantSearch\v4.0.1\classes\SearchEngineProviders\MysqlSearchEngineProvider.php on line 173 <== in includes/classes/db/mysql/query_factory.php on line 714.

Since it's using p., I'm not sure how to fix this....p. is really necessary?

Removing ONLY_FULL_GROUP_BY allows it to proceed (to the next problem!).

[marco-pm]

I have the same ONLY_FULL_GROUP_BY sql mode but I'm not getting this error. It could be because of some particular values of your products table.

For now try changing the GROUP BY clause to

GROUP BY
    p.products_id,
    pd.products_name,
    m.manufacturers_name,
    p.products_type

and see if it works

[torvista]

I also have define('STRICT_ERROR_REPORTING', true);

Once I add p.products_type, I get the same error for p.products_quantity. Once I add p.products_quantity, I get the same error for p.products_model... I assume it's going to ask for all the columns since the query is using p.* instead of specific fields.

[marco-pm]

I also have define('STRICT_ERROR_REPORTING', true);

Once I add p.products_type, I get the same error for p.products_quantity. Once I add p.products_quantity, I get the same error for p.products_model... I assume it's going to ask for all the columns since the query is using p.* instead of specific fields.

Yeah, as I suspected unfortunately... Most of the product fields are necessary later, when displaying results, that's why I wrote p.*. I could select the specific fields instead – that is also a good practice in general – and then add them to the GROUP BY cause. Maybe try this way for now. But I still want to understand why are you getting this error while I'm not. I'll think about this.

[torvista]

It's a local install on Windows? MySQL 10.6.5-MariaDB? from version info: error_log_mode 0644 0644 error_reporting 32767 32767

[marco-pm]

It's a local install on Windows? MySQL 10.6.5-MariaDB? from version info: error_log_mode 0644 0644 error_reporting 32767 32767

Thanks to your version info note I figured it could be related to MariaDB, as I was using MySQL 8 instead. I therefore tried with MariaDB and got the same error as you. It turns out that in some cases the ONLY_FULL_GROUP_BY mode is handled differently in MySQL than in MariaDB 😓

I rewrote the query to avoid this error. I ran the tests and it seems to work fine. Maybe you can take it for a spin if you want, it should work now.

[torvista]

Works correctly now, thanks!