Incompatibility with MySQL 5.7 (Ubuntu 16.04)

[gigo6000]

In mysql 5.7 it's required to have the order by fields to be present in the select statement when using DISTINCT. This is because the ONLY_FULL_GROUP_BY setting is turned on by default. I couldn't find where to fix this but it seems to be related to the sfPager or sfDoctrinePager, and I'm sure there are other places in the code this could happen.

Here's part of the trace of error:

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'time_dev.u3.bio' which is not in SELECT list; this is incompatible with DISTINCT. Failing Query: "SELECT DISTINCT u3.id, SUM(u4.value) AS u4__0 FROM user u3 INNER JOIN user_skill u4 ON u3.id = u4.user_id WHERE u3.first_name IS NOT NULL AND u3.cache_thumbs >= 0 AND u3.id IN (1, 14, 22, 35, 58, 79, 87, 91, 97, 101, 125, 178, 190, 235, 261, 267, 277, 280, 287, 341, 410, 438, 483, 488, 489, 492, 496, 510, 526, 562, 573, 579, 1085, 1134, 1147, 1193, 1195, 1285, 1542, 1558, 1643, 1664, 1842, 1843, 2206, 2268, 2387, 2442, 2475, 2491, 2566, 2678, 2714, 3059, 3202, 3258, 3677, 3723, 4176, 4177, 4183, 4187, 4189, 4230, 4244, 4271, 4288, 4322, 4335, 4361, 4362, 4372, 4374, 4388, 4402, 4407, 4419, 4443, 4444, 4450, 4465, 4475, 4484, 4488, 4489, 4510, 4517, 4531, 4538, 4548, 4566, 4573, 4617, 4618, 4621, 4622, 4624, 4631, 4638, 4653, 4664, 4669, 4672, 4673, 4680, 4681, 4690, 4695, 4723, 4738, 4739, 4745, 4772, 4779, 4789, 4796, 4812, 4821, 4822, 4823, 4827, 4847, 4860, 4871, 4877, 4880, 4887, 4913, 4921, 4923, 4944, 4983, 4984, 4987, 5002, 5004, 5007, 5011, 5019, 5021, 5023, 5028, 5029, 5036, 5043, 5056, 5065, 5083, 5095, 5096, 5116, 5137, 5155, 5195, 5204, 5214, 5221, 5231, 5239, 5274, 5275, 5287, 5293, 5299, 5306, 5344, 5345, 5346, 5348, 5354, 5355, 5358, 5359, 5371, 5374, 5394, 5395, 5403, 5408, 5429, 5430, 5434, 5447, 5464, 5474, 5481, 5497, 5514, 5520, 5550, 5551, 5556, 5557, 5558, 5567, 5569, 5610, 5622, 5629, 5633, 5636, 5640, 5641, 5646, 5650, 5674, 5677, 5684, 5696, 5697, 5702, 5714, 5728, 5731, 5735, 5747, 5750, 5761) AND u4.value >= 1 AND u4.skill_id IN (1, 2, 3, 4, 5, 6, 7, 31, 39, 51, 52, 54) GROUP BY u3.username ORDER BY CASE WHEN u3.bio IS NULL OR u3.bio='' THEN 1 ELSE 0 END, u4__0 DESC LIMIT 20"

As a workaround I added

[mysqld]
sql-mode=""

to the my.cnf file and it solves the issue, but I guess that's not a very permanent solution.

[j0k3r]

Yeah this is also sth that happen on Doctrine 2.
The only workaround from now is what you said.
And I think it'll be hard to fix the problem inside doctrine1 😕

[endelwar]

And I think it'll be hard to fix the problem inside doctrine1

not that hard: I've already sent a couple of patch to zikula/doctrine1 on november 2015 (see zikula/doctrine1#7 and zikula/doctrine1#8), I can recreate the PR on LExpress/doctrine1 if you wish

[gigo6000]

@endelwar I was actually talking about a change in Doctrine to comply with the ONLY_FULL_GROUP_BY restriction, but I guess setting the sql-mode from Doctrine can be another workaround.

[endelwar]

A rewrite of all queries that incur in ONLY_FULL_GROUP_BY restriction is a (too big?) big refactor for doctrine1.
I ended with using the proposed patch for working around the issue.

[endelwar]

I've just submitted PR #29 for setting sql mode