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)

Most upvoted comments

@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 tags in table memo? simply because it does not exist.

By checking store/db/mysql/migration/prod/LATEST__SCHEMA.sql at branch release/0.21.0, (last stable release) we could find:


-- memo
CREATE TABLE `memo` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(256) NOT NULL UNIQUE,
  `creator_id` INT NOT NULL,
  `created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `row_status` VARCHAR(256) NOT NULL DEFAULT 'NORMAL',
  `content` TEXT NOT NULL,
  `visibility` VARCHAR(256) NOT NULL DEFAULT 'PRIVATE'
);

There is no column called tags in this table, neither payload. That is why I regard

ALTER TABLE `memo` DROP COLUMN `payload`;

in 02__memo_payload.sql (branch release/0.22.0) is also problematic.

I would suggest following modifications:

-- 01__memo_tags.sql
ALTER TABLE `memo` DROP COLUMN IF EXISTS `tags`;
-- 02__memo_payload.sql
ALTER TABLE `memo` DROP COLUMN IF EXISTS `payload`;

Use IF EXISTS to gain higher robustness.