Home› General support

Upgrade from 1.5.9 to 1.6.1 fails at database table update

Sablerider Member
in General support

I did an upgrade from a zenphoto installation from 1.4.14 to 1.5.9 successfully.

Now when i try to upgrade from 1.5.9 to 1.6.1 i can proceed to the table update page. All checkmarks there are green below

"Begin table updates"

and

"Begin converting UTF-8 tables to utf8mb4 collation"

but nothing happens.

I switched to PHP Version 8.0.20 before the upgrade.

The backend shows 1.6.1 but tells me i have to do a setup.

Page is: https://galerie.wirk-licht.de/

You can see the full php-Informations via

https://galerie.wirk-licht.de/phpinfo.php

I cleared the setup log befor starting the setup. This is the output:

Zenphoto Setup v1.6.1 : Sun, 03 Mar 2024 22:55:40 +0100

Warn: zp-data security [is compromised] Zenphoto suggests you make the sensitive files in the zp-data folder accessable by owner only (permissions = 0600). The file permissions for debug-1.log are 0764 which may allow unauthorized access.

Pass: SSL connection

Pass: Apache

Pass: PHP version 8.0.20

Pass: PHP Sessions.

Pass: PHP display_errors

Pass: PHP gettext() support

Pass: PHP cURL support

Pass: PHP tidy support

Pass: PHP ZipArchive support

Pass: PHP JSON support

Pass: PHP exif support

Pass: PHP bz2 support

Pass: PHP fileinfo support

Pass: PHP intl support

Pass: PHP xml support

Pass: PHP dom support

Pass: PHP simplexml support

Pass: PHP Reflection support

Pass: PHP Ctype support

Pass: PHP filter support

Pass: PHP session support

Pass: PHP mbstring and iconv packages

Pass: Graphics support:PHP GD library bundled (2.1.0 compatible) [enabled]PHP Imagick library 3.6.0ImageMagick 6.8.9-9 Q16 x86_64 2019-12-29 http://www.imagemagick.org [available]

Pass: zenphoto.cfg.php file

Pass: The Zenphoto filesystem character define is ISO‑8859‑1 [confirmed]

Pass: PHP mysqli support for configured Database

Pass: PHP pdo_mysql support

Pass: MySQLi version 5.7.25

Pass: Connect to MySQLi

Pass: SQL mode

Pass: Database access rights for usr_web307_2

Pass: SHOW TABLES found: zp_admin_to_object, zp_administrators, zp_albums, zp_captcha, zp_comments, zp_images, zp_menu, zp_news, zp_news2cat, zp_news_categories, zp_obj_to_tag, zp_options, zp_pages, zp_plugin_storage, zp_search_cache, zp_tags

Pass: UTF8MB4 collation support

Pass: Zenphoto core files

Pass: Zenphoto core file permissions

Pass: .htaccess file (RewriteEngine is ON)

Pass: .htaccess RewriteBase is /

Pass: albums folder

Pass: cache folder

Pass: locale folders

Pass: uploaded folder

Pass: zp-data folder

Pass: HTML cache folder (cache_html)

Pass: Third party plugins folder (plugins)

Completed system check

Any help is appreciated.

Cheers,
Alex

Comments

  • acrylian Administrator, Developer

    Please review the debug.log and/or if you have access your server PHp error log.

  • Sablerider Member

    {206328:Tue, 12 Mar 2024 12:29:14 GMT}
    USER ERROR: MySQLi Error: ( ALTER TABLE [prefix]tags CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci ) failed. MySQLi returned the error Duplicate entry 'Fanø' for key 'name' in /var/www/web307/html/wirklicht/zp-core/classes/class-dbmysqli.php on line 83
    trigger_error called from dbMySQLi->query (class-dbmysqli.php [83])
    from dbBase->convertTableToUtf8mb4 (class-dbbase.php [906])
    from index.php [2448]

    i dont understand this error

  • acrylian Administrator, Developer

    That means somehow there is already an entry with the same "name" value of "Fanø" in that table.

    What that exists I cannot tell. You will probably have to look into the table via phpMyAdmin or similar tools and remove that entry.

  • Sablerider Member

    Thanks! you nailed it!

    It was a "duplicated" entry in the tags database

    It was

    "Malmö"
    and
    "Malmø"

    plus

    "Fanö"
    and
    "Fanø"

    So it was not the same entry but a similar one.
    Maybe its something to look further into in the upgrade-process.

    For me the problem was solved by deleting the entrys

    "Fanø"
    and
    "Malø"

    and keeping the entrys

    "Fanö"
    and
    "Malmö"

    I appreciate your support!
    Zenfoto is up and running on the newest version now.

    Kind regards,
    Alex

  • acrylian Administrator, Developer

    Interesting, actually those are clearly not the same. But since chars like ø are "special chars" (just like German umlauts), this might be a strange encoding clash here between PHP itself and the database, especially on the utf8mb4 conversion that is newly added (long overdue) in 1.6.

    Is your system generally utf8? You can use the utlilty button "Database info" on the overview page to get an overview on the encodings used in several places. If any is not utf8 this could be an indicator.

  • Sablerider Member

    Hello acrylian.

    Yeah. Looks like its not set to utf8 completely.

    I change the phpmyadmin collation for the database to utf8mb4_general_ci. It was latin1 before.

    Now i get a green message:

    "The database is UTF-8"

    Still i get a warning, but i dont know where else to change the database encoding.

    MySQLi version: 5.7.25
    Database name: usr_web307_2
    Table prefix: zp_
    The database is UTF-8

    The database is not fully set to UTF-8 (utf8mb4). If you encounter data encoding issues try changing the configuration.

    character_set_client: utf8mb4
    character_set_connection: utf8mb4
    character_set_database: utf8mb4
    character_set_filesystem: binary
    character_set_results: utf8mb4
    character_set_server: latin1
    character_set_system: utf8
    character_sets_dir: /usr/share/mysql/charsets/
    collation_connection: utf8mb4_general_ci
    collation_database: utf8mb4_general_ci
    collation_server: latin1_german1_ci

  • acrylian Administrator, Developer

    If I see right the only thing not uf8 is the server. you cannot change that yourself unless you manage your own server. That would be host domain. But that probably should not matter.

    Did you try if you can can re-add the tags that was complained about?

  • Sablerider Member

    Yes, its a shared hoster (alfahosting.de), so we cant change that.

    We do not need the additional tags. So we keep going without them.

    But i tried to add it, and it works without any errors.

  • Sablerider Member

    sorry,. i have to revoke my previous message. I could add

    "Malmø"

    as a tag.

    But it shows as

    "Malmö"

    At the sub-galery section i now have TWO entries with "Malmö"

    In the "Tags" section auf the admin-panel i only have ONE entry for

    "Malmö"

    and NO entry for "Malmø".

    I checked the mysql database zp_tags. There i only find ONE entry when i search for "Malm" without the "ö" or "ø".

    Strange...

  • acrylian Administrator, Developer

    I have never used your host but surely one of the good hosts over here ;-) The encoding on some partst is not UFT8 on our host as well (also shared hosting btw, which we use you can spot on our home page ,-)).

    Interesting observation. There must be two entries as tags different to other item types don't have a title and urlname pair (which might be changed/aligned some time in the future though). I can only assume that somehow the Malmø one is ignored due to some encoding issue. I will try to reproduce that.

  • acrylian Administrator, Developer

    I finally tried on my local dev install. I could add a tag "Malmø" without any issue and it is used exactly like that.

    This is my local database/server encoding setting:

    character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: latin1
character_set_filesystem: binary
character_set_results: utf8mb4
character_set_server: utf8
character_set_system: utf8
character_sets_dir: /Applications/MAMP/Library/share/charsets/

collation_connection: utf8mb4_general_ci
collation_database: latin1_swedish_ci
collation_server: utf8_general_ci

    So not even everything is utf8. It does not really matter as far as I know that the database is not urf8 completely as the collation of a table itself takes over. All tables respectively string columbs are utf8mb4_unicode_520_ci. Since ø is a special char it must be related to that.

    But certainly if the server transmits data wrong it may end up wrong. This encoding stuff can really be complicated.

    You could try to enter "Malmø" directly in the database via phpmyadmin and see of that works. Tags are assigned via their id to objects so that does no harm.

Sign In or Register to comment.