Announcement

#1 2022-07-20 09:49:42

abravorus
Member
2015-03-03
36

Error after upgrading MySQL to 8.0

Hello

Our server was updated to MySQL 8.0.
The main page of the gallery after upgrade works fine. But if you try to open any album you get an error in SQL syntax:
----
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 'ASC' at line 7

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

   ORDER BY rank ASC
; in PIWIGO_ROOT/include/dblayer/functions_mysqli.inc.php on line 847


Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in PIWIGO_ROOT/include/dblayer/functions_mysqli.inc.php:908 Stack trace: #0 PIWIGO_ROOT/include/section_init.inc.php(317): query2array() #1 PIWIGO_ROOT/index.php(12): include('PIWIGO_ROOT...') #2 {main} thrown in PIWIGO_ROOT/include/dblayer/functions_mysqli.inc.php on line 908
----

Looks like this SQL statement, which worked with MySQL 5.7,  is not correct anymore.
It works if you remove "ORDER BY rank ASC" completely.

Could you advise please how to correct the SQL request&

Thanks,
  Alexandr

---
Gallery: https://terijoki.spb.ru/photos/
Piwigo 12.3.0
Linux
PHP: 7.4.30
MySQL: 8.0.28-20
ImageMagick 6.9.11-60

Offline

 

#2 2022-07-20 10:02:03

abravorus
Member
2015-03-03
36

Re: Error after upgrading MySQL to 8.0

Hello again,

The reason of this error is clear:
MySQL: RANK (R); added in 8.0.2 (reserved) - rank is a reserved word since 8.0.2 in MySQL.

So, the field name rank should be written in backtics in the SQL statement.

The question is - in which file to correct that.

Thanks,
  Alexandr

Last edited by abravorus (2022-07-20 10:08:41)

Offline

 

#3 2022-07-20 10:13:17

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

Re: Error after upgrading MySQL to 8.0

The "rank" word should be protected. We made some changes years ago for that. I wonder how you end up still having an unprotected (by backticks) "rank" word.

What is the [Administration > Configuration > Options > tab General > section Basic Settings > option Default photos order] ? can you remove the "manual sort order" criteria? Can you add it back?

Offline

 

#4 2022-07-20 10:45:59

abravorus
Member
2015-03-03
36

Re: Error after upgrading MySQL to 8.0

plg wrote:

The "rank" word should be protected. We made some changes years ago for that. I wonder how you end up still having an unprotected (by backticks) "rank" word.

I didn't change any code :)

plg wrote:

What is the [Administration > Configuration > Options > tab General > section Basic Settings > option Default photos order] ? can you remove the "manual sort order" criteria? Can you add it back?

The default photos order is "By publication date, from new to old".  I removed "manual sort order" - check, added it back - check again. The same error in both cases.

And I can't find 'rank' without backticks in the code... In category_cats.inc.php it's backticked, the problem is not there.

And. the problem is not for all albums, some of the albums work fine. Looks like it depends from the photos order for particular album, I will try to understand.

Update:
It works fine if the album originally had "manual sort order". Changing [Administration > Configuration > Options > tab General > section Basic Settings > option Default photos order] to "manual sort order" didn't help. But if I go to album sort order administration - admin.php?page=album-NNNN-sort_order - and set there "manual sort order" - everything became fine with this particular album.

Is it possible to set "manual sort order" for all albums at once, why changing [Administration > Configuration > Options > tab General > section Basic Settings > option Default photos order] doesn't work?

Thanks,
  Alexandr

Last edited by abravorus (2022-07-20 10:59:47)

Offline

 

#5 2022-07-20 13:54:14

erAck
Only trying to help
2015-09-06
1998

Re: Error after upgrading MySQL to 8.0

That's still the dreaded "sort order rank is stored in the database" failure apparently never solved in 12.x (and I couldn't spot anything on master either, but I also didn't dig thoroughly), the only remedy is to apply manual sort order once for each affected album and then switch back.
See also
[Forum, post 182747 by erAck in topic 31818] Error in functions_mysqli.inc.php on line 847 ?
[Forum, post 182989 by hodgman in topic 31818] Error in functions_mysqli.inc.php on line 847 ?


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

Offline

 

#6 2022-07-21 21:22:33

abravorus
Member
2015-03-03
36

Re: Error after upgrading MySQL to 8.0

erAck wrote:

That's still the dreaded "sort order rank is stored in the database" failure apparently never solved in 12.x (and I couldn't spot anything on master either, but I also didn't dig thoroughly), the only remedy is to apply manual sort order once for each affected album and then switch back.
See also
[Forum, post 182747 by erAck in topic 31818] Error in functions_mysqli.inc.php on line 847 ?
[Forum, post 182989 by hodgman in topic 31818] Error in functions_mysqli.inc.php on line 847 ?

Thanks, I understood.
I have 3000+ albums, so the task is not easy :)

Would be great if this will be fixed sometimes. I'm sure that most of the providers will switch to MySQL 8 very soon and a lot of people will "step on the same rake".

Offline

 

#7 2022-09-05 10:17:06

ahtoagah
Member
2019-01-25
23

Re: Error after upgrading MySQL to 8.0

This may or may not be related to the above discussion (apologies if not), but I solved a "rank by ASC" problem quite simply:

Once I became aware that "rank" was a reserved word in php8.1, and that somehow "ASC" was involved in my problem I used PhpMyAdmin to do a search in my database, and found something interesting. It seems that in the pi_categories table, image_order field I had several hundred (out of a few thousand) records with invalid "rank" data. That is, instead of `rank`by ASC (with back-ticks) they showed rank by ASC (no back ticks). It seems that those invalid entries were created a long time ago, and somehow survived many piwigo upgrades. They only appeared as a problem when I changed to php8.1.

I then used PhpMyAdmin to do a search and replace, and all these problems went away.

Thus, for me it was not a problem with the pwg code, but with my data. I wonder if other users might have the same situation.

Perhaps for a future version of pwg the Maintenance >> Repair and optimise database routines could include a validity check on this kind of newly reserved words.

Offline

 

#8 2022-09-14 18:05:00

slynne
Member
2022-09-12
1

Re: Error after upgrading MySQL to 8.0

plg wrote:

What is the [Administration > Configuration > Options > tab General > section Basic Settings > option Default photos order] ? can you remove the "manual sort order" criteria? Can you add it back?

Thank you so much for this. I struggled with various patches for the rank problem for three days to no avail, but this solved it for me. In fact, "manual" was not even selected in my Default photos order (the first dropdown was blank). I added "manual", and just like that I stopped getting the errors. :) You have made my day!

Offline

 

#9 2022-10-11 22:20:24

abravorus
Member
2015-03-03
36

Re: Error after upgrading MySQL to 8.0

ahtoagah wrote:

This may or may not be related to the above discussion (apologies if not), but I solved a "rank by ASC" problem quite simply:

Once I became aware that "rank" was a reserved word in php8.1, and that somehow "ASC" was involved in my problem I used PhpMyAdmin to do a search in my database, and found something interesting. It seems that in the pi_categories table, image_order field I had several hundred (out of a few thousand) records with invalid "rank" data. That is, instead of `rank`by ASC (with back-ticks) they showed rank by ASC (no back ticks). It seems that those invalid entries were created a long time ago, and somehow survived many piwigo upgrades. They only appeared as a problem when I changed to php8.1.

I then used PhpMyAdmin to do a search and replace, and all these problems went away.

Thanks! That solved my "rank by ASC" problem too.

Offline

 

Board footer

Powered by FluxBB

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