Piwigo.org

jonnyq · 2024-01-19 19:49:35

Greetings!

So I'm using Piwigo 2.8.2 on a fairly old website... PHP 5.6.40, MySQL 8.0.36.

MySQL database was unfortunately named Piwigo (because I'm an idiot) and recently it stopped working. I then found an email from a few months back that I had missed that said that if I kept the MySQL db named "piwigo" it would cause problems, as it has.

The error I get is:

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 '' at line 16

SELECT
c.*,
user_representative_picture_id,
nb_images,
date_last,
max_date_last,
count_images,
nb_categories,
count_categories
FROM piwigo_categories c
INNER JOIN piwigo_user_cache_categories ucc
ON id = cat_id
AND user_id = 1
WHERE id_uppercat is NULL

ORDER BY rank in /home/[directory]/public_html/gallery/include/dblayer/functions_mysqli.inc.php on line 832

Fatal error: Call to a member function fetch_assoc() on boolean in /home/[directory]/public_html/gallery/include/dblayer/functions_mysqli.inc.php on line 213

I appear to have the ability to change the name of the MySQL database to something more appropriate, as well as the ability to change the db prefixes from "piwigo_" to something likewise more appropriate.

Is this recommended, is there a solution to changing the name of the MySQL database that my Piwigo installation is linked to?

erAck · 2024-01-19 22:45:07

Your problem is not the database name (why should it even be?) but the "ORDER BY rank" where rank is not escaped as it is a keyword since MySQL 8. The remedy is to change sort order on affected albums to something else and then back to ranking, as that will write the escaped `rank` to the database which will then be used on further queries.

jonnyq · 2024-01-19 23:30:10

Thanks kindly for the info! (Sorry, I'm a relative novice at doing this, I thought perhaps the db name I chose was conflicting with something, but I guess it was actually "rank," cheers)

My next hurdle is that I can't change the album sort order through Configuration... When I go to Configuration -> Albums -> Manage I get a similar error, presumably for the same reason...

This is admin.php?page=cat_list:

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 ', status
FROM piwigo_categories
WHERE id_uppercat IS NULL
ORDER BY rank AS' at line 1

SELECT id, name, permalink, dir, rank, status
FROM piwigo_categories
WHERE id_uppercat IS NULL
ORDER BY rank ASC
; in /home/[directory]/public_html/gallery/include/dblayer/functions_mysqli.inc.php on line 832

Fatal error: Call to a member function fetch_assoc() on boolean in /home/[directory]/public_html/gallery/include/dblayer/functions_mysqli.inc.php on line 885

So, may I ask where I should change "rank" to something like "name"? I tried include/dblayer/functions_mysqli.inc.php and the "ORDER BY rank" phrase wasn't there... If it's an "include", is it assembling the .php from other components? (Sorry, like I said, I'm trying hard over here.) :D

Or is there an easier alternative way to change the album sort order to solve this?

erAck · 2024-01-21 02:52:03

Edit your database using phpMyAdmin or Adminer, in table piwigo_categories in column image_order replace all rank ASC (or rank DESC) with `rank` ASC (or `rank` DESC).

jonnyq · 2024-01-21 04:45:00

phpAdmin as a tool to edit the db was helpful, thank you!

Unfortunately, it's still not working, and I'm not sure why...

I went into piwigo_categories, and saw the image_order column with many rank ASC entries, and changed those all to `rank` ASC

That still gave the same error, so I changed all the image_order row entries to NULL and sadly am still getting the error (in original post)...

What really confused me more is that when I go to Configuration -> Albums -> Manage I still get the same error from post #3, specifically:

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 ', status
FROM piwigo_categories
WHERE id_uppercat IS NULL
ORDER BY rank AS' at line 1

SELECT id, name, permalink, dir, rank, status
FROM piwigo_categories
WHERE id_uppercat IS NULL
ORDER BY rank ASC
; in /home/[directory]/public_html/gallery/include/dblayer/functions_mysqli.inc.php on line 832

Fatal error: Call to a member function fetch_assoc() on boolean in /home/[directory]/public_html/gallery/include/dblayer/functions_mysqli.inc.php on line 885

...but I've changed the entries in the SQL db, I'm not sure why it thinks it hasn't changed, unless... is there somewhere else it would be finding this? I've tried combing the remaining tables in the piwigo db, but I'm not seeing rank ASC anywhere else...?

erAck · 2024-01-21 17:26:22

ORDER BY rank AS' (note the ' apostrophe) is definitely wrong, that should be `rank` ASC instead.

Then again, the query seems to originate from admin/cat_list.php around line 217 but that in its current version has

Code:

SELECT id, name, permalink, dir, `rank`, status

and

Code:

ORDER BY `rank` ASC

instead, see https://github.com/Piwigo/Piwigo/blob/1 … t.php#L217 and compare with your source code.

Piwigo.org

Announcement

#1 2024-01-19 19:49:35

Piwigo 2.8.2 database error

#2 2024-01-19 22:45:07

Re: Piwigo 2.8.2 database error

#3 2024-01-19 23:30:10

Re: Piwigo 2.8.2 database error

#4 2024-01-21 02:52:03

Re: Piwigo 2.8.2 database error

#5 2024-01-21 04:45:00

Re: Piwigo 2.8.2 database error

#6 2024-01-21 17:26:22

Re: Piwigo 2.8.2 database error

Code:

Code:

Board footer