Announcement

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

abravorus
Member
2015-03-03
30

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
30

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
13564

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
30

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
1396

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
30

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

 

Board footer

Powered by FluxBB

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