Piwigo.org

rbr28 · 2016-06-29 15:37:11

Seems like there are a lot of problems with the Piwigo code and mysql 5.7.11. Everything was working fine before I upgraded from Fedora 23 to Fedora 24 and everything seems to point to the MySQL version being the problem.

Here's the first error that appears on most pages.:

Warning: [mysql error 3065] Expression #1 of ORDER BY clause is not in SELECT list, references column 'piwigo.piwigo_images.date_available' which is not in SELECT list; this is incompatible with DISTINCT

SELECT DISTINCT(image_id)
FROM piwigo_image_category
INNER JOIN piwigo_images ON id = image_id
WHERE
category_id = 1

ORDER BY date_available DESC
; in /piwigo/include/dblayer/functions_mysqli.inc.php on line 832

Fatal error: Call to a member function fetch_assoc() on boolean in /piwigo/include/dblayer/functions_mysqli.inc.php on line 893

When I use set sql_mode="" I then get the following error:

Warning: [mysql error 1146] Table 'performance_schema.session_variables' doesn't exist
SHOW VARIABLES LIKE 'max_allowed_packet' in /piwigo/include/dblayer/functions_mysqli.inc.php on line 832

Fatal error: Call to a member function fetch_row() on boolean in /piwigo/include/dblayer/functions_mysqli.inc.php on line 218

These are critical errors, making the photo gallery completely unusable. Any help would be appreciated.

Thanks,
Vern

Fedora 24
Piwigo version: 2.8.1
PHP version: 5.6.22
MySQL version: 5.7.11
Piwigo URL: http://vw.homelinux.net/z

rbr28 · 2016-07-01 03:06:55

Aren't others seeing this problem? I can't imagine Fedora 24 is the only distro on v.5.7.11 of MySQL. I noticed some other web apps had similar problems with this version, but their code has been updated already. I'm wondering if whatever the change was in MySQL, the same change wasn't made in MariaDB, and switching to that would be a workaround. Hate to dump all my databases, remove MySQL, and install MariaDB, just to find out the same problem exists, but I'm thinking about it.

Chris Lander · 2016-07-01 05:14:26

I have a new install of Piwigo on an Ubuntu 16.04 LTS box, and am seeing similar MySQL errors to the above when trying to access an album.

Warning: [mysql error 3065] Expression #1 of ORDER BY clause is not in SELECT list, references column 'piwigo.piwigo_images.date_available' which is not in SELECT list; this is incompatible with DISTINCT

SELECT DISTINCT(image_id)
FROM piwigo_image_category
INNER JOIN piwigo_images ON id = image_id
WHERE
category_id = 3
AND (category_id NOT IN (2))
ORDER BY date_available DESC, file ASC, id ASC
; in /var/www/vhosts/com/labbs/piwigo/include/dblayer/functions_mysqli.inc.php on line 832

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/vhosts/com/labbs/piwigo/include/dblayer/functions_mysqli.inc.php:893 Stack trace: #0 /var/www/vhosts/com/labbs/piwigo/include/section_init.inc.php(318): query2array('\nSELECT DISTINC...', NULL, 'image_id') #1 /var/www/vhosts/com/labbs/piwigo/index.php(27): include('/var/www/vhosts...') #2 {main} thrown in /var/www/vhosts/com/labbs/piwigo/include/dblayer/functions_mysqli.inc.php on line 893

The system has:-
Apache/2.4.18 (Ubuntu)
MySQL 5.7.12-0ubuntu1.1 - (Ubuntu)
PHP 7.0.4-7ubuntu2.1

teekay · 2016-07-01 07:46:29

See [Github] Piwigo issue #305

rbr28 · 2016-07-01 21:08:08

As a temporary workaround for others having the same problem:

sql_mode="" did not work for me. it eliminated some errors, but I ended up with other errors on different pages.

I ended up using the line below (in my.cnf) and everything seems to be working fine. I'd make sure your database is backed up before fiddling around with any changes.

sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Chris Lander · 2016-07-02 05:47:18

I have been looking at this problem for several hours, going through on-line documentation and forums for information about the MySQL commands SELECT DISTINCT and ORDER BY.

The problem seems to be that MySQL behavior has changed with newer versions (I have 5.7.12-0ubuntu1.1 - (Ubuntu)), and that the ORDER BY columns 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 >, < http://stackoverflow.com/questions/5391 … -statement >, and < http://dev.mysql.com/doc/refman/5.7/en/ … l_group_by > for information.

You should be able to set MySQL modes at runtime with an SQL query/command, so I had a quick look at the DB functions in Piwigo and tried to work out a way of setting the SQL mode without breaking everything else!

I seem to have had some success setting the SQL mode for the session by making a couple of additional MySQL queries to ensure ONLY_FULL_GROUP_BY is turned off for the session. Looking through the DB abstraction layer I have made a change in ./include/dblayer/functions_mysqli.inc.php, so that after the database is opened, in function pwg_db_connect an additional query is sent to MySQL to set the server's SQL mode as desired. It seems to be working for me! ;-)

I determined the server's SQL mode using SELECT @@SESSION.sql_mode;, then removed ONLY_FULL_GROUP_ORDER_BY from the result before sending the set command.

I placed my extra code in the function pwg_db_connect block (near the beginning of functions_mysqli.inc.php), in an effort to limit the number of times it was used to try and reduce the database workload.

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

76,90d75
<.
< ### [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);
< # Ensure there is no superflous comma, in case it is reported in the middle of the string
< $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.
91a77
>.
148d133
<.

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

Mercury_LABBS · 2016-07-03 05:24:01

Update to the code/diff above to try and cover all possible results while detecting ONLY_FULL_GROUP_BY in the server flags, and turning the flag off for the session.

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.

Tirs · 2016-09-18 19:05:28

It worked in my 2.8.2! Thanks a lot. Actually, to keep things "clean", I put your hack in a separate mysql5711hack.inc.php file and included it from the functions_mysqli.inc.php file. Hopefully the Piwigo team will release an update, but at least it's working now, thanks to your help.

Regards!

plg · 2016-09-18 19:17:17

Tirs wrote:
Hopefully the Piwigo team will release an update

We will :-) [Github] Piwigo commit b87094ac

Piwigo.org

Announcement

#1 2016-06-29 15:37:11

Many problems after mysql upgrade to 5.7.11, default for Fedora 24

#2 2016-07-01 03:06:55

Re: Many problems after mysql upgrade to 5.7.11, default for Fedora 24

#3 2016-07-01 05:14:26

Re: Many problems after mysql upgrade to 5.7.11, default for Fedora 24

#4 2016-07-01 07:46:29

Re: Many problems after mysql upgrade to 5.7.11, default for Fedora 24

#5 2016-07-01 21:08:08

Re: Many problems after mysql upgrade to 5.7.11, default for Fedora 24

#6 2016-07-02 05:47:18

Re: Many problems after mysql upgrade to 5.7.11, default for Fedora 24

#7 2016-07-03 05:24:01

Re: Many problems after mysql upgrade to 5.7.11, default for Fedora 24

Code:

#8 2016-09-18 19:05:28

Re: Many problems after mysql upgrade to 5.7.11, default for Fedora 24

#9 2016-09-18 19:17:17

Re: Many problems after mysql upgrade to 5.7.11, default for Fedora 24

Tirs wrote:

Board footer