SQLite: Fix "too many SQL variables" error when updating the index

[lastzero]

As a SQLite user, I want PhotoPrism not to throw a "too many SQL variables" error when optimizing the index during or after indexing.

This probably happens somewhere in the entity or query package when Db().Where("id IN (?)", photos) is expanded as part of a SQL query and photos is a slice with more than 999 values.

Related Discussion:

• SQLite: "too many SQL variables" error when updating the index #3665

[lastzero]

My last commit improves the logging so that it is possible to confirm this query is responsible for the error:

photoprism/internal/query/photo.go

Lines 196 to 197 in 139562e

	} else if err = Db().Table(entity.Photo{}.TableName()).Where("id IN (?)", hidden).UpdateColumn("photo_quality", -1).Error; err != nil {
	log.Warnf("index: failed to flag %d pictures as hidden", len(hidden))

👉 The query is flagging pictures with no primary file (JPEG) as hidden. Primary JPEG files may be missing if you either have many broken files or if you have many other file types like HEIC or videos and "Convert to JPEG" is disabled in /library/settings/media.

You can help with testing by updating to today's development preview, which should be available within the next hour:

• https://docs.photoprism.app/getting-started/updates/#development-preview
• https://docs.photoprism.app/release-notes/#development-preview

[lastzero]

Note that I have started a new preview build, but it is not complete yet. I'll add a "please test" label to this issue once it's ready for you to pull as described in our update guide.

[0chroma]

I can duplicate my config folder and point a new docker container at it once it's ready!

[lastzero]

These changes will only update 500 records at once to stay under the SQLite limit of 999 variables for IN (?) conditions.