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?
Offline
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.
Offline
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?
Offline
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).
Offline
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...?
Offline
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
SELECT id, name, permalink, dir, `rank`, status
and
ORDER BY `rank` ASC
instead, see https://github.com/Piwigo/Piwigo/blob/1 … t.php#L217 and compare with your source code.
Offline