Wakapi init on CockroachDB

[cfouche3005]

Describe the bug

Wakapi won't initialize at first boot : DB migration won't go throught :

Waiting for database to come up
wait-for-it.sh: waiting 60 seconds for serv-arm.netbird.cloud:26257
wait-for-it.sh: serv-arm.netbird.cloud:26257 is available after 0 seconds
2022-12-15T22:39:59.440599436Z [INFO ] creating job queue 'wakapi.default' (1 workers)
2022-12-15T22:39:59.440666676Z [INFO ] creating job queue 'wakapi.processing' (2 workers)
2022-12-15T22:39:59.440687556Z [INFO ] creating job queue 'wakapi.reports' (1 workers)
2022-12-15T22:39:59.440854236Z [INFO ] creating job queue 'wakapi.imports' (1 workers)
2022-12-15T22:39:59.440862676Z [INFO ] creating job queue 'wakapi.housekeeping' (2 workers)
2022-12-15T22:39:59.443417157Z [INFO ] disabling data retention policy, keeping data forever
2022-12-15T22:39:59.478413574Z [INFO ] potentially running migration '20201103-rename_language_mappings_table'
2022-12-15T22:39:59.504079307Z [INFO ] potentially running migration '20201106-migration_cascade_constraints'
2022-12-15T22:39:59.527700279Z [INFO ] no need to migrate '20201106-migration_cascade_constraints'
2022-12-15T22:39:59.527718799Z [INFO ] potentially running migration '20210202-fix_cascade_for_alias_user_constraint'
2022-12-15T22:39:59.54970549Z [INFO ] no need to migrate '20210202-fix_cascade_for_alias_user_constraint'
2022-12-15T22:39:59.54972005Z [INFO ] potentially running migration '20220403-drop_user_project_idx'
2022-12-15T22:39:59.572325301Z [INFO ] no need to migrate '20220403-drop_user_project_idx'
2022-12-15T22:39:59.572412021Z [INFO ] potentially running migration '20220930-drop_heartbeats_entity_idx'
2022-12-15T22:39:59.594637592Z [INFO ] potentially running migration '20221002-fix_summary_id_types'
2022-12-15T22:39:59.594657792Z [INFO ] potentially running migration '20221028-fix_heartbeats_time_user_idx'
2022-12-15T22:40:06.949234567Z [ERROR] ERROR: ALTER COLUMN TYPE from int to int is only supported experimentally (SQLSTATE XCEXF)
panic: ERROR: ALTER COLUMN TYPE from int to int is only supported experimentally (SQLSTATE XCEXF)
goroutine 1 [running]:
github.com/emvi/logbuch.(*StandardFormatter).Pnc(0x40000f9000?, {0x4000014420?, 0x4000014420?}, {0x0?, 0x0?, 0x0?})
	/go/pkg/mod/github.com/emvi/[email protected]/standard_formatter.go:57 +0x40
github.com/emvi/logbuch.(*Logger).Fatal(0x40000f9000, {0x4000014420, 0x5a}, {0x0, 0x0, 0x0})
	/go/pkg/mod/github.com/emvi/[email protected]/logger.go:132 +0x74
github.com/emvi/logbuch.Fatal(...)
	/go/pkg/mod/github.com/emvi/[email protected]/funcs.go:55
github.com/muety/wakapi/migrations.RunSchemaMigrations(0x0?, 0x0?)
	/src/migrations/migrations.go:39 +0x80
github.com/muety/wakapi/migrations.Run(0x400018b380?, 0x2?)
	/src/migrations/migrations.go:33 +0x30
main.main()
	/src/main.go:157 +0x674

System information
Please provide information on:

• Wakapi version : 2.5.4
• Operating system
  • If Docker: ghcr.io/muety/wakapi:latest
• Database : CockroachDB v22.2

Related Docs :
https://www.cockroachlabs.com/docs/v22.2/alter-column#alter-column-data-types

[muety]

Thanks for bringing this up. I tried to fix this, but when setting enable_experimental_alter_column_type_general=true on every session, I'm still getting a different error:

2022-12-16T11:29:19.494417728+01:00 [ERROR] ERROR: relation "users" (110): computed column "share_data_max_days_1" cannot also have a DEFAULT expression (SQLSTATE 42P16)

The error is thrown for the following query, that is part of GORM's auto schema migrations.

ALTER TABLE "users" ALTER COLUMN "share_data_max_days" TYPE bigint USING "share_data_max_days"::bigint

Tbh., I have no clue what "computed" columns are and what that error is about. Would love to get some help on this!

[muety]

I questioned why these seemingly useless queries were even happening in the first place and pushed a fix to prevent them. Initial setup works fine with Cockroach now. However, when restarting Wakapi, another issue occurs:

2022-12-16T12:28:40.519262157+01:00 [ERROR] ERROR: unimplemented: ALTER COLUMN TYPE requiring rewrite of on-disk data is currently not supported for columns that are part of an index (SQLSTATE 0A000)

For query:

ALTER TABLE "heartbeats" ALTER COLUMN "time" TYPE timestamp(3) USING "time"::timestamp(3)

I don't know why GORM thinks that migration is relevant either, but I also don't know how to prevent it. I'd assume that it's just due to a lack in compatibility of GORM's Postgres driver with Cockroach. A temporary workaround is to set WAKAPI_DB_AUTOMIGRATE_FAIL_SILENTLY=true, but that's obviously not a long-term solution.

I'll see if I can debug this further and potentially file an issue with GORM.

[cfouche3005]

Sorry for not replying sooner, thanks for all of your debuging, I have been searching for a solution I couldn't find one.
The full support for gorm by CockrroachDB seem a little misleading.

I will continue to test to find a solution.

[cfouche3005]

My knowledge of Go, SQL, and CockroachDB are pretty limited, so I wasn't able to figure a solution. I guess I will wait for it to be natively supproted by CockroachDB in 2023. I think I will go back to the good old PostgreSQl and abandon easy clustering because I have to much issue with Cockroach and other selfhosted services (nextcloud and coder). CockroachDB will maybe be more Postgres compatible in the future. You can keep the issue open in hope for future support.

[muety]

That sounds like a plan to me. I think there is part of the problem on both ends - on the hand, Cockroach is still not 100 % Postgres-compatible, but on the other hand, GORM seems to be doing weird things, too. At some point, I want to dig a bit deeper into why GORM issues all these unneeded migration queries on startup.

Thanks for these interesting insights, though! I also considered Cockroach an option for Wakapi.dev, because I think the current single-node MySQL setup will reach its limits not too far in the future as data volume keeps growing. But given your findings, I'll probably rather go for a "traditional" MySQL- or Postgres cluster.

[cfouche3005]

I have made a post on Reddit about CockroachDB and it's current use with selfhosted services. Someone says that Yugabyte is more compatible than CockroachDB. I might test it later with his free tier and maybe, it will work ¯\(ツ)/¯ I don't know.
Edit : Yugabyte doesn't support some ALTER COLUMN queries and doesn't release arm64 build (wich is a requirement for me).

[muety]

Wakapi 2.8.0 seems to be working with Cockroach 23.1.5 🙌. At least migrations and integration tests run successfully.