Dupcheck: "incompatible with sql_mode=only_full_group_by" error and fix

[Stian Lund]

Hi all,

I've been using the "dupcheck" module for a long time, and not sure when it broke - my host recently did a move to a new host but the MySQL host is the same (version 5.7.38).

When going to the dupcheck page it errored with:

"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ... which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The sql_mode is the default for MySQL installs and not really recommended to override it even if you could. I was able to find some docs on this problem here:

https://dev.mysql.com/doc/refman/8.3/en/group-by-handling.html

After skimming through that and a *lot* of fiddling (I have little clue about PHP+SQL), I think I found a solution using ANY_VALUE(). Line 56 in controllers/dupcheck.php (the first ->select statement) has to be changed like this:

      ->select(array("item_id" => "ANY_VALUE(\"item_id\")"))

This will (far as I can tell...) do a "SELECT ANY_VALUE(item_id) AS item_id".

You need the AS to work with the $row->item_id down below.

Here's the commit:

https://github.com/Pathduck/gallery3-themes-modules/commit/a69b9f154fb198e8446883aa3b9d273e1fce8424

Mainly putting this up here in case it helps others who run into the same problem with this module. Also would like some input if at all possible if this could've been done more elegantly - I have no clue about the db::build stuff in G3 so just copied what was done on the line below it 😎

cheers,

Stian