SQL mode "ONLY_FULL_GROUP_BY" causes errors

[liam-wiltshire]

Ran into a bit of an odd issue today - set up a new server with Percona, using the server default Magmi wouldn't import, mySQL errors with "Expression #2 of SELECT list is not in GROUP BY clause"

After doing a bit of digging, it turns out that "ONLY_FULL_GROUP_BY" is set as a sql_mode by default, which causes errors.

Setting sql_mode to "" (or otherwise removing ONLY_FULL_GROUP_BY) works.

I will have a dig and see if I can submit a fix at some point, but in the meantime, it might be a useful fyi for someone.

[limolitz]

We are having a similar issue. The actual query which raises the exception in our case is in otfindexer.php#L147. The exception is: PDOException occurred Message: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'magento1.ccev.value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[mjh1709muc]

This fixed it for me: $sql = "SELECT cce.entity_id as catid, COALESCE(ANY_VALUE(ccev.value),ANY_VALUE(ccevd.value)) as value

[mirill]

Thanks, mjh1709muc, this saved my day.