Albums: Pictures are suddenly all the same

[lastzero]

A small number of users report that all their albums "suddenly" have the same pictures:

• All albums suddenly have the same images #3245

We have created this issue to find the root cause and implement code changes if needed. Since most users, including our team, have never had a problem like this, we suspect it is something very specific that cannot be found easily.

In order to reproduce the reported behavior, we therefore need to ask for your help:

1. What types of albums are affected (regular albums, folders, calendar months, states, moments)? Have their titles or other attributes changed as well?
2. Did you upgrade from a previous version? If so, which version and which version are you using now?
3. Did you previously use another database type or version, like MySQL or SQLite? If so, please provide details and what database you are using now.
4. Did you already try performing a rescan of your library? If it's just a database index issue, for example after a schema migration, that should fix it.
5. Are you using any third party apps or scripts provided by our community?
6. Can you remember the last actions (like adding pictures to an album) before the problem occurred?

If you are using Docker Compose with our default configuration, the following will create an SQL dump of your album-related database tables (without anything else) so we can use them for debugging your problem:

docker compose exec -T photoprism mysqldump -u photoprism -p photoprism albums photos_albums > dump.sql 

Please note:

• you need to manually enter the password specified in the environment variable MARIADB_PASSWORD, see your docker-compose.yml
• if you are not using Docker Compose or have changed the default container, database, or username, you will need to modify the command accordingly

Dumps and sample files can be emailed directly to us so you don't have to post them publicly:

👉 https://www.photoprism.app/contact

Any help in providing a solution is greatly appreciated, as it allows our team to work on other issues that our community is waiting for. Thank you very much!

[lastzero]

My best guess is that this is either a database migration issue after upgrading to a newer version, or a problem with updating the album_filter column in the albums table. So far I haven`t found any queries in our current codebase that could cause this behavior, so it might as well be fixed already.

[barry-luijten]

Hi, I was one of the users experiencing this. I am not sure what exactly caused this to happen, but the only change I made was an upgrade from MariaDB from 10.10 to 10.11.
Besides having the problem that all albums (album_type = 'album') showed the same set of unrelated pictures, my album names were also reset to the same name January 2019 in my case.
I found a set of yml files in this directory: storage/albums/album, which turned out to be backups of my album definitions (as @lastzero pointed out to me in the discussion). These turned out to be a life saver.
Then I explored the database table albums and noticed that the field album_filter for most of my album records contained a value, while a test album that I created after noticing the problem, didn't. The field contained this string: path:2016/10 public:true.
The yml files are named after the uid of the album, and contained the original title of my albums. Using that information and a small shell script, I was able to generate a set of SQL statements to update my malformed album records in this format:
UPDATE albums SET album_title = 'Original Title', album_filter = '' WHERE album_uid = 'uid of album';
After this, my albums were restored in their original state! Setting the album_filter field to an empty string fixed the issue of showing the wrong pictures when opening the album.
I'm still curious what caused this, because I suspect that the DB upgrade has little to do with it. I think the issue happened before that and I simply noticed it after the upgrade.
PS. I'm running this version of Photoprism (docker container) since it's release: Build 221118-e58fee0fb

[lastzero]

Thanks for your feedback! Since we haven't tested with 10.11 yet, there is a small risk. See our documentation:

We recommend not using the :latest tag for the MariaDB Docker image and to upgrade manually by changing the tag once we had a chance to test a new major version.

For example, it is possible that the error codes have changed so that our library no longer recognizes not found errors and just returns a generic error, which could change the application logic. I've changed the albums error handling with the last commit to make sure this doesn't happen.

[lastzero]

An updated development preview and public demo will be available for testing soon. Of course, if you're not sure how to reproduce this, it can be difficult to test if the changes are helpful or not...

[lastzero]

@barry-luijten Are you using our MariaDB config example at https://dl.photoprism.app/docker/docker-compose.yml or does your server not only have a different version but also different settings, for example for query compatibility?

[barry-luijten]

@lastzero : Yes, I'm using the exact same command and environment variables in my database container.
I have been trying to reproduce the issue, but failed so far. I remembered a few more things I did before the issue occurred:

1. I searched for specific pictures from a certain month, which might have something to do with my albums being renamed to "January 2019", because that was the exact search term I used.
2. I had "experimental features" turned on. I turned them off after noticing the problems, but that did not help of course. It's still unclear though what features this option exactly unlocks.

I can try to turn experimental features "on" again and perform the search. I think it's only useful to test the preview version if I can reproduce the problem ;)

Thanks for your quick response and help so far!

[lastzero]

Search should never change the contents of the database. I'm also not aware of any experimental features that could cause this.

[barry-luijten]

Update: I searched my "Folder" albums and found one named "January 2019", which was in fact referencing a folder named 2016/10, which was the exact string that was found in the album_filter fields. Apparently one can rename the title of a folder album, so I renamed it back to "October 2016", which is what it should be. Nothing happened on the albums...

[lastzero]

So maybe this could have happened when you renamed folder albums in the UI? Did you also rename any folders on the disk?

[5783354]

I have the same issue, I didn’t rename anything. Could it be caused by this SQL migration? what was the previous length? if it was longer then maybe it broke the path?

[lastzero]

If it was, it should have only affected Folder albums that have a path assigned to them, not all albums?

[5783354]

also I just found that all broken albums have the same (wrong) album_filter and album_slug value in albums table. Even different album types have the same wrong values (moment/album/folder)

[lastzero]

Do you happen to know what exact versions you had installed before? A full rescan changes nothing?

[5783354]

it’s hard to say when it’s happened, around the beginning of this year :), I’m using truecharts, currently I have "PhotoPrism® Plus Build 221118-e58fee0fb", and I update every time there is a new version

let me know if I can help you with investigating this issue

[5783354]

full rescan does not help (it takes ~2 days for me, I have > 200k photos)

[lastzero]

Any change in the filesystem should do as this should trigger a refresh of the albums table. You don't need to refresh all your files.

[lastzero]

Based on your reports, it looks like the batch update happened here when the function was called on a folder album instance without a primary key (which unfortunately omits the where part in the SQL statement with the ORM library we use):

photoprism/internal/entity/album.go

Lines 633 to 636 in a74a02c

	if err := m.Updates(map[string]interface{}{
	"AlbumPath": albumPath,
	"AlbumFilter": albumFilter,
	"AlbumSlug": albumSlug,

Edit: The changes I made earlier this week should prevent this, since the primary key is checked before values are updated.

[lastzero]

My guess is that the SQL select in the find function returned an error other than "not found", e.g. because the database server was unavailable for a short time or because the ORM library didn't properly detect it. The update statement was then executed without a where condition, resulting in a batch update of the albums table.

Clearing the album_path and album_filter columns for manually managed albums should restore them as they were before. For moments, folders, and the calendar, it may be easiest to just remove them by album_type <> 'album' from the albums table and use the index command to recreate them (unless you have a lot of manual changes like added descriptions, etc. that you need to keep).

[5783354]

that’s worked, thank you!
this query fixed all my albums:
update albums set album_path = NULL, album_filter = NULL where album_type = 'album';
that won’t fix album slug, but it’s not critical and can be renamed manually

[lastzero]

@5783354 Exactly, that's the update query as I would have written it:

UPDATE albums SET album_path = '', album_filter = '' WHERE album_type = 'album';

The slug is not really important for the albums to work. I think it should be updated automatically when you change the name of an album.

[IPMAN890]

that’s worked, thank you! this query fixed all my albums: update albums set album_path = NULL, album_filter = NULL where album_type = 'album'; that won’t fix album slug, but it’s not critical and can be renamed manually

omg thanks a lot, that worked, i can finally get back to finishing scanning my parents 40 year old photos!

[barry-luijten]

@IPMAN890 : If you want to recover your album names too, and you have enabled the backup yml files option (enabled by default), you can use this shell script to generate the SQL statements required to restore them:

#!/bin/bash

album_files=$(find . -type f -name '*.yml')
for album_file in $album_files; do
        album_uid=$(basename $album_file .yml)
        title=$(awk 'BEGIN {FS=": "} $1 == "Title" {print $2}' $album_file)
        echo "UPDATE albums SET album_title = '"$title"', album_filter = '' WHERE album_uid = '"$album_uid"';"
done

Just place this script in in your storage/albums/album directory, make it executable and run it from there.