Family grid and ONLY_FULL_GROUP_BY mysql mode

[Jean-Beru]

Mysql 5.7 and above have "only_full_group_by mode" enabled by default (see http://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-recognizing-functional-dependencies-enabled-by-default/).

Since then, family grid doesn't show due to an mysql error during an ajax call (datagrid/family-grid/load) :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #9 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pimkie-pim.p1_.label' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by") in PimDataGridBundle:Datagrid:load.json.twig at line 3.

This mode can be disabled with SET GLOBAL sql_mode="..." but I thought it could be fixed or specifed in doc.

[nidup]

Hi @Jean-Beru,

Thank you for your feedback, we really appreciate it!

Really great to see that Mysql now enforces this kind of detection, if you want help us and contribute to Akeneo PIM don't hesitate to write a Pull Request, we'll be glad to review and merge it.

The patch should be applied in this query https://github.com/akeneo/pim-community-dev/blob/1.4/src/Pim/Bundle/CatalogBundle/Doctrine/ORM/Repository/FamilyRepository.php#L147 and you can do a PR on v1.4 branch.

Please notice that, to allow us to merge, you'll need to sign our contributor agreement http://www.akeneo.com/contributor-license-agreement".

If you're not interested in contributing, please let us now and we'll fix the issue.

[Jean-Beru]

It will be a pleasure to do a PR on it but I have some difficulties to run Behat tests on my machine. I have to fix it before...

[nidup]

No problem, if you write the PR with the fix only and manually test it, I will be happy to run the PR for you on our Continuous Integration to execute all the Behat. BTW, run all the Behat scenario on your own machine could takes ~15 hours (we have a very special setup of our CI with parallelisation and lot of containers to run it in 30 min).

[Jean-Beru]

Hmmmm... This bug could be resolved by using

• ANY_VALUE() for each joined field in SELECT statement (https://github.com/akeneo/pim-community-dev/blob/1.4/src/Pim/Bundle/CatalogBundle/Doctrine/ORM/Repository/FamilyRepository.php#L147) but it exists only in MSQL (since 5.7).
• More relevant GROUP BY generated by ORM pager (https://github.com/Jean-Beru/pim-community-dev/blob/21837e99c38f528b857f88f79e92c2b0149c2065/src/Pim/Bundle/DataGridBundle/Extension/Pager/Orm/Pager.php#L102) but it depends on original query. You have to add all joined fields in GROUP BY statement.

There isn't a "simple way" to do that. So, I think that the best solution is to add documentation in "Known issues" (http://docs.akeneo.com/1.4/developer_guide/installation/installation_workstation.html#known-issues).

[Jean-Beru]

OKay, finally I found a "very easy" solution...

[nidup]

Your PR has been merged, thank you again!

Did you encountered any other issue related to Mysql 5.7?

[Jean-Beru]

No, I didn't encounter other issues when I was working on it.

But I removed the ONLY_FULL_GROUP_BY mode, so some bugs may still exists.

[nidup]

Ok, I close the ticket, don't hesitate to open a new ticket if you encounter another issue.