Announcement

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

rbr28
Member
Bloomington, IN USA
2010-07-13
35

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

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

Offline

 

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

rbr28
Member
Bloomington, IN USA
2010-07-13
35

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

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.

Offline

 

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

Chris Lander
Guest

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

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

 

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

teekay
Member
2013-06-12
404

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

Offline

 

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

rbr28
Member
Bloomington, IN USA
2010-07-13
35

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

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"

Offline

 

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

Chris Lander
Guest

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

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.

 

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

Mercury_LABBS
Member
Luton. UK
2016-07-02
2

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

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.

Offline

 

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

Tirs
Member
2016-09-18
1

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

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!

Offline

 

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

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

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

Tirs wrote:

Hopefully the Piwigo team will release an update

We will :-) [Github] Piwigo commit b87094ac


Latest blog post (November 9th 2018) Why Flickr could not remain free for ever

Offline

 

Board footer

Powered by FluxBB

github twitter facebook google+ newsletter Donate Piwigo.org © 2002-2019 · Contact