Error when duplicate product - MySQL 5.7

[rflx]

When I try to copy (duplicate) a product it generates following error:

An exception occurred while executing 'SELECT c1.*, MAX(c2.sorting) AS max_sorting FROM tl_iso_product_category c1 LEFT JOIN tl_iso_product_category c2 ON c1.page_id=c2.page_id WHERE c1.pid=17 GROUP BY c1.page_id': SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cto_inlain.c1.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Edit: this error happens also when i try to open the Integrity Check.

Isotope 2.5.8
Contao 4.6.6
MySQL 5.7.23

[rflx]

I've found out that this is an MySQL 5.7 issue.
Due that MySQL 5.7 is now defaulted to ONLY_FULL_GROUP_BY turned on (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by). Which means its a two-step group and MySQL needs to know which group should be shown first.

Workaround: if you are using MySQL 5.7 you could try to turn off ONLY_FULL_GROUP_BY in your mysql.cnf config by removing it in sql_mode:

mysql.cnf:

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[fritzmg]

Similar issues:

• Broken with SQL Mode "ONLY_FULL_GROUP_BY" #1886
• export does not work under MySQL 5.7.5+ terminal42/contao-leads#49
• Newsletter::send does not work with MySQL 5.7.5+ contao/newsletter-bundle#9