Piwigo.org

krist · 2023-10-29 16:01:31

Hello/Hi/Greetings,

I installed Piwigo 14.0.0.beta3 on MacBook Air. It works, and all my albums and photos show correctly. But when I recover my database from a dump from php7.4 (mysql version 15.1), there is this error

ERROR 3554 (HY000) at line 1894: Access to data dictionary table 'mysql.index_stats' is rejected

I tried to use --force option to do the recover. The album pages show the album photos and my descriptions but then the photos in the album do not display resulting in Fatal errors

Fatal error: Uncaught mysqli_sql_exception: 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, file ASC, id ASC' at line 7 in /Users/kit/Sites/piwigo/include/dblayer/functions_mysqli.inc.php:132 Stack trace: #0 /Users/kit/Sites/piwigo/include/dblayer/functions_mysqli.inc.php(132): mysqli->query('\nSELECT DISTINC...') #1 /Users/kit/Sites/piwigo/include/dblayer/functions_mysqli.inc.php(888): pwg_query('\nSELECT DISTINC...') #2 /Users/kit/Sites/piwigo/include/section_init.inc.php(317): query2array('\nSELECT DISTINC...', NULL, 'image_id') #3 /Users/kit/Sites/piwigo/index.php(12): include('/Users/kit/Site...') #4 {main} thrown in /Users/kit/Sites/piwigo/include/dblayer/functions_mysqli.inc.php on line 132

Is there any way to get around this problem, or should I move back to php7.4?

Thanks.

erAck · 2023-10-29 18:17:51

mysql.index_stats isn't a Piwigo table, it's internal to MySQL or MariaDB, see https://mariadb.com/kb/en/mysqlindex_stats-table/ , and shouldn't even be in a regular database dump. If you used command line mysqldump or mariadb-dump you forgot to exclude these system tables, like

Code:

mysqldump -u root -p --ignore-table=mysql.index_stats --ignore-table=mysql.table_stats --ignore-table=mysql.column_stats db_name > db_name_dump.sql

In an existing dump file you can edit and remove the corresponding table structure and data commands, if you feel brave enough.

If you use --force when restoring, the error is ignored, but so is any other error as well..

If photos don't show it may be yet another problem; images are not stored in the database, they reside under the galleries/ (physical albums) and/or upload/ (virtual albums) directories.

The "right syntax to use near 'ASC, file ASC, id ASC'" error might be the ORDER BY unquoted `rank` problem discussed in dozens of topics in the forum. Usually such error is preceded by the (partial) output of the SQL command that caused it that you omitted here and would enlighten this. But it's likely.

krist · 2023-10-29 22:05:18

Thanks for your response. I followed your method to do the mysqldump again on the php7.4 and recover it on php8.2.
I ended with the same problem. The album photo is ok with the text description, but when I click it to view the photos in the album, the same Fatal error is there.

The albums, photos are all showing properly before I did the mysql dump recovery.

I don't know how to investigate the ASC... message. Please give me a link to the discussions in this forum.

Thanks.

edit: I searched and found that i had to change sort order from manual to automatic (new to old) and it works. But now I have to deal with 287 albums. Any short cut?

Last edited by krist (2023-10-29 22:35:53)

krist · 2023-10-29 22:34:14

OK, I search and found that I have to change sort order from manual to automatic (new to old). Now I have to do it for 287 albums. Is there any short cut?

erAck · 2023-11-01 12:44:26

You could use one of mysql or phpMyAdmin or Adminer to select from table piwigo_categories where field image_order contains the unescaped word rank and replace with `rank`. The tricky part is to replace only where rank is indeed a word and not already escaped, otherwise it would be escaped twice..

Backup your table first!

This SQL query (with MySQL >=8.0.4 or MariaDB >=10) should do

Code:

UPDATE piwigo_categories SET image_order = REGEXP_REPLACE(image_order, "(^|[^-`_[:alnum:]])rank([^-`_[:alnum:]]|$)", "\\1`rank`\\2")

Warning, untested..

In phpMyAdmin you probably also could select table piwigo_categories and try Search -> Find and replace,

Find: (^|[^-`_[:alnum:]])rank([^-`_[:alnum:]]|$)
Replace: \\1`rank`\\2
Column: image_order

check Use regular expression.

Again, warning, untested..

Replacement could also be \1`rank`\2 depending on how phpMyAdmin assembles the query.

See also https://dev.mysql.com/doc/refman/8.0/en … xp-replace and https://mariadb.com/kb/en/regexp_replace/

Piwigo.org

Announcement

#1 2023-10-29 16:01:31

[resolved] Error to recover from php 7.4 server dump to php 8.2

#2 2023-10-29 18:17:51

Re: [resolved] Error to recover from php 7.4 server dump to php 8.2

Code:

#3 2023-10-29 22:05:18

Re: [resolved] Error to recover from php 7.4 server dump to php 8.2

#4 2023-10-29 22:34:14

Re: [resolved] Error to recover from php 7.4 server dump to php 8.2

#5 2023-11-01 12:44:26

Re: [resolved] Error to recover from php 7.4 server dump to php 8.2

Code:

Board footer