Announcement

#1 2016-07-03 06:13:34

Mercury_LABBS
Member
Luton. UK
2016-07-02
2

Workaround for SELECT DISTINCT... ORDER BY.. errors for MySQL v5.7.?

MySQL appears to have changed its default behavior with newer versions, so that when using a SELECT DISTINCT statement with ORDER BY there is now a requirement that the column(s) used in the ORDER BY have to be referenced in the SELECT DISTINCT part of the SQL statement.

This behavior appears to be controlled by the MySQL Server SQL Mode variable ONLY_FULL_GROUP_BY, see < http://dev.mysql.com/doc/refman/5.7/en/ … l_group_by >, < http://stackoverflow.com/questions/3469 … y-in-mysql >, and < http://stackoverflow.com/questions/5391 … -statement > for more information.

One workaround is to place additional code in the  function pwg_db_connect block (near the beginning of functions_mysqli.inc.php) that queries the current server flags/mode, and then sets the server flags/mode for the session so that ONLY_FULL_ORDER_BY is not used.

Here is a diff of my additions against the Piwigo v2.8.1 source in ./include/dblayer/functions_mysqli.inc.php,

Code:

76,94d75
<
<   ### [START] Set Global Server variable 'ONLY_FULL_GROUP_BY' off for the session.
<   # Get current SQL Mode from server
<   ($result = $mysqli->query('SELECT @@SESSION.sql_mode;')) or my_error('SELECT @@SESSION.sql_mode;', $conf['die_on_sql_error']);
<   $sql_mode_result = $result->fetch_assoc();
<   $sql_mode = $sql_mode_result['@@SESSION.sql_mode'];
<   # Strip ONLY_FULL_GROUP_BY from result
<   $sql_mode = str_replace('ONLY_FULL_GROUP_BY', '', $sql_mode);
<   # Remove any leading ',' in the instance that ONLY_FULLGROUP_BY is the first parameter in the list reported
<   trim ($sql_mode, ',');
<   # Remove any trailing ',' in the instance that ONLY_FULLGROUP_BY is the last parameter in the list reported
<   rtrim ($sql_mode, ',');
<   # Ensure there is no superflous comma, in the instance that ONLY_FULL_GROUP_BY is in the middle of the list reported
<   $sql_mode = str_replace(',,', ',', $sql_mode);
<   # Prepend the command to set the desired runtime environment
<   $sql_mode = "SET SESSION sql_mode='".$sql_mode."';";
<   # Finally Set SQL Mode without 'ONLY_FULL_GROUP_BY'
<   ($result = $mysqli->query($sql_mode)) or my_error($sql_mode, $conf['die_on_sql_error']);
<   ### [END] Set Global Server variable 'ONLY_FULL_GROUP_BY' off for the session.
95a77
>
152d133
<

I hope this helps all of you that are having similar issues.

---
Piwigo version: 2.8.1
PHP version: 7.0.4-7ubuntu2.1
MySQL version: 5.7.12-0ubuntu1.1 - (Ubuntu)
Piwigo URL: *** NON Production ***
---
Further discussion can be found on the forums here at < http://piwigo.org/forum/viewtopic.php?id=26696 >

Offline

 

#2 2016-07-13 18:08:55

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13573

Re: Workaround for SELECT DISTINCT... ORDER BY.. errors for MySQL v5.7.?

Hi Mercury_LABBS,

Thank you, I'm going to try this.

Offline

 

#3 2016-07-26 16:00:58

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13573

Re: Workaround for SELECT DISTINCT... ORDER BY.. errors for MySQL v5.7.?

Hi,

I rewrote it a "cleaner" (in my opinion ;-) way on [Github] Piwigo commit b87094ac but that makes the same change. For me it works fine on a MySQL 5.7 with default settings on Ubuntu 16.04.

Offline

 

Board footer

Powered by FluxBB

github twitter newsletter Donate Piwigo.org © 2002-2022 · Contact