Problem with get products list by taxon on MySQL 5.7

[4oXdev]

When I testing Sylius with mysql 5.7, there is an error in Reports:

request.INFO: Matched route "sylius_shop_product_index". {"route":"sylius_shop_product_index","route_parameters":{"_controller":"sylius.controller.product:indexAction","_sylius":{"template":"@SyliusShop/Product/index.html.twig","grid":"sylius_shop_product"},"slug":"xxxx","_route":"sylius_shop_product_index"},"request_uri":"http://xxxx/taxons/xxxx","method":"GET"} []

request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'SELECT s0_.code AS code_0, s0_.available_on AS available_on_1, s0_.available_until AS available_until_2, s0_.created_at AS created_at_3, s0_.updated_at AS updated_at_4, s0_.enabled AS enabled_5, s0_.id AS id_6, s0_.variant_selection_method AS variant_selection_method_7, s0_.average_rating AS average_rating_8, s1_.name AS name_9, s1_.slug AS slug_10, s1_.description AS description_11, s1_.meta_keywords AS meta_keywords_12, s1_.meta_description AS meta_description_13, s1_.id AS id_14, s1_.short_description AS short_description_15, s1_.locale AS locale_16, s0_.main_taxon_id AS main_taxon_id_17, s1_.translatable_id AS translatable_id_18 FROM sylius_product s0_ LEFT JOIN sylius_product_translation s1_ ON s0_.id = s1_.translatable_id AND (s1_.locale = ?) INNER JOIN sylius_product_variant s2_ ON s0_.id = s2_.product_id INNER JOIN sylius_channel_pricing s3_ ON s2_.id = s3_.product_variant_id INNER JOIN sylius_product_taxon s4_ ON s0_.id = s4_.product_id INNER JOIN sylius_taxon s5_ ON s4_.taxon_id = s5_.id INNER JOIN sylius_taxon_translation s6_ ON s5_.id = s6_.translatable_id WHERE s6_.locale = ? AND s6_.slug = ? AND EXISTS (SELECT 1 FROM sylius_product_channels s7_ INNER JOIN sylius_channel s8_ ON s7_.channel_id = s8_.id WHERE s7_.product_id = s0_.id AND s8_.id IN (?)) AND s0_.enabled = 1 AND s3_.channel_id = ? GROUP BY s0_.id ORDER BY s4_.position ASC LIMIT 10 OFFSET 0' with params ["en_US", "en_US", "aem-induction", 1, 1]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 's4_.position' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

[pamil]

What MySQL version are you using?

[4oXdev]

MySQL 5.7.15

[c-revillo]

We managed to solve this by changing a setting in the my.cnf file like this

[mysqld]
sql-mode=""

My teammate @belaustegui knows the details better.

[4oXdev]

I can turn off only_full_group_by mode. It is right way?
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

[koemeet]

Yes this is the right way, I use the TRADITIONAL sql mode in production myself.

[psihius]

This is a change in MySQL, and going forward this will be required to change one or the other way. Otherwise people, who are not able to configure the mysql server itself, will not be able to run Sylius (and for that matter, anything else that has this kind of ORDER BY usage). I read a bit on it, for now I just also reconfigured mysql to run in less strict mode, but this is the new default, and solving it will be an ongoing issue.

[deniskrasilnikov]

@psihius fully agree. We are building an e-commerce with 1.0-beta + mysql 5.7 and faced this issue. Years of experience told me clearly, that as more explicit (non 'guessable') and strict behavior the system has in each of its part, more stable and bulletproof it is general. So hoping that Sylius will match the new mysql behavior for the first release :)

[damonsson]

That problem which people are reporting more and more often (mysql 8 released) can be solved in 2 ways. Either modify actual SQL query (join finally product not variant), or add a relation between channel pricing and product (not just product variant). @pamil let me know what you think and what would be better for the application. I will try to fix it.

[lsmith77]

ping

[lchrusciel]

@lsmith77 did you encounter this problem recently, after update to newest master? Can you check if this line is relevant? https://github.com/Sylius/Sylius/pull/10070/files#r246342388

[lsmith77]

I used the install docs today to setup a sylius standard via docker and found the old tickets when I debugged the exception I got.

[lsmith77]

but yeah I assume removing distinct there will remove that exception. I can try to test later.

[lsmith77]

@lchrusciel yes .. reverting #10070 fixes the exception

[lchrusciel]

I knew it was too simple... Good to know before 1.4 release :D