Container will not start, "could not migrate database" error

[yllekz]

Describe the bug Photoview will not start after updating. The container is complaining that the database migration failed. It seems the actual database container is fine but photoview itself is throwing a fit. It was fine until a recent update. I do know it successfully migrated to the 2.0 version so this seems to be a smaller update that somehow broke the database

To Reproduce Steps to reproduce the behavior:

1. Launch container
2. See error

Expected behavior Container should launch

Logs

goroutine 1 [running]:,
log.Panicf(0x90e70d, 0x1f, 0x1e95ae0, 0x1, 0x1),
    /usr/lib/go-1.15/src/log/log.go:358 +0x94,
main.main(),
    /app/server.go:46 +0x178,
2021/04/05 14:36:25 Starting Photoview...,
2021/04/05 14:36:25 No .env file found,
2021/04/05 14:36:25 Connecting to MYSQL database: photoview:photoview@tcp(db:3306)/photoview?multiStatements=true&parseTime=true,

,
2021/04/05 14:36:26 /go/pkg/mod/gorm.io/driver/mysql@v1.0.5/migrator.go:83 Error 1292: Truncated incorrect INTEGER value: '',
[630.960ms] [rows:0] ALTER TABLE `media_exif` MODIFY COLUMN `flash` bigint,
2021/04/05 14:36:26 Migrating `media_exif.flash` from string to int,

,
2021/04/05 14:36:26 /go/pkg/mod/gorm.io/driver/mysql@v1.0.5/migrator.go:83 Error 1292: Truncated incorrect INTEGER value: '',
[636.903ms] [rows:0] ALTER TABLE `media_exif` MODIFY COLUMN `flash` bigint,
2021/04/05 14:36:26 Could not migrate database: failed to auto migrate media_exif after exposure conversion: Error 1292: Truncated incorrect INTEGER value: '',
panic: Could not migrate database: failed to auto migrate media_exif after exposure conversion: Error 1292: Truncated incorrect INTEGER value: '',

Your environment: Raspberry Pi 4B, "Latest" tag

• Are you using Docker? Yes
• Database [e.g. mariadb 10.5] yobasystems/alpine-mariadb:latest
• Browser [e.g. chrome, safari] Firefox
• Version [e.g. 2.0.0] "Latest"

Additional context Add any other context about the problem here.

[viktorstrate]

Does it happen each time you restart the container. It's ok if it fails the first time as the manual migrations should fix it afterwards.

[yllekz]

Yeah I've done full restarts of each container, one by one, both, and the entire host. It never recovers from this state, infinitely looping.

[viktorstrate]

Okay.

Truncated incorrect INTEGER value: ''

It looks like it has problems converting an empty string '' to an integer. Maybe the migration tool should take that into account and make them NULL instead.

Can you try to run UPDATE media_exif SET flash=NULL WHERE flash=""; on your database? If you are using the default docker-compose.yml file, you should be able to get a mysql shell by running the following command.

$ docker-compose exec db mysql -uphotoview -pphotosecret

[yllekz]

Sure, I'll see if I can do that. I'm not sure if I'm using the default docker-compose.yml file though. I was able to use Portainer to break into a console for mariaDB but weirdly enough the only database it will show me is information_schema. (EDIT: fixed, see below)

[yllekz]

Your fix worked @viktorstrate !

Doing this via Portainer meant I had to change up the process a bit, but it was still fixable.

Here's what I had to do to get to the mySQL prompt and run your command (which fixed the photoview container errors):

Portainer->Console->bin/ash (or bin/sh)->Connect Specify user "mysql" then checking "use custom command" and entering this into the "command" box: mysql -u mysql

From there, running this was successful:

USE photoview;
UPDATE media_exif SET flash=NULL WHERE flash="";

[ThibaultNocchi]

So it seems my flash column also didn't migrate to integer type. I imagine it was due as @yllekz showed to some flash values at ''. :)