Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Update to Keycloak 22.0.3 fails: Migration failed for change set META-INF/jpa-changelog-13.0.0.xml::default-roles::keycloak #23220

Closed
2 tasks done
lrozenblyum opened this issue Sep 13, 2023 · 12 comments · Fixed by #23560
Assignees
Labels
area/storage Indicates an issue that touches storage (change in data layout or data manipulation) kind/bug Categorizes a PR related to a bug
Milestone

Comments

@lrozenblyum
Copy link
Contributor

lrozenblyum commented Sep 13, 2023

Before reporting an issue

Area

core

Describe the bug

Update from 8.0.1 to 22.0.3 fails.

Version

22.0.3

Expected behavior

Update from older Keycloak to newer should work

Actual behavior

During first start of Keycloak 22.0.3, Liquibase migration fails:

2023-09-13 22:10:38,059 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode
2023-09-13 22:10:38,059 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to update database
2023-09-13 22:10:38,059 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-13
.0.0.xml::default-roles::keycloak:
     Reason: liquibase.exception.DatabaseException: ERROR: duplicate key value violates unique constraint "UK_J3RWUVD56ONTGSUHOGM184WW2-2"
  Detail: Key (name, client_realm_constraint)=(default-roles-default, 457fced9-8955-4979-951e-3e5507355372) already exists. [Failed SQL: (0) INSERT INTO keycloak.keycloak_role (ID, CLIENT_REALM_CONSTRAINT, C
LIENT_ROLE, DESCRIPTION, NAME, REALM_ID, REALM) VALUES ('380e0a09-9f4b-4214-8fd6-b9c4ea4253f3', '457fced9-8955-4979-951e-3e5507355372', FALSE, '${role_default-roles-default}', 'default-roles-default', '457fc
ed9-8955-4979-951e-3e5507355372', '457fced9-8955-4979-951e-3e5507355372')]
2023-09-13 22:10:38,059 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Migration failed for changeset META-INF/jpa-changelog-13.0.0.xml::default-roles::keycloak:
     Reason: liquibase.exception.DatabaseException: ERROR: duplicate key value violates unique constraint "UK_J3RWUVD56ONTGSUHOGM184WW2-2"
  Detail: Key (name, client_realm_constraint)=(default-roles-default, 457fced9-8955-4979-951e-3e5507355372) already exists. [Failed SQL: (0) INSERT INTO keycloak.keycloak_role (ID, CLIENT_REALM_CONSTRAINT, C
LIENT_ROLE, DESCRIPTION, NAME, REALM_ID, REALM) VALUES ('380e0a09-9f4b-4214-8fd6-b9c4ea4253f3', '457fced9-8955-4979-951e-3e5507355372', FALSE, '${role_default-roles-default}', 'default-roles-default', '457fc
ed9-8955-4979-951e-3e5507355372', '457fced9-8955-4979-951e-3e5507355372')]
2023-09-13 22:10:38,059 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: ERROR: duplicate key value violates unique constraint "UK_J3RWUVD56ONTGSUHOGM184WW2-2"
  Detail: Key (name, client_realm_constraint)=(default-roles-default, 457fced9-8955-4979-951e-3e5507355372) already exists. [Failed SQL: (0) INSERT INTO keycloak.keycloak_role (ID, CLIENT_REALM_CONSTRAINT, C
LIENT_ROLE, DESCRIPTION, NAME, REALM_ID, REALM) VALUES ('380e0a09-9f4b-4214-8fd6-b9c4ea4253f3', '457fced9-8955-4979-951e-3e5507355372', FALSE, '${role_default-roles-default}', 'default-roles-default', '457fc
ed9-8955-4979-951e-3e5507355372', '457fced9-8955-4979-951e-3e5507355372')]
2023-09-13 22:10:38,060 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: ERROR: duplicate key value violates unique constraint "UK_J3RWUVD56ONTGSUHOGM184WW2-2"
  Detail: Key (name, client_realm_constraint)=(default-roles-default, 457fced9-8955-4979-951e-3e5507355372) already exists.
2023-09-13 22:10:38,060 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) For more details run the same command passing the '--verbose' option. Also you can use '--help' to see the de
tails about the usage of the particular command.

How to Reproduce?

Attempt to update Keycloak 8.0.1 to 22.0.3.
It worked to 21.*

Anything else?

Multiple people in the discussion #9886 confirm that the problem started to occur again in 22.*.

@lrozenblyum lrozenblyum added kind/bug Categorizes a PR related to a bug status/triage labels Sep 13, 2023
@mposolda mposolda added area/storage Indicates an issue that touches storage (change in data layout or data manipulation) and removed team/core labels Sep 18, 2023
@ghost ghost added the team/store label Sep 18, 2023
@ahus1
Copy link
Contributor

ahus1 commented Sep 21, 2023

@lrozenblyum - thank for reporting this. Which kind of database are you using?

@lrozenblyum
Copy link
Contributor Author

@ahus1 PostgreSQL 12.13

@svkoblov
Copy link

svkoblov commented Sep 26, 2023

I can confirm the issue is reproduced with older version of Keycloak (10.0.1 in our case) on PostgreSQL 14
It is probably related with previous issue here: #9886
There are many reports about similar problems.
The basic workaround is to apply multi-step upgrade on older Keycloak version:

  • Firstly upgrade to any previous version before 22.x.x
  • Upgrade to 22.x.x afterwards
    Which is still might be not applicable for high loaded and business critical KC/DB deployments

As far as my investigation is correct: there's some duplicated INSERTs in generated SQL update script, related to default-role definitions in Keycloak Realms. There's no effect on newest Keycloak versions as default roles already exists there (since v15 if I'm not mistaken), that's why it only happens with ancient Keycloak versions
Duplicated INSERTs are blocked by DB constraints which causes above errors

@lrozenblyum
Copy link
Contributor Author

There was a Liquibase version change between Keycloak 21.1.2 (4.16.1) and 22.0.3 (4.20.0) that may affect the migrations execution somehow.
New 22.* migrations themselves don't seem to affect the problematic table.

@vramik
Copy link
Contributor

vramik commented Sep 27, 2023

Thank you all for the report and additional information. I can confirm the bug. The culprit in this case is indeed a liquibase update. It tries to execute the same migration step twice:

	at org.keycloak.connections.jpa.updater.liquibase.custom.JpaUpdate13_0_0_MigrateDefaultRoles.generateStatementsImpl(JpaUpdate13_0_0_MigrateDefaultRoles.java:47)
	at org.keycloak.connections.jpa.updater.liquibase.custom.CustomKeycloakTask.generateStatements(CustomKeycloakTask.java:99)
	at liquibase.change.custom.CustomChangeWrapper.generateStatements(CustomChangeWrapper.java:168)
	at liquibase.changelog.ChangeSet.addSqlMdc(ChangeSet.java:1514)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:700)
	at org.keycloak.connections.jpa.updater.liquibase.custom.JpaUpdate13_0_0_MigrateDefaultRoles.generateStatementsImpl(JpaUpdate13_0_0_MigrateDefaultRoles.java:47)
	at org.keycloak.connections.jpa.updater.liquibase.custom.CustomKeycloakTask.generateStatements(CustomKeycloakTask.java:99)
	at liquibase.change.custom.CustomChangeWrapper.generateStatements(CustomChangeWrapper.java:168)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1250)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:702)

and obviously when it's executed second time it complains that the entry already exists.

It affects only migration from older versions than 13 and the workaround for this (as stated above) is to migrate in two steps.

Still it would be good to investigate a cause of the issue in liquibase or at least find out whether newer liquibase versions are also affected.

@vramik vramik removed their assignment Sep 27, 2023
@lrozenblyum
Copy link
Contributor Author

Thanks for investigating and confirming this @vramik. Yet 2-stage update doesn't seem feasible for our use cases. It would be great to find out how to fix it properly in Keycloak...

@lrozenblyum
Copy link
Contributor Author

Maybe this issue may be relevant for reconsidering if it causes current problem? #21710

@vramik vramik self-assigned this Sep 27, 2023
@ahus1 ahus1 added this to the 22.0.5 milestone Sep 27, 2023
@AxinAx
Copy link

AxinAx commented Sep 28, 2023

This issue also exists in upgrading to 22.0.3 from version 12.0.2.

During first start of Keycloak 22.0.3, Liquibase migration fails:

keycloak_1  | Updating the configuration and installing your custom providers, if any. Please wait.
keycloak_1  | 2023-09-28 06:36:50,558 INFO  [io.quarkus.deployment.QuarkusAugmentor] (main) Quarkus augmentation completed in 8226ms
keycloak_1  | 2023-09-28 06:36:52,008 INFO  [org.keycloak.quarkus.runtime.hostname.DefaultHostnameProvider] (main) Hostname settings: Base URL: <unset>, Hostname: <request>, Strict HTTPS: false, Path: <request>, Strict BackChannel: false, Admin URL: <unset>, Admin: <request>, Port: -1, Proxied: false
keycloak_1  | 2023-09-28 06:36:53,785 WARN  [io.quarkus.agroal.runtime.DataSources] (main) Datasource <default> enables XA but transaction recovery is not enabled. Please enable transaction recovery by setting quarkus.transaction-manager.enable-recovery=true, otherwise data may be lost if the application is terminated abruptly
keycloak_1  | 2023-09-28 06:36:54,233 WARN  [org.infinispan.PERSISTENCE] (keycloak-cache-init) ISPN000554: jboss-marshalling is deprecated and planned for removal
keycloak_1  | 2023-09-28 06:36:54,306 WARN  [org.infinispan.CONFIG] (keycloak-cache-init) ISPN000569: Unable to persist Infinispan internal caches as no global state enabled
keycloak_1  | 2023-09-28 06:36:54,437 INFO  [org.infinispan.CONTAINER] (keycloak-cache-init) ISPN000556: Starting user marshaller 'org.infinispan.jboss.marshalling.core.JBossUserMarshaller'
keycloak_1  | 2023-09-28 06:36:55,941 WARN  [liquibase.changelog.DatabaseChangeLog] (main) Due to mysql SQL limitations, modifyDataType will lose primary key/autoincrement/not null/comment settings explicitly redefined in the change. Use <sql> or <modifySql> to re-specify all configuration if this is the case
keycloak_1  | 2023-09-28 06:36:55,941 WARN  [liquibase.changelog.DatabaseChangeLog] (main) Due to mysql SQL limitations, setNullable will lose primary key/autoincrement/not null/comment settings explicitly redefined in the change. Use <sql> or <modifySql> to re-specify all configuration if this is the case
keycloak_1  | 2023-09-28 06:36:56,081 INFO  [org.keycloak.quarkus.runtime.storage.legacy.liquibase.QuarkusJpaUpdaterProvider] (main) Updating database. Using changelog META-INF/jpa-changelog-master.xml
keycloak_1  | 2023-09-28 06:36:56,325 WARN  [liquibase.changelog.DatabaseChangeLog] (main) Due to mysql SQL limitations, modifyDataType will lose primary key/autoincrement/not null/comment settings explicitly redefined in the change. Use <sql> or <modifySql> to re-specify all configuration if this is the case
keycloak_1  | 2023-09-28 06:36:56,325 WARN  [liquibase.changelog.DatabaseChangeLog] (main) Due to mysql SQL limitations, setNullable will lose primary key/autoincrement/not null/comment settings explicitly redefined in the change. Use <sql> or <modifySql> to re-specify all configuration if this is the case
keycloak_1  | 2023-09-28 06:36:57,215 WARN  [liquibase.changelog.DatabaseChangeLog] (main) Due to mysql SQL limitations, modifyDataType will lose primary key/autoincrement/not null/comment settings explicitly redefined in the change. Use <sql> or <modifySql> to re-specify all configuration if this is the case
keycloak_1  | 2023-09-28 06:36:57,216 WARN  [liquibase.changelog.DatabaseChangeLog] (main) Due to mysql SQL limitations, setNullable will lose primary key/autoincrement/not null/comment settings explicitly redefined in the change. Use <sql> or <modifySql> to re-specify all configuration if this is the case
keycloak_1  | 2023-09-28 06:36:57,517 ERROR [liquibase.changelog.ChangeSet] (main) ChangeSet META-INF/jpa-changelog-13.0.0.xml::default-roles::keycloak encountered an exception.
keycloak_1  | 2023-09-28 06:36:57,565 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (development) mode
keycloak_1  | 2023-09-28 06:36:57,566 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to update database
keycloak_1  | 2023-09-28 06:36:57,566 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-13.0.0.xml::default-roles::keycloak:
keycloak_1  |      Reason: liquibase.exception.DatabaseException: Duplicate entry 'default-roles-account-center-account-center' for key 'UK_J3RWUVD56ONTGSUHOGM184WW2-2' [Failed SQL: (1062) INSERT INTO xczx_chbn_2023.keycloak_role (ID, CLIENT_REALM_CONSTRAINT, CLIENT_ROLE, `DESCRIPTION`, NAME, REALM_ID, REALM) VALUES ('46f9ba32-c847-441c-9576-ac3e538a49ec', 'account-center', 0, '${role_default-roles-account-center}', 'default-roles-account-center', 'account-center', 'account-center')]
keycloak_1  | 2023-09-28 06:36:57,566 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Migration failed for changeset META-INF/jpa-changelog-13.0.0.xml::default-roles::keycloak:
keycloak_1  |      Reason: liquibase.exception.DatabaseException: Duplicate entry 'default-roles-account-center-account-center' for key 'UK_J3RWUVD56ONTGSUHOGM184WW2-2' [Failed SQL: (1062) INSERT INTO xczx_chbn_2023.keycloak_role (ID, CLIENT_REALM_CONSTRAINT, CLIENT_ROLE, `DESCRIPTION`, NAME, REALM_ID, REALM) VALUES ('46f9ba32-c847-441c-9576-ac3e538a49ec', 'account-center', 0, '${role_default-roles-account-center}', 'default-roles-account-center', 'account-center', 'account-center')]
keycloak_1  | 2023-09-28 06:36:57,566 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Duplicate entry 'default-roles-account-center-account-center' for key 'UK_J3RWUVD56ONTGSUHOGM184WW2-2' [Failed SQL: (1062) INSERT INTO xczx_chbn_2023.keycloak_role (ID, CLIENT_REALM_CONSTRAINT, CLIENT_ROLE, `DESCRIPTION`, NAME, REALM_ID, REALM) VALUES ('46f9ba32-c847-441c-9576-ac3e538a49ec', 'account-center', 0, '${role_default-roles-account-center}', 'default-roles-account-center', 'account-center', 'account-center')]
keycloak_1  | 2023-09-28 06:36:57,566 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Duplicate entry 'default-roles-account-center-account-center' for key 'UK_J3RWUVD56ONTGSUHOGM184WW2-2'
keycloak_1  | 2023-09-28 06:36:57,566 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) For more details run the same command passing the '--verbose' option. Also you can use '--help' to see the details about the usage of the particular command.

Database is mysql 5.7

@svkoblov
Copy link

svkoblov commented Sep 28, 2023

This issue also exists in upgrading to 22.0.3 from version 12.0.2.

That's expectable, default-roles-[realm] released in KC v13, so they don't even exist in any previous versions and KC attempts to create it during upgrade

vramik added a commit to vramik/keycloak that referenced this issue Oct 4, 2023
vramik added a commit to vramik/keycloak that referenced this issue Oct 9, 2023
…d twice

Closes keycloak#23220

(cherry picked from commit 7f2f4aa)

Conflicts:
	testsuite/integration-arquillian/tests/base/src/test/java/org/keycloak/testsuite/zerodowntime/ZeroDowntimeTest.java
ahus1 pushed a commit that referenced this issue Oct 9, 2023
…d twice

Closes #23220

(cherry picked from commit 7f2f4aa)

Conflicts:
	testsuite/integration-arquillian/tests/base/src/test/java/org/keycloak/testsuite/zerodowntime/ZeroDowntimeTest.java
@lrozenblyum
Copy link
Contributor Author

lrozenblyum commented Oct 10, 2023

Thank you for backporting it to the 22.* release! @ahus1, @vramik. For us it was a major stopper for upgrading.

srose pushed a commit to srose/keycloak that referenced this issue Dec 20, 2023
kamontat pushed a commit to kamontat/keycloak that referenced this issue Jan 20, 2024
…d twice

Closes keycloak#23220

Signed-off-by: Kamontat Chantrachirathumrong <14089557+kamontat@users.noreply.github.com>
@kschroeder-of
Copy link

Hi! As far as I can see this issue seems to be back on v24.0.1.
I know it was an issue caused by liquibase before, could this be the case again?

Here is an example log output when migrating from before v13 to v24.0.1:

keycloak-test  | 2024-03-11 10:38:18,085 DEBUG [io.quarkus.runtime.Application] (main) Stopping application
keycloak-test  | 2024-03-11 10:38:18,085 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode
keycloak-test  | 2024-03-11 10:38:18,085 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to update database
keycloak-test  | 2024-03-11 10:38:18,085 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-13.0.0.xml::default-roles::keycloak:
keycloak-test  |      Reason: liquibase.exception.DatabaseException: Duplicate entry 'default-roles-test-portal-test-Portal' for key 'KEYCLOAK_ROLE.UK_J3RWUVD56ONTGSUHOGM184WW2-2' [Failed SQL: (1062) INSERT INTO keycloak.KEYCLOAK_ROLE (ID, CLIENT_REALM_CONSTRAINT, CLIENT_ROLE, `DESCRIPTION`, NAME, REALM_ID, REALM) VALUES ('9f628d00-18a2-435c-bf41-eef9042112c7', 'test-Portal', 0, '${role_default-roles-test-portal}', 'default-roles-test-portal', 'test-Portal', 'test-Portal')]
keycloak-test  | 2024-03-11 10:38:18,085 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-13.0.0.xml::default-roles::keycloak:
keycloak-test  |      Reason: liquibase.exception.DatabaseException: Duplicate entry 'default-roles-test-portal-test-Portal' for key 'KEYCLOAK_ROLE.UK_J3RWUVD56ONTGSUHOGM184WW2-2' [Failed SQL: (1062) INSERT INTO keycloak.KEYCLOAK_ROLE (ID, CLIENT_REALM_CONSTRAINT, CLIENT_ROLE, `DESCRIPTION`, NAME, REALM_ID, REALM) VALUES ('9f628d00-18a2-435c-bf41-eef9042112c7', 'test-Portal', 0, '${role_default-roles-test-portal}', 'default-roles-test-portal', 'test-Portal', 'test-Portal')]
keycloak-test  | 2024-03-11 10:38:18,085 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Migration failed for changeset META-INF/jpa-changelog-13.0.0.xml::default-roles::keycloak:
keycloak-test  |      Reason: liquibase.exception.DatabaseException: Duplicate entry 'default-roles-test-portal-test-Portal' for key 'KEYCLOAK_ROLE.UK_J3RWUVD56ONTGSUHOGM184WW2-2' [Failed SQL: (1062) INSERT INTO keycloak.KEYCLOAK_ROLE (ID, CLIENT_REALM_CONSTRAINT, CLIENT_ROLE, `DESCRIPTION`, NAME, REALM_ID, REALM) VALUES ('9f628d00-18a2-435c-bf41-eef9042112c7', 'test-Portal', 0, '${role_default-roles-test-portal}', 'default-roles-test-portal', 'test-Portal', 'test-Portal')]
keycloak-test  | 2024-03-11 10:38:18,085 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Duplicate entry 'default-roles-test-portal-test-Portal' for key 'KEYCLOAK_ROLE.UK_J3RWUVD56ONTGSUHOGM184WW2-2' [Failed SQL: (1062) INSERT INTO keycloak.KEYCLOAK_ROLE (ID, CLIENT_REALM_CONSTRAINT, CLIENT_ROLE, `DESCRIPTION`, NAME, REALM_ID, REALM) VALUES ('9f628d00-18a2-435c-bf41-eef9042112c7', 'test-Portal', 0, '${role_default-roles-test-portal}', 'default-roles-test-portal', 'test-Portal', 'test-Portal')]
keycloak-test  | 2024-03-11 10:38:18,085 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Duplicate entry 'default-roles-test-portal-test-Portal' for key 'KEYCLOAK_ROLE.UK_J3RWUVD56ONTGSUHOGM184WW2-2'

@lrozenblyum
Copy link
Contributor Author

@kschroeder-of consider reporting a new ticket (with references to the current one and #9886)
it will provide more chances for attention of the KC team.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/storage Indicates an issue that touches storage (change in data layout or data manipulation) kind/bug Categorizes a PR related to a bug
Projects
Status: Done
Development

Successfully merging a pull request may close this issue.

7 participants