keycloak: Keycloak unable to start using legacy JPA storage with CockroachDB

Describe the bug

When using CockroachDB with the legacy JPA storage, Keycloak fails to start with the following error:

2022-02-16 14:00:54,681 ERROR [org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider] (ServerService Thread Pool -- 58) Error has occurred while updating the database: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set META-INF/jpa-changelog-1.3.0.xml::1.3.0::bburke@redhat.com:
     Reason: liquibase.exception.DatabaseException: ERROR: relation "user_required_action" (127): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
  Hint: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/48026/v21.2 [Failed SQL: (0) ALTER TABLE public.USER_REQUIRED_ACTION DROP CONSTRAINT CONSTRAINT_2]

This happens because CockroachDB doesn’t allow a primary key to be dropped without adding a new primary key in the same transaction. Liquibase commits changes to the schema one by one, so all changesets that use the dropPrimaryKey change are incompatible with CockroachDB.

Version

17.0.0

Expected behavior

Keycloak should be able to start using CockroachDB

Actual behavior

When using CockroachDB the server fails to start

How to Reproduce?

Run KeycloakServer with the legacy JPA providers using a configuration that connects to a CockroachDB. It uses the Postgres driver so the config is very similar to that of Postgres. Simply adjust the connection URL with the correct host/port and it should work.

If done right, Keycloak will attempt to process the legacy changelogs and it will fail once it finds a changeset that attempts to drop a primary key.

Anything else?

No response

About this issue

Original URL
State: open
Created 2 years ago
Comments: 35 (35 by maintainers)

Commits related to this issue

suggested solution to https://github.com/keycloak/keycloak/issues/10430 — committed to p2-inc/keycloak by xgp 2 years ago

Most upvoted comments

Hi All!

Given https://www.keycloak.org/2023/10/map-store-removal.html, would the team be willing to reconsider CRDB support in the “legacy” store? We have been maintaining a fork of Keycloak for our own and customers’ use since v19 which supports CRDB. The changes we made are described above in https://github.com/keycloak/keycloak/issues/10430#issuecomment-1076849162

Here is a diff for v22.0.4: https://github.com/keycloak/keycloak/compare/22.0.4...p2-inc:keycloak:22.0.4_crdb

And if you want to try it, our images are here: https://quay.io/repository/phasetwo/keycloak-crdb

@sguilhen @ahus1 @pedroigor

xgp on Oct 17, 2023

@xgp @sguilhen Does it make sense to disable JTA? We have some databases that only work when JTA is disabled but this is usually because they are lacking JTA support.

Not sure if it applies here but I think is worth trying it out. To disable JTA, you should set the transaction-xa-enabled option to false as well as make sure the driver is also not XA.

pedroigor on May 24, 2022

Hi @xgp , thanks for the info and for the work you’ve put into this. If I’m not mistaken, the actual JPA transaction is already wrapped as a KeycloakTransaction and is enlisted here:

https://github.com/keycloak/keycloak/blob/main/model/jpa/src/main/java/org/keycloak/connections/jpa/DefaultJpaConnectionProviderFactory.java#L104

Perhaps you can work with this JpaKeycloakTransaction that wraps the actual JPA transaction to add the retry logic?

sguilhen on May 24, 2022