thespecialbeast
commented
6 months ago I appear to be having the same issue.
Closed wayner6 closed 6 months ago
thespecialbeast
commented
6 months ago I appear to be having the same issue.
leo9800
commented
6 months ago Seems I found the (real) cause of this issue and found a possible solution.
The OP of this issue posted a piece of log SQL logic error: duplicate column name: storage_type, suggests the storage_type column in memo table is related to this issue, but seems it is unrelated.
By examine my docker log from memos container, gives something as follow:
---
Server profile
version: 0.22.0
data: /var/opt/memos
dsn: memos:CENSORED@tcp(db)/memos
addr:
port: 5230
mode: prod
driver: mysql
---
start to migrate database schema
applying migration of 0.22.0
2024/05/11 00:56:06 ERROR failed to migrate database !BADKEY="Error 1091 (42000): Can't DROP C
OLUMN `tags`; check that it exists\nmigrate error: \nALTER TABLE `memo` DROP COLUMN `tags`\ngi
thub.com/usememos/memos/store/db/mysql.(*DB).applyMigrationForMinorVersion\n\t/backend-build/s
tore/db/mysql/migrator.go:131\ngithub.com/usememos/memos/store/db/mysql.(*DB).prodMigrate\n\t/
backend-build/store/db/mysql/migrator.go:104\ngithub.com/usememos/memos/store/db/mysql.(*DB).M
igrate\n\t/backend-build/store/db/mysql/migrator.go:30\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/co
bra@v1.8.0/command.go:987\ngithub.com/spf13/cobra.(*Command).ExecuteC\n\t/go/pkg/mod/github.co
m/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/r
untime/proc.go:271\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1695\nfailed to ap
ply minor version migration\ngithub.com/usememos/memos/store/db/mysql.(*DB).prodMigrate\n\t/ba
ckend-build/store/db/mysql/migrator.go:105\ngithub.com/usememos/memos/store/db/mysql.(*DB).Mig
rate\n\t/backend-build/store/db/mysql/migrator.go:30\nmain.init.func1\n\t/backend-build/bin/me
mos/main.go:52\ngithub.com/spf13/cobra.(*Command).execute\n\t/go/pkg/mod/github.com/spf13/cobr
a@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/g
ithub.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/run
time/proc.go:271\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1695"
---
Server profile
version: 0.22.0
data: /var/opt/memos
dsn: memos:CENSORED@tcp(db)/memos
addr:
port: 5230
mode: prod
driver: mysql
---
start to migrate database schema
applying migration of 0.22.0
2024/05/11 00:56:07 ERROR failed to migrate database !BADKEY="Error 1060 (42S21): Duplicate co
lumn name 'storage_type'\nmigrate error: ALTER TABLE `resource`\n ADD COLUMN `storage_type` V
ARCHAR(256) NOT NULL DEFAULT '',\n ADD COLUMN `reference` VARCHAR(256) NOT NULL DEFAULT '',\n
ADD COLUMN `payload` TEXT NOT NULL\ngithub.com/usememos/memos/store/db/mysql.(*DB).applyMigr
ationForMinorVersion\n\t/backend-build/store/db/mysql/migrator.go:131\ngithub.com/usememos/mem
os/store/db/mysql.(*DB).prodMigrate\n\t/backend-build/store/db/mysql/migrator.go:104\ngithub.c
om/usememos/memos/store/db/mysql.(*DB).Migrate\n\t/backend-build/store/db/mysql/migrator.go:30
\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/mysql.(*DB).prodMigrate\n\t/backend-build/store/db/mysql/migrator.go:105\ngithub.com/usememos/memos/store/db/mysql.(*DB).Migrate\n\t/backend-build/store/db/mysql/migrator.go:30\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"
---
Server profile
version: 0.22.0
data: /var/opt/memos
dsn: memos:CENSORED@tcp(db)/memos
addr:
port: 5230
mode: prod
driver: mysql
---
start to migrate database schema
applying migration of 0.22.0
2024/05/11 00:56:08 ERROR failed to migrate database !BADKEY="Error 1060 (42S21): Duplicate column name 'storage_type'\nmigrate error: ALTER TABLE `resource`\n ADD COLUMN `storage_type` VARCHAR(256) NOT NULL DEFAULT '',\n ADD COLUMN `reference` VARCHAR(256) NOT NULL DEFAULT '',\n ADD COLUMN `payload` TEXT NOT NULL\ngithub.com/usememos/memos/store/db/mysql.(*DB).applyMigrationForMinorVersion\n\t/backend-build/store/db/mysql/migrator.go:131\ngithub.com/usememos/memos/store/db/mysql.(*DB).prodMigrate\n\t/backend-build/store/db/mysql/migrator.go:104\ngithub.com/usememos/memos/store/db/mysql.(*DB).Migrate\n\t/backend-build/store/db/mysql/migrator.go:30\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/mysql.(*DB).prodMigrate\n\t/backend-build/store/db/mysql/migrator.go:105\ngithub.com/usememos/memos/store/db/mysql.(*DB).Migrate\n\t/backend-build/store/db/mysql/migrator.go:30\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"I am on MySQL (MariaDB) while the OP is worked with SQLite, but anyway you could noticed that the first error message reads Can't DROP C OLUMN \tags`; check that it exists, then all the following error messages areDuplicate co
lumn name 'storage_type'`.
By reading code in store/db/mysql/migration/prod/0.22 at branch release/0.22.0, (https://github.com/usememos/memos/tree/release/0.22.0/store/db/mysql/migration/prod/0.22) we could find 4 .sql files, concating them together makes:
-- 00__resource_storage_type.sql
ALTER TABLE `resource` ADD COLUMN `storage_type` VARCHAR(256) NOT NULL DEFAULT '';
ALTER TABLE `resource` ADD COLUMN `reference` VARCHAR(256) NOT NULL DEFAULT '';
ALTER TABLE `resource` ADD COLUMN `payload` TEXT NOT NULL;
UPDATE `resource` SET `storage_type` = 'LOCAL', `reference` = `internal_path` WHERE `internal_path` IS NOT NULL AND `internal_path` != '';
UPDATE `resource` SET `storage_type` = 'EXTERNAL', `reference` = `external_link` WHERE `external_link` IS NOT NULL AND `external_link` != '';
ALTER TABLE `resource` DROP COLUMN `internal_path`;
ALTER TABLE `resource` DROP COLUMN `external_link`;
-- 01__memo_tags.sql
ALTER TABLE `memo` ADD COLUMN `tags_temp` JSON;
UPDATE `memo` SET `tags_temp` = '[]';
ALTER TABLE `memo` DROP COLUMN `tags`;
ALTER TABLE `memo` CHANGE COLUMN `tags_temp` `tags` JSON NOT NULL;
-- 02__memo_payload.sql
ALTER TABLE `memo` ADD COLUMN `payload_temp` JSON;
UPDATE `memo` SET `payload_temp` = '{}';
ALTER TABLE `memo` DROP COLUMN `payload`;
ALTER TABLE `memo` CHANGE COLUMN `payload_temp` `payload` JSON NOT NULL;
-- 03__drop_tag.sql
DROP TABLE IF EXISTS `tag`;The first fail occurs at
ALTER TABLE `memo` DROP COLUMN `tags`;in 01__memo_tags.sql, while all the following error messages come from:
ALTER TABLE `resource` ADD COLUMN `storage_type` VARCHAR(256) NOT NULL DEFAULT ''which is the first line of first migration file 00__resource_storage_type.sql.
This suggests the real cause of this error is in 01__memo_tags.sql, the following error messages are just caused by re-doing the migration process from begining, which tries to create a column that already created.
So, the migration results is:
-- 00__resource_storage_type.sql
ALTER TABLE `resource` ADD COLUMN `storage_type` VARCHAR(256) NOT NULL DEFAULT ''; -- SUCCESS
ALTER TABLE `resource` ADD COLUMN `reference` VARCHAR(256) NOT NULL DEFAULT ''; -- SUCCESS
ALTER TABLE `resource` ADD COLUMN `payload` TEXT NOT NULL; -- SUCCESS
UPDATE `resource` SET `storage_type` = 'LOCAL', `reference` = `internal_path` WHERE `internal_path` IS NOT NULL AND `internal_path` != ''; -- SUCCESS
UPDATE `resource` SET `storage_type` = 'EXTERNAL', `reference` = `external_link` WHERE `external_link` IS NOT NULL AND `external_link` != ''; -- SUCCESS
ALTER TABLE `resource` DROP COLUMN `internal_path`; -- SUCCESS
ALTER TABLE `resource` DROP COLUMN `external_link`; -- SUCCESS
-- 01__memo_tags.sql
ALTER TABLE `memo` ADD COLUMN `tags_temp` JSON; -- SUCCESS
UPDATE `memo` SET `tags_temp` = '[]'; -- SUCCESS
ALTER TABLE `memo` DROP COLUMN `tags`; -- First FAILURE
ALTER TABLE `memo` CHANGE COLUMN `tags_temp` `tags` JSON NOT NULL; -- Not Executed ...
-- 02__memo_payload.sql
ALTER TABLE `memo` ADD COLUMN `payload_temp` JSON;
UPDATE `memo` SET `payload_temp` = '{}';
ALTER TABLE `memo` DROP COLUMN `payload`; -- This could cause another failure, mentioned below.
ALTER TABLE `memo` CHANGE COLUMN `payload_temp` `payload` JSON NOT NULL;
-- 03__drop_tag.sql
DROP TABLE IF EXISTS `tag`;I also dumped the schema of DB by mysqldump <hostname, credentials> --no-data memos, and this confirms my guess:
--
-- Table structure for table `memo`
--
DROP TABLE IF EXISTS `memo`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `memo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` varchar(256) NOT NULL,
`creator_id` int(11) NOT NULL,
`created_ts` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_ts` timestamp NOT NULL DEFAULT current_timestamp(),
`row_status` varchar(255) NOT NULL DEFAULT 'NORMAL',
`content` text NOT NULL,
`visibility` varchar(255) NOT NULL DEFAULT 'PRIVATE',
`tags_temp` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`tags_temp`)),
PRIMARY KEY (`id`),
UNIQUE KEY `idx_memo_resource_name` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;We could see a column named tags_temp, which is the result of
ALTER TABLE `memo` ADD COLUMN `tags_temp` JSON; -- line 1in 01__memo_tags.sql, it should laterly be renamed to tag by
ALTER TABLE `memo` CHANGE COLUMN `tags_temp` `tags` JSON NOT NULL; -- line 4but because of the failure execution of
ALTER TABLE `memo` DROP COLUMN `tags`; -- line 3at one line above, it was not renamed and remains tags_temp.
leo9800
commented
6 months ago 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.
leo9800
commented
6 months ago Now we have already in trouble, we have a database in "half-migrated" status. But fortunately we now know where it failed migration, and the approach we fix it is, manually finish the migration.
In short,
memos to keep its hands off our database-- remaining of 01__memo_tags.sql
ALTER TABLE `memo` DROP COLUMN IF EXISTS `tags`;
ALTER TABLE `memo` CHANGE COLUMN `tags_temp` `tags` JSON NOT NULL;
-- 02__memo_payload.sql
ALTER TABLE `memo` ADD COLUMN `payload_temp` JSON;
UPDATE `memo` SET `payload_temp` = '{}';
ALTER TABLE `memo` DROP COLUMN IF EXISTS `payload`; -- we wont fail this
ALTER TABLE `memo` CHANGE COLUMN `payload_temp` `payload` JSON NOT NULL;
-- 03__drop_tag.sql
DROP TABLE IF EXISTS `tag`;lastly, store/db/mysql/migrator.go suggests that memos would record the history of database migration in table migration_history. Running SELECT * FROM migration_history; results in:
+---------+---------------------+
| version | created_ts |
+---------+---------------------+
| 0.17.1 | 2023-11-21 03:25:06 |
| 0.18.0 | 2023-12-10 20:53:06 |
| 0.19.0 | 2024-01-26 10:38:55 |
| 0.20.0 | 2024-02-10 06:26:13 |
| 0.21.0 | 2024-03-24 14:37:01 |
+---------+---------------------+
5 rows in set (0.000 sec)Which suggest we should run this query at last:
INSERT INTO migration_history VALUES ('0.22.0', '2024-05-12 00:57:00');Change the timestamp (2024-05-12 00:57:00) accordingly, it should not be earlier than the last migration (0.21.0), which is 2024-03-24 14:37:01 in my case.
Finally, restart memos and enjoy.
wzc90
commented
6 months ago @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.
wayner6
commented
6 months ago Thank you very much for your answers. I quit it. I pulled the stable version.
bbruceyuan
commented
5 months ago Thank you very much for your answers. I quit it. I pulled the stable version.
Under @leo9800's instruction, I know why it doesn't work. and his/her explanation is very clear.
But the SQL is for MySQL. how do u fix it in sqlite3.
I can not downgrade 0.22.0 -> 0.21.0. Can you give me some guides to downgrade it to stable version? thanks @wayner6 @leo9800
WyInnovate
commented
5 months ago Yes, I also need to know how to file back to the stable version
wayner6
commented
5 months ago You just need to change the version from lastest to stable, I changed it in my docker-compose file
version: "3"
services:
memos:
image: neosmemo/memos:stable
container_name: memeos
hostname: memeos
ports:
- "7264:5230"
volumes:
- ./.memos/:/var/opt/memos
restart: alwaysEqually, you can change your docker run command.
bbruceyuan
commented
5 months ago You just need to change the version from lastest to stable, I changed it in my docker-compose file
version: "3" services: memos: image: neosmemo/memos:stable container_name: memeos hostname: memeos ports: - "7264:5230" volumes: - ./.memos/:/var/opt/memos restart: always
I have to say it does not work for me. It can not fetch data properly.
I also give up downgration. I hve to give up my android app...
wayner6
commented
5 months ago @bbruceyuan Do you have database files backed up earlier, which before you met this problem. You can use it to replace the current datebase files, it will works, but you will lose some datas.
WyInnovate
commented
5 months ago @wayner6 I will replace the version, but it's still the same issue. How do I synchronize the data? If data is lost, this is not feasible Currently, there is exported data but not imported data. Can we add the function of importing data to facilitate handling this situation
wayner6
commented
5 months ago @WyInnovate You need to replace memos.prod.db in the .memos directory with an earlier backup version on your server.
WyInnovate
commented
5 months ago @WyInnovate You need to replace
memos.prod.dbin the.memosdirectory with an earlier backup version on your server.
When was the latest version changed to 0.22? Can you please explain, thank you Then I remember before that the db file would back up on its own, why can't it be automatically backed up now? @wayner6
Describe the bug
Today I updated memos, but the web cnannot load, so I run
docker-compose upto check something.my docker-compose.yml
Steps to reproduce
run
docker-compose upThe version of Memos you're using
0.22.0
Screenshots or additional context
No response