Announcement

  •  » Extensions
  •  » Plugin Problems since upgrade to mysql 8.0

#1 2019-11-18 04:56:12

2otherjobs
Member
2014-07-14
30

Plugin Problems since upgrade to mysql 8.0

Hello/Hi/Greetings,

I have a stand-alone freebsd server hosting piwigo 2.10.1,  mysql 8.0, php7.3.  I have another freebsd server hosting piwigo 2.95.  On both, I upgraded from mysql 5.7 to 8.0.  I did this by exporting the mysql 5.7 database (with piwigo 2.95) using phpMyAdmin, then importing the same database to the new server with mysql 8.0 (with piwigo 2.10.1) using phpMyAdmin.  All went well except that the following plugins now give the following errors:

PWG Stuffs:
---------------------Error below:--------------------
Warning:  [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups, show_title, id_line, width
FROM pw_stuffs
LEFT JOIN pw_user_group
  O' at line 1

SELECT DISTINCT id, name, path, datas, groups, show_title, id_line, width
FROM pw_stuffs
LEFT JOIN pw_user_group
  ON user_id = 1
WHERE (users IS NULL OR users LIKE "%webmaster%")
  AND (groups IS NULL OR groups REGEXP CONCAT("(^|,)",group_id,"(,|$)"))
  AND level <= 8
  AND on_home = "true"
ORDER BY pos ASC
; in /usr/local/www/piwigo/include/dblayer/functions_mysqli.inc.php on line 854


Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in /usr/local/www/piwigo/include/dblayer/functions_mysqli.inc.php:235 Stack trace: #0 /usr/local/www/piwigo/plugins/PWG_Stuffs/class.inc.php(73): pwg_db_fetch_assoc(false) #1 /usr/local/www/piwigo/plugins/PWG_Stuffs/class.inc.php(35): stuffs->get_modules() #2 /usr/local/www/piwigo/plugins/PWG_Stuffs/main.inc.php(53): stuffs->__construct() #3 /usr/local/www/piwigo/include/functions_plugins.inc.php(279): load_stuffs() #4 /usr/local/www/piwigo/index.php(43): trigger_notify('loc_begin_index') #5 {main} thrown in /usr/local/www/piwigo/include/dblayer/functions_mysqli.inc.php on line 235

------------- The error goes away if the plugin is deactivated-------------

Same behavior with plugin Advanced Menu Manager:

--------------Begin Error:--------------------
Warning:  [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups
          FROM pw_amm_blocks
          ORDER BY `order`' at line 1
SELECT id, `order`, users, groups
          FROM pw_amm_blocks
          ORDER BY `order`; in /usr/local/www/piwigo/include/dblayer/functions_mysqli.inc.php on line 854

------------The error goes away when I disable the plugin--------------
I have 21 other plugins working properly with mysql8.0.  I suspect the problem comes from new 'reserved' words used by mysql 8, this time 'groups'.  This is a problem recognized and corrected for reserved word 'rank' in piwigo/include/dblayer/functions_mysqli.inc.php.  I have tried mimicking the code for the work 'groups' without success.

Is there a fix for either the code in the plugins or functions_mysqli.inc.php?  Or is this a different problem?



Piwigo version: Piwigo 2.95 and 2.10.1
PHP version: 7.3
MySQL version: Migrating from 5.7 to 8.0
Piwigo URL: http://

Hope to see a fix for PWG Stuffs and for Advanced Menu Manager.  Both are great and have been useful.
All other 21 plugins that I use work well.  Let me know if I can clarify further what I have done so far.

2otherjobs

Offline

 

#2 2019-11-18 13:42:27

erAck
Only trying to help
2015-09-06
1026

Re: Plugin Problems since upgrade to mysql 8.0

Search the forum. There are some more MySQL reserved keywords in version 8 that in some plugins are used as table names or column names and need to be enclosed in `` backticks to be properly handled.


Running Piwigo at https://erack.net/gallery/

Offline

 

#3 2019-11-18 13:52:05

2otherjobs
Member
2014-07-14
30

Re: Plugin Problems since upgrade to mysql 8.0

i have found that also.  Where do the backticks go?  in the plugin or in /piwigo/include/dblayer/functions_mysqli.inc.php?

I tried copying the solution from GitHub piwigo/piwigo#1095 without success.  There, code is put in functions_mysqli.inc.php to handle reserved word 'groups' by copying the successfully handled reserved work 'rank'.  The code for handling 'rank' is already in the distributed official file.  When i made this change, the errors persisted.

[Github] AMenuManager issue #5

Thanks for the quick reply.

Offline

 

#4 2019-11-18 17:23:30

erAck
Only trying to help
2015-09-06
1026

Re: Plugin Problems since upgrade to mysql 8.0

The code example given there is wrong, first for not properly doing a code block and thus omitting the backticks in the display, second for a typo. See my comment at
[Github] AMenuManager issue #5

In include/dblayer/functions_mysqli.inc.php copy the rank code block and in the copy replace all rank with groups

Better would be to fix the plugin to properly quote the `groups` already. Adding more and more of these workarounds for every unquoted reserved keyword encountered in plugins to function pwg_query() just makes things slower. I don't have the plugin installed and don't know where in its source the query is assembled.


Running Piwigo at https://erack.net/gallery/

Offline

 

#5 2019-11-19 04:01:57

2otherjobs
Member
2014-07-14
30

Re: Plugin Problems since upgrade to mysql 8.0

Thanks.  Your fix has worked.  Perhaps the maintainers of the plugins in question might update their code to take into account the reserved words in Mysql 8.

Offline

 
  •  » Extensions
  •  » Plugin Problems since upgrade to mysql 8.0

Board footer

Powered by FluxBB

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