Migration fails with "drop column: no such column: "old_album_id"

[grisu48]

Lychee version

v7.6.2

Did you check the latest Lychee version?

Yes, I did

Which PHP version are you using?

PHP 8.5

Detailed description of the problem

When updating a containerized Lychee instance from v7.3.0 to v7.6.2 I run into the following migration issue:

lychee[2334128]:    INFO  Running migrations.  
lychee[2334128]: 
lychee[2334128]:   2026_02_22_090000_drop_old_album_id_from_photos ................ 9.99ms FAIL
lychee[2334128]: 
lychee[2334128]: In Connection.php line 838:
lychee[2334128]:                                                                                
lychee[2334128]:   SQLSTATE[HY000]: General error: 1 error in index idx_876e0d91137abcf after   
lychee[2334128]:   drop column: no such column: "old_album_id" - should this be a string liter  
lychee[2334128]:   al in single-quotes? (Connection: sqlite, Database: /var/www/html/Lychee/da  
lychee[2334128]:   tabase/database.sqlite, SQL: alter table "photos" drop column "old_album_id  
lychee[2334128]:   ")                                                                           
lychee[2334128]:                                                                                
lychee[2334128]: 
lychee[2334128]: In Connection.php line 584:
lychee[2334128]:                                                                                
lychee[2334128]:   SQLSTATE[HY000]: General error: 1 error in index idx_876e0d91137abcf after   
lychee[2334128]:   drop column: no such column: "old_album_id" - should this be a string liter  
lychee[2334128]:   al in single-quotes?                                                         
lychee[2334128]:                                                                                
lychee[2334128]: 

I'm using sqlite and short of manually adding this column such that it can be removed I do not see another workaround.

Steps to reproduce the issue

Upgrade Lychee from v.7.3.0 to v.7.6.2 (container)

Diagnostics [REQUIRED]

Cannot provide, as I cannot start the system

Browser & System [REQUIRED]

Firefox v152.0, openSUSE Tumbleweed, Lychee container v.7.6.2

Please confirm (incomplete submissions will not be addressed)

• I have provided easy and step-by-step instructions to reproduce the bug.
• I understand my bug report will be removed if I haven't met the criteria above.
• I understand that if I am requested to provide more information, I must do so within 14 days or the bug report will be closed.

[grisu48]

FYI, the database itself appears to contain the column in question old_album_id:

sqlite> .schema photos
CREATE TABLE IF NOT EXISTS "photos" ("id" varchar(255) not null collate 'binary', "owner_id" integer not null default (0) collate 'binary', "old_album_id" varchar(255) default (NULL) collate 'binary', "created_at" datetime not null, "updated_at" datetime not null, "title" varchar, "description" clob default (NULL) collate 'binary', "license" varchar(255) not null default ('none') collate 'binary', "is_starred" boolean not null default (0) collate 'binary', "iso" varchar(255) default (NULL) collate 'binary', "make" varchar(255) default (NULL) collate 'binary', "model" varchar(255) default (NULL) collate 'binary', "lens" varchar(255) default (NULL) collate 'binary', "aperture" varchar(255) default (NULL) collate 'binary', "shutter" varchar(255) default (NULL) collate 'binary', "focal" varchar(255) default (NULL) collate 'binary', "latitude" numeric(10, 0) default (NULL) collate 'binary', "longitude" numeric(10, 0) default (NULL) collate 'binary', "altitude" numeric(10, 0) default (NULL) collate 'binary', "img_direction" numeric(10, 0) default (NULL) collate 'binary', "location" varchar(255) default (NULL) collate 'binary', "taken_at" datetime default (NULL) collate 'binary', "taken_at_orig_tz" varchar(255) default (NULL) collate 'binary', "type" varchar(255) not null collate 'binary', "filesize" integer not null default (0) collate 'binary', "checksum" varchar(255) not null collate 'binary', "original_checksum" varchar(255) not null collate 'binary', "live_photo_short_path" varchar(255) default (NULL) collate 'binary', "live_photo_content_id" varchar(255) default (NULL) collate 'binary', "live_photo_checksum" varchar(255) default (NULL) collate 'binary', "initial_taken_at" datetime, "initial_taken_at_orig_tz" varchar, "rating_avg" numeric, "duration" varchar, "fps" varchar, foreign key("owner_id") references users("id") on delete no action on update no action, primary key ("id"));
CREATE INDEX "idx_876e0d91137abcf" on "photos" ("old_album_id");
CREATE INDEX "idx_876e0d97e3c61f9" on "photos" ("owner_id");
CREATE INDEX "photos_checksum_index" on "photos" ("checksum");
CREATE INDEX "photos_live_photo_checksum_index" on "photos" ("live_photo_checksum");
CREATE INDEX "photos_live_photo_content_id_index" on "photos" ("live_photo_content_id");
CREATE INDEX "photos_original_checksum_index" on "photos" ("original_checksum");
CREATE INDEX "photos_is_starred_title_taken_created_index" on "photos" ("is_starred", "title", "taken_at", "created_at");
CREATE INDEX "photos_rating_avg_index" on "photos" ("rating_avg");

The error message appears a quotation issue with the sqlite3 backend?

[ildyria]

It seems that for some reason there is a key that is still existing when dropping the column.

lychee[2334128]: SQLSTATE[HY000]: General error: 1 error in index idx_876e0d91137abcf after
lychee[2334128]: drop column: no such column: "old_album_id" - should this be a string liter
lychee[2334128]: al in single-quotes? (Connection: sqlite, Database: /var/www/html/Lychee/da
lychee[2334128]: tabase/database.sqlite, SQL: alter table "photos" drop column "old_album_id
lychee[2334128]: ")

It refers to this:
#4137

I will try to see if I can make a test bench for this.

[grisu48]

Resolved locally by following #4137 (reply in thread): DROP INDEX idx_876e0d91137abcf;

I'm attaching the run log after dropping the index with the migration output here: log.txt

[ildyria]

@grisu48 #4458 here we go :)

[grisu48]

Well crafted! 🤪