MariaDB: Problems with invalid Unicode characters on a custom instance running on WSL2

[dgtal1]

1. What is not working as documented?

Indexing fails (moments generation) with the below error messages:

2024-04-15 05:38:15 ERRO moments: Error 1366: Incorrect string value: '\xF3ra 20...' for column `photoprism`.`albums`.`album_path` at row 1 (create folder)
2024-04-15 05:38:15 ERRO moments: Error 1366: Incorrect string value: '\xF3w' for column `photoprism`.`albums`.`album_path` at row 1 (create folder)
2024-04-15 05:23:13 ERRO moments: Error 1366: Incorrect string value: '\xF3ra 20...' for column `photoprism`.`albums`.`album_path` at row 1 (create folder)
2024-04-15 05:23:12 ERRO moments: Error 1366: Incorrect string value: '\xF3w' for column `photoprism`.`albums`.`album_path` at row 1 (create folder)

2. How can we reproduce it?

Steps to reproduce the behavior:

1. Go to Library
2. Click on Logs
3. See error

I've got about 55 000 photos indexed. 90% are JPEGs. I changed thumbnail quality to 70%
I have not adjusted manually any databse entries and yet the error happens.

3. What behavior do you expect?

No error message

4. What could be the cause of your problem?

Perhaps directory names may be causing this. I use polish diactric characters in them. Altough I think in year 2024 software should not have any issues with internationalization and languages as these things are nothing special nor new.

I think this is related to some background worker that is not directly part of the indexing process - e.g. facial recognition (it's the only one I'm aware of that Photoprism is running separately and periodically) as these messages appear after the indexing process finished and they appear more less every 15 minutes.

5. Can you provide us with example files for testing, error logs, or screenshots?

Sure, but which ones? My photos use about 2.5TB disk space. I have not uploaded them via Photoprism. I only index originals with Photoprism.

6. Which software versions do you use?

(a) PhotoPrism Architecture & Build Number: AMD64 Build 231128-f48ff16ef

(b) Database Type & Version: MariaDB hosted on a separate machine

(c) Operating System Types & Versions: Windows 10 + Docker Desktop using WSL2 backend based on Ubuntu

(d) Browser Types & Versions: Firefox, Chrome

(e) Ad Blockers, Browser Plugins, and/or Firewall Software? No blockers

7. On what kind of device is PhotoPrism installed?

This is especially important if you are reporting a performance, import, or indexing issue. You can skip this if you're reporting a problem you found in our public demo, or if it's a completely unrelated issue, such as incorrect page layout.

(a) Device / Processor Type: Windows 10 PC i5-4670K

(b) Physical Memory & Swap Space in GB: 32 GB RAM

(c) Storage Type: SSD

(d) Anything else that might be helpful to know?

8. Do you use a Reverse Proxy, Firewall, VPN, or CDN?

No

[graciousgrey]

Thank you for your feedback! I just tested indexing folders with diactric characters and did not observe any errors.

Do you use our default docker-compose example or a custom mariadb?

If you are not using our default configuration please see:

https://docs.photoprism.app/getting-started/troubleshooting/mariadb/#unicode-support

[dgtal1]

Hello @graciousgrey,
I do not use the docker-compose mariadb, but my own, separate docker-based MariaDB instance. I saw no point of having 2 Maria DBs, so I used the one I already had for other applications.
I executed the SQL query as stated in the docs and here's the result:

MariaDB Docker/photoprism/		http://synology.local/phpMyAdmin/db_sql.php?db=photoprism&server=2
Your SQL query has been executed successfully.

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'



character_set_client	utf8mb4	
character_set_connection	utf8mb4	
character_set_database	utf8mb4	
character_set_filesystem	binary	
character_set_results	utf8mb4	
character_set_server	utf8mb4	
character_set_system	utf8mb3	
collation_connection	utf8mb4_unicode_ci	
collation_database	utf8mb4_general_ci	
collation_server	utf8mb4_general_ci	

I can see that the collation_server variable has a different value than the recommended one by the docs. If I set it to utf8mb4_unicode_ci - will there be any negative implications to my existing photoprism db? Will I have to reindex the entire library again (hope not!).

Thanks in advance for your reply!

[lastzero]

As @graciousgrey, we are not aware of any issues and my tests also confirm that:

Key Differences:

• utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.
• utf8mb4_general_ci is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.

So it is best to use utf8mb4_unicode_ci and not mix utf8mb4_unicode_ci and utf8mb4_general_ci, as this could cause issues that are not covered by our free community support. Also note that tables and columns MAY use different character sets and collations than the server and the database as a whole.

Should you then still experience problems and be sure that it is an issue with our software and not with the MariaDB instance you are using, please provide us with a ZIP archive containing sample files and folders that trigger this error with your instance so that we can test it and provide a fix if necessary.

Thank you very much!

[lastzero]

One more note: Windows uses UCS2 internally and not UTF8. It is also Unicode, but a different encoding. Since Linux, your database and our software only support UTF8, there is a small chance that at least some path names are not encoded correctly when using WSL2.

However, figuring this out is beyond the scope of our community support and it is quite possible that the problem then needs to be fixed in e.g. WSL2 and/or the Go programming language and not in our software. If you have any other information and there is something we need to change, we will be happy to look into it.

[dgtal1]

ne more note: Windows uses UCS2 internally and not UTF8. It is also Unicode, but a different encoding. Since Linux, your database and our software only support UTF8, there is a small chance that at least some path names are not encoded correctly when using WSL2.

Actually I think this is not WSL2 or Windows specific. Later on I moved my photoprism instance to Linux (Synology DSM) and it was reporting the same error. All these folders were created within Linux (DSM). All the originals are hosted on this linux as well. My photoprism inctsnce in DSM accesses them via local DSM volume, whereas my previous Windows instance accessed them via CIFS network share form this DSM server.

I changed the settings of my MariaDB server and the database to the recommended ones (unicode_ci) . Let's see if this helps.

Thank you for your support.

[dgtal1]

It didn't solve it :( Still the same error - reported by by linux/DSM instace:

2024-04-16 10:59:07 ERRO moments: Error 1366: Incorrect string value: '\xF3ra 20...' for column `photoprism`.`albums`.`album_path` at row 1 (create folder)

2024-04-16 10:59:07 ERRO moments: Error 1366: Incorrect string value: '\xF3w' for column `photoprism`.`albums`.`album_path` at row 1 (create folder)

MariaDB variables:

MariaDB Docker/photoprism/		http://synology.local/phpMyAdmin/db_sql.php?db=photoprism&server=2
Your SQL query has been executed successfully.

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'



character_set_client	utf8mb4	
character_set_connection	utf8mb4	
character_set_database	utf8mb4	
character_set_filesystem	binary	
character_set_results	utf8mb4	
character_set_server	utf8mb4	
character_set_system	utf8mb3	
collation_connection	utf8mb4_unicode_ci	
collation_database	utf8mb4_unicode_ci	
collation_server	utf8mb4_unicode_ci	

Can you help me to understand what this means? '\xF3ra 20...' ? I would like to know which folder or file may be the faulty one.

I restarted photprism docker after applying these changes to the db.

[lastzero]

Did you also check the character set used for the specific table and column as that may be different, especially if you've changed the defaults at a later time?

[dgtal1]

I think I've found the directory causing the trouble for '\xF3ra 20...':

The other one:

Can you please advise how should I copy its name and paste it here to preserve the encoding? Should I use ssh terminal for this or copying from the browser is just fine?

Did you also check the character set used for the specific table and column as that may be different, especially if you've changed the defaults at a later time?

Not sure hwo to check it, but I think this screenshot shows this (from phpMyAdmin)?

[lastzero]

https://mariadb.com/kb/en/setting-character-sets-and-collations/#column-level

[lastzero]

If you are not using the latest stable MariaDB release (you don't seem to provide any information on this), I would also recommend upgrading to the latest version before you look any further.

[dgtal1]

Thanks! Also found this link. Here it is:

*************************** 5. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: photoprism
              TABLE_NAME: albums
             COLUMN_NAME: album_path
        ORDINAL_POSITION: 5
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 1024
  CHARACTER_OCTET_LENGTH: 4096
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(1024)
              COLUMN_KEY: MUL
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
            IS_GENERATED: NEVER
   GENERATION_EXPRESSION: NULL

Looks good to me. In fact all the columns in this table are set as utf8mb4_unicode_ci.

[lastzero]

As written earlier, you should also update the MariaDB version you are using and are welcome to provide us with a ZIP archive containing files and folders with problematic names so that we can test it. Troubleshooting your issues is otherwise a lot of guesswork for us and takes a lot of time.

[dgtal1]

Allright thanks! I will try to use your docker-compose file to get it up and running and let's see how it goes. I will just modify the port for mariadb to avoid a conflict with my existing MariaDB.

[dgtal1]

Update for the ones seeking a solution to this in the future:
I used the official docker-compose, set it up and all worked fine. Then I though this maybe related to some old Maria version I had (10.3) although doubted as this is a mature product. I upgraded my db to the newest one - same image as the official docker-compose. That unfortunately also didn't solve it. And then I inspected files from the container point of view. I used both DD and terminal connected to the container. And I observed the same weird, invalid file names. Started googling and found that this happens for CIFS volumes unless you specify the encoding explicitly in the CIFS driver settings using this: iocharset=utf8.
So at the end the CIFS volume definiton looks like this:

  photo: 
    driver_opts: 
      type: cifs 
      o: "iocharset=utf8,username=photoprism,password=***********),vers=3.0" 
      device: "//192.168.0.13/photo" 

And that solved it for me! From that moment onwards Photoprism is able to scan all my folders and no more errors related to invalid file and folder names.
Altough it turned out it wasn't the DB, but at least I'm on the newest version now. Worth the effort taken :)

All fine with Photoprism code! I stayed with Photoprism for my daily usage, altough discovered immich, but despite it does the indexing twice as fast with superior AI recognition accuracy and looks more modern - it's not as stable as Photoprism is and it's built on top of too modern architecture that requires the newest software to run correctly, so it doesn't run correctly on my TV for example. Photoprism is a stable and mature product and runs well everywhere. It doesn't try to be the next Google Photos and I find it a good solution for browsing my family library. If you guys manage to improve the AI recognition to match immich or other self-hosted solutions - it will be the perfect solution then! Keep up the good work!

[lastzero]

Thanks! I've updated our docs to include this option: https://github.com/photoprism/photoprism-docs/blob/master/docs/getting-started/troubleshooting/docker.md

[dgtal1]

Maybe it's worth to mention something in the docs as well: if someone created a CIFS volume before applying the iocharset setting - the volume needs to deleted and the next docker compose will pick up the up-to-date settings. In my case docker compose didn't update the volumes when I added the config. I had to delete and the next docker compose up applied it. This maybe a bug or an intentional behaviour, but I mention this in case someone applies the setting without effect.