A quick note on migrating from sqlite3 to mariadb in case it helps someone.

[jquiros2]

If you plan on importing lots of pictures, or having your photo library grow, just start from zero with MariaDB instead of sqlite which won't scale.

If you have tons of data already, are migrating, and have problems, some of these notes might help you.

I'm using TrueNas Scale with the Photoprism official chart.

After some time importing (a couple of hundreds of Gb's of photos taken over the years) the db was stuck in a locked state for way too long.

So I installed mariadb via docker, enabled tcp access in mariadb config, and made sure the port was forwarding well (external 9306 to internal 3306).
Create the photoprism db using https://raw.githubusercontent.com/photoprism/photoprism/develop/internal/entity/schema/mariadb.sql
listed at https://docs.photoprism.app/developer-guide/database/ and created the photoprism db user/pass.

Then used techouse/sqlite3-to-mysql to migrate the sqlite index.db file into the photoprism mariadb database. I shut down, set photoprism to use mysql instead of sqlite, and things seemed to work.

Except lots of errors in photoprism logs that ended up being mostly resolved by adding auto-increment to key fields in some tables.

I tried "photoprism migrate" and it seemed to update the schema. A note on this, is that most of the docs say "photoprism migration" and for some reason in mine it's "migrate" instead of "migration"

Since I was getting lots of
errors like:
ERRO keyword: Error 1364: Field 'id' doesn't have a default value (find or create collateral)

I needed to make several id columns auto increment in the mariadb photoprism database for it to work well.

I was still having issues, so I enabled the general_log file to trace a little more.

I found:
Prepare INSERT INTO albums (album_uid,album_slug,album_path,album_type,album_title,album_location,`album_catego

So it was not inserting "id" and the db has it and didn't know what to put there as a default. I am guessing id is replaced by uid.

---

Awesome functionality in photoprism!

If I were to do it again, I would just start with this compose file https://dl.photoprism.app/docker/docker-compose.yml listed at https://docs.photoprism.app/getting-started/advanced/databases/

[graciousgrey]

Related to: https://docs.photoprism.app/getting-started/troubleshooting/performance/#migration-from-sqlite

[lapo-luchini]

I recently decided to "clean up" my migrated SQLite→MariaDB schema using the pristine one as you mention, but now it isn't capable of adding any more photo:

TRAC[2024-05-10T10:36:43+02:00] photo: Error 1364 (HY000): Field 'id' doesn't have a default value in Lapo /Xiaomi11T/Camera/IMG_20240222_210627 (create)

I guess all those AUTO_INCREMENT were really necessary… but then why doesn't the official schema contain them?

[lastzero]

Which pristine one? The ones we have in the repo have been generated in the past to generate visual relationship diagrams, but they are not constantly kept up to date and may also be incomplete (depending on which edition you use). It's easiest to generate a complete and up to date mariadb scheme dump from our official Docker image by running the mariadb-dump command with the parameters that are right for your particular use case and situation.

[lastzero]

@lapo-luchini I have updated the mariadb.sql and mariadb.mmd files that we have in our repository for you:

• https://github.com/photoprism/photoprism/tree/develop/internal/entity/schema

Note, however, that this schema description is for illustrative purposes only, e.g. to generate visual relationship diagrams. It should generally not be used to update or replace an existing production database.

[lastzero]

I've also updated our developer guide for you so that it describes how you can generate an updated SQL schema dump from within your development environment:

• https://docs.photoprism.app/developer-guide/database/#mariadb-sql-dump

[lapo-luchini]

Which pristine one? The ones we have in the repo have been generated in the past to generate visual relationship diagrams

Sorry, my bad, I thought this one was.