memos: ERROR failed to migrate database after I updated memos
Describe the bug
Today I updated memos, but the web cnannot load, so I run docker-compose up to check something.
memeos is up-to-date
Attaching to memeos
memeos | ---
memeos | Server profile
memeos | version: 0.22.0
memeos | data: /var/opt/memos
memeos | dsn: /var/opt/memos/memos_prod.db
memeos | addr:
memeos | port: 5230
memeos | mode: prod
memeos | driver: sqlite
memeos | ---
memeos | succeed to copy a backup database file
memeos | start migrate
memeos | applying migration for 0.22.0
memeos | 2024/05/11 14:07:06 ERROR failed to migrate database !BADKEY="SQL logic error: duplicate column name: storage_type (1)\nfailed to execute statement\ngithub.com/usememos/memos/store/db/sqlite.(*DB).execute\n\t/backend-build/store/db/sqlite/migrator.go:206\ngithub.com/usememos/memos/store/db/sqlite.(*DB).applyMigrationForMinorVersion\n\t/backend-build/store/db/sqlite/migrator.go:159\ngithub.com/usememos/memos/store/db/sqlite.(*DB).Migrate\n\t/backend-build/store/db/sqlite/migrator.go:90\nmain.init.func1\n\t/backend-build/bin/memos/main.go:52\ngithub.com/spf13/cobra.(*Command).execute\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.0/command.go:987\ngithub.com/spf13/cobra.(*Command).ExecuteC\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.0/command.go:1115\ngithub.com/spf13/cobra.(*Command).Execute\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.0/command.go:1039\nmain.Execute\n\t/backend-build/bin/memos/main.go:100\nmain.main\n\t/backend-build/bin/memos/main.go:183\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:271\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1695\nmigrate error: ALTER TABLE resource ADD COLUMN storage_type TEXT NOT NULL DEFAULT '';\n\nALTER TABLE resource ADD COLUMN reference TEXT NOT NULL DEFAULT '';\n\nALTER TABLE resource ADD COLUMN payload TEXT NOT NULL DEFAULT '{}';\n\nUPDATE resource\nSET storage_type = 'LOCAL', reference = internal_path\nWHERE internal_path IS NOT NULL AND internal_path != '';\n\nUPDATE resource\nSET storage_type = 'EXTERNAL', reference = external_link\nWHERE external_link IS NOT NULL AND external_link != '';\n\nALTER TABLE resource DROP COLUMN internal_path;\n\nALTER TABLE resource DROP COLUMN external_link;\n\ngithub.com/usememos/memos/store/db/sqlite.(*DB).applyMigrationForMinorVersion\n\t/backend-build/store/db/sqlite/migrator.go:160\ngithub.com/usememos/memos/store/db/sqlite.(*DB).Migrate\n\t/backend-build/store/db/sqlite/migrator.go:90\nmain.init.func1\n\t/backend-build/bin/memos/main.go:52\ngithub.com/spf13/cobra.(*Command).execute\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.0/command.go:987\ngithub.com/spf13/cobra.(*Command).ExecuteC\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.0/command.go:1115\ngithub.com/spf13/cobra.(*Command).Execute\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.0/command.go:1039\nmain.Execute\n\t/backend-build/bin/memos/main.go:100\nmain.main\n\t/backend-build/bin/memos/main.go:183\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:271\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1695\nfailed to apply minor version migration\ngithub.com/usememos/memos/store/db/sqlite.(*DB).Migrate\n\t/backend-build/store/db/sqlite/migrator.go:91\nmain.init.func1\n\t/backend-build/bin/memos/main.go:52\ngithub.com/spf13/cobra.(*Command).execute\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.0/command.go:987\ngithub.com/spf13/cobra.(*Command).ExecuteC\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.0/command.go:1115\ngithub.com/spf13/cobra.(*Command).Execute\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.0/command.go:1039\nmain.Execute\n\t/backend-build/bin/memos/main.go:100\nmain.main\n\t/backend-build/bin/memos/main.go:183\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:271\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1695"
my docker-compose.yml
version: "3"
services:
memos:
image: neosmemo/memos:latest
container_name: memeos
hostname: memeos
ports:
- "7264:5230"
volumes:
- ./.memos/:/var/opt/memos
restart: always
Steps to reproduce
run docker-compose up
The version of Memos you’re using
0.22.0
Screenshots or additional context
No response
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Comments: 16 (4 by maintainers)
@leo9800 I believe the best solution is to remove the two lines of code that led to the error directly in – 01__memo_tags.sql and – 02__memo_payload.sql.
Cause (cont.)
Why we cant drop column
tagsin tablememo? simply because it does not exist.By checking
store/db/mysql/migration/prod/LATEST__SCHEMA.sqlat branchrelease/0.21.0, (last stable release) we could find:There is no column called
tagsin this table, neitherpayload. That is why I regardin
02__memo_payload.sql(branchrelease/0.22.0) is also problematic.I would suggest following modifications:
Use
IF EXISTSto gain higher robustness.