Migration to 2.3.2 fails with "duplicate column name: id"

[YC]

Describe the bug

2022-03-26T09:02:34.632888706Z [WARN ] with sqlite, only a single connection is supported
2022-03-26T09:02:34.658063536Z [INFO ] potentially running migration '20201103-rename_language_mappings_table'
2022-03-26T09:02:34.659944581Z [INFO ] potentially running migration '20201106-migration_cascade_constraints'
2022-03-26T09:02:34.659965181Z [INFO ] not attempting to drop and regenerate constraints on sqlite
2022-03-26T09:02:34.659967501Z [INFO ] potentially running migration '20210202-fix_cascade_for_alias_user_constraint'
2022-03-26T09:02:34.659970141Z [INFO ] not attempting to drop and regenerate constraints on sqlite
2022-03-26T09:02:34.678247352Z [ERROR] duplicate column name: id
panic: duplicate column name: id

goroutine 1 [running]:
github.com/emvi/logbuch.(*StandardFormatter).Pnc(0x40000e2000?, {0x4000454440?, 0x4000454440?}, {0x0?, 0x0?, 0x0?})
	/go/pkg/mod/github.com/emvi/logbuch@v1.2.0/standard_formatter.go:57 +0x44
github.com/emvi/logbuch.(*Logger).Fatal(0x40000e2000, {0x4000454440, 0x19}, {0x0, 0x0, 0x0})
	/go/pkg/mod/github.com/emvi/logbuch@v1.2.0/logger.go:132 +0x78
github.com/emvi/logbuch.Fatal(...)
	/go/pkg/mod/github.com/emvi/logbuch@v1.2.0/funcs.go:55
github.com/muety/wakapi/migrations.RunSchemaMigrations(0x0?, 0x0?)
	/src/migrations/migrations.go:39 +0x84
github.com/muety/wakapi/migrations.Run(0x40000eaf70?, 0x1?)
	/src/migrations/migrations.go:33 +0x34
main.main()
	/src/main.go:141 +0x594
exit code: 0

System information
Please provide information on:

• Wakapi version: 2.3.2 Docker
• Database (SQLite, MySQL, ... ?): SQLite

[muety]

Thanks for bringing this up. I was able to reproduce the error. It occurs while attempting to migrate the heartbeats schema, although it hasn't changed whatsoever since the previous release.

Some digging led to the following GORM issue, which describes the exact same scenario: go-gorm/gorm#5175. There seems to be a bug in GORM which we'll have to wait for to get fixed.

In the meanwhile, you should be able to work around this by setting WAKAPI_DB_AUTOMIGRATE_FAIL_SILENTLY to true (or db.automigrate_fail_silently in config.yml respectively.

[phanirithvij]

@muety setting db.automigrate_fail_silently does not affect it but setting the environment variable works.

[oasido]

In the meanwhile, you should be able to work around this by setting WAKAPI_DB_AUTOMIGRATE_FAIL_SILENTLY to true (or db.automigrate_fail_silently in config.yml respectively.

Sorry for the stupid question, how can I run customized config.yml when using Docker?

[muety]

Sorry for the stupid question, how can I run customized config.yml when using Docker?

Use the respective environment variable (with -e in docker run command). However, until this bug is fixed, it's anyway recommended to keep the previous version.

[muety]

I found that the mentioned upstream bug is not actually the cause for this issue, instead it's related to go-gorm/sqlite#87. I'll push a fix soon.

[roj1512]

This is not fixed for me. I must use -e "WAKAPI_DB_AUTOMIGRATE_FAIL_SILENTLY=true".

[muety]

What exact errors do you get?

[roj1512]

The same as the one in the description of this issue.

[muety]

Can you please post the result of running the following query on your SQLite database?

select * from sqlite_schema

[roj1512]

table|users|users|2|CREATE TABLE `users` (`id` text,`api_key` text UNIQUE,`email` text,`location` text,`password` text,`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,`last_logged_in_at` timestamp DEFAULT CURRENT_TIMESTAMP,`share_data_max_days` integer DEFAULT 0,`share_editors` numeric DEFAULT false,`share_languages` numeric DEFAULT false,`share_projects` numeric DEFAULT false,`share_oss` numeric DEFAULT false,`share_machines` numeric DEFAULT false,`share_labels` numeric DEFAULT false,`is_admin` numeric DEFAULT false,`has_data` numeric DEFAULT false,`wakatime_api_key` text,`reset_token` text,`reports_weekly` numeric DEFAULT false, `wakatime_api_url` text,PRIMARY KEY (`id`))
index|sqlite_autoindex_users_1|users|3|
index|sqlite_autoindex_users_2|users|4|
index|idx_user_email|users|5|CREATE INDEX `idx_user_email` ON `users`(`email`)
table|key_string_values|key_string_values|6|CREATE TABLE `key_string_values` (`key` text,`value` text,PRIMARY KEY (`key`))
index|sqlite_autoindex_key_string_values_1|key_string_values|7|
table|summary_items|summary_items|739|CREATE TABLE "summary_items" (`id` integer,`summary_id` integer,`type` integer,`key` text,`total` integer,PRIMARY KEY (`id`),CONSTRAINT `fk_summaries_projects` FOREIGN KEY (`summary_id`) REFERENCES `summaries`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT `fk_summaries_languages` FOREIGN KEY (`summary_id`) REFERENCES `summaries`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT `fk_summaries_editors` FOREIGN KEY (`summary_id`) REFERENCES `summaries`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT `fk_summaries_operating_systems` FOREIGN KEY (`summary_id`) REFERENCES `summaries`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT `fk_summaries_machines` FOREIGN KEY (`summary_id`) REFERENCES `summaries`(`id`) ON DELETE CASCADE ON UPDATE CASCADE)
index|idx_type|summary_items|18|CREATE INDEX `idx_type` ON `summary_items`(`type`)
table|aliases|aliases|8|CREATE TABLE "aliases" (`id` integer,`type` integer NOT NULL,`user_id` text NOT NULL,`key` text NOT NULL,`value` text NOT NULL,PRIMARY KEY (`id`),CONSTRAINT `fk_aliases_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE)
index|idx_alias_type_key|aliases|22|CREATE INDEX `idx_alias_type_key` ON `aliases`(`type`,`key`)
index|idx_alias_user|aliases|24|CREATE INDEX `idx_alias_user` ON `aliases`(`user_id`)
table|heartbeats|heartbeats|10|CREATE TABLE "heartbeats" (`id` integer,`user_id` text NOT NULL,`entity` text NOT NULL,`type` text,`category` text,`project` text,`branch` text,`language` text,`is_write` numeric,`editor` text,`operating_system` text,`machine` text,`user_agent` text,`time` timestamp,`hash` varchar(17),`origin` text,`origin_id` text,`created_at` timestamp,PRIMARY KEY (`id`),CONSTRAINT `fk_heartbeats_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE)
index|idx_time_user|heartbeats|6939|CREATE INDEX `idx_time_user` ON `heartbeats`(`user_id`)
index|idx_project|heartbeats|35047|CREATE INDEX `idx_project` ON `heartbeats`(`project`)
index|idx_branch|heartbeats|1225|CREATE INDEX `idx_branch` ON `heartbeats`(`branch`)
index|idx_editor|heartbeats|29579|CREATE INDEX `idx_editor` ON `heartbeats`(`editor`)
index|idx_language|heartbeats|17032|CREATE INDEX `idx_language` ON `heartbeats`(`language`)
index|idx_operating_system|heartbeats|31506|CREATE INDEX `idx_operating_system` ON `heartbeats`(`operating_system`)
index|idx_machine|heartbeats|31775|CREATE INDEX `idx_machine` ON `heartbeats`(`machine`)
index|idx_time|heartbeats|30871|CREATE INDEX `idx_time` ON `heartbeats`(`time`)
index|idx_heartbeats_hash|heartbeats|34697|CREATE UNIQUE INDEX `idx_heartbeats_hash` ON `heartbeats`(`hash`)
table|summaries|summaries|11594|CREATE TABLE "summaries" (`id` integer,`user_id` text NOT NULL,`from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`to_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`num_heartbeats` integer DEFAULT 0,PRIMARY KEY (`id`),CONSTRAINT `fk_summaries_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE)
index|idx_time_summary_user|summaries|17|CREATE INDEX `idx_time_summary_user` ON `summaries`(`user_id`,`from_time`,`to_time`)
table|language_mappings|language_mappings|13474|CREATE TABLE "language_mappings" (`id` integer,`user_id` text NOT NULL,`extension` varchar(16),`language` varchar(64),PRIMARY KEY (`id`),CONSTRAINT `fk_language_mappings_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE)
index|idx_language_mapping_user|language_mappings|19|CREATE INDEX `idx_language_mapping_user` ON `language_mappings`(`user_id`)
index|idx_language_mapping_composite|language_mappings|21|CREATE UNIQUE INDEX `idx_language_mapping_composite` ON `language_mappings`(`user_id`,`extension`)
table|project_labels|project_labels|11639|CREATE TABLE "project_labels" (`id` integer,`user_id` text NOT NULL,`project_key` text,`label` varchar(64),PRIMARY KEY (`id`),CONSTRAINT `fk_project_labels_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE)
index|idx_project_label_user|project_labels|9|CREATE INDEX `idx_project_label_user` ON `project_labels`(`user_id`)
index|idx_user_project|heartbeats|1123|CREATE INDEX idx_user_project on heartbeats (user_id, project)
table|diagnostics|diagnostics|23|CREATE TABLE "diagnostics" (`id` integer,`platform` text,`architecture` text,`plugin` text,`cli_version` text,`logs` text,`stack_trace` text,PRIMARY KEY (`id`))

[muety]

Can't reproduce this. Are you on version 2.3.4?

[roj1512]

Oh, no. It is 2.3.2. My bad.

[roj1512]

I fixed it, thanks for being with me @muety!