search

netzbegruenung / keycloak-mfa-plugins

Keycloak plugins for MFA (enforce MFA, SMS authentication step, native app integration)
Apache License 2.0
79 stars 24 forks source link

Error in app_auth_challenge table #139

Open alsaadii98 opened 3 weeks ago

alsaadii98 commented 3 weeks ago

Hi, I was installing all of the MFA Provider Plugins into Keycloak after adding them to the provider directory, and try to run the app I got the following error stack

2024-10-02 14:36:43,132 ERROR [liquibase.changelog.ChangeSet] (main) ChangeSet META-INF/challenge-changelog.xml::app-auth-challenge::giuliano.mele@verdigado.com encountered an exception.

UPDATE SUMMARY
Run:                          3
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            3

2024-10-02 14:36:43,170 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (development) mode
2024-10-02 14:36:43,170 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to update database
2024-10-02 14:36:43,170 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/challenge-changelog.xml::app-auth-challenge::giuliano.mele@verdigado.com:
     Reason: liquibase.exception.DatabaseException: (conn=43) Can't create table `keycloak`.`app_auth_challenge` (errno: 150 "Foreign key constraint is incorrectly formed") [Failed SQL: (1005) ALTER TABLE keycloak.app_auth_challenge ADD CONSTRAINT FK_APP_AUTH_CHALLENGE_ON_REALM FOREIGN KEY (realm_id) REFERENCES keycloak.realm (id) ON DELETE CASCADE]
2024-10-02 14:36:43,171 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/challenge-changelog.xml::app-auth-challenge::giuliano.mele@verdigado.com:
     Reason: liquibase.exception.DatabaseException: (conn=43) Can't create table `keycloak`.`app_auth_challenge` (errno: 150 "Foreign key constraint is incorrectly formed") [Failed SQL: (1005) ALTER TABLE keycloak.app_auth_challenge ADD CONSTRAINT FK_APP_AUTH_CHALLENGE_ON_REALM FOREIGN KEY (realm_id) REFERENCES keycloak.realm (id) ON DELETE CASCADE]
2024-10-02 14:36:43,171 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Migration failed for changeset META-INF/challenge-changelog.xml::app-auth-challenge::giuliano.mele@verdigado.com:
     Reason: liquibase.exception.DatabaseException: (conn=43) Can't create table `keycloak`.`app_auth_challenge` (errno: 150 "Foreign key constraint is incorrectly formed") [Failed SQL: (1005) ALTER TABLE keycloak.app_auth_challenge ADD CONSTRAINT FK_APP_AUTH_CHALLENGE_ON_REALM FOREIGN KEY (realm_id) REFERENCES keycloak.realm (id) ON DELETE CASCADE]
2024-10-02 14:36:43,171 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: (conn=43) Can't create table `keycloak`.`app_auth_challenge` (errno: 150 "Foreign key constraint is incorrectly formed") [Failed SQL: (1005) ALTER TABLE keycloak.app_auth_challenge ADD CONSTRAINT FK_APP_AUTH_CHALLENGE_ON_REALM FOREIGN KEY (realm_id) REFERENCES keycloak.realm (id) ON DELETE CASCADE]
2024-10-02 14:36:43,171 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: (conn=43) Can't create table `keycloak`.`app_auth_challenge` (errno: 150 "Foreign key constraint is incorrectly formed")
2024-10-02 14:36:43,171 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.

After debugging, I notice the migration file is trying to create an index with the realm instead of REALM table name.

Screenshot 2024-10-02 at 5 38 19 PM

Here is my system specification: Alama Linux 9.4 MariaDB 10.5.22 Keycloak 24.0.4 Keycloak-mfa-plugin 24.0.4

melegiul commented 1 week ago

Oh no. That will be tricky to fix. :see_no_evil:

Simply fixing the problem in the migration will leave keycloak unusable with validation errors.

2024-10-15 16:26:45,798 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.ValidationFailedException: Validation Failed:
     2 changesets check sum
          META-INF/challenge-changelog.xml::app-auth-challenge::giuliano.mele@verdigado.com was: 9:e1eeca744c1ae636c26136d444a47042 but is now: 9:0e1d0466aa32d78d19fc788feb8e4c18
          META-INF/challenge-changelog.xml::app-auth-challenge-23.0.2::giuliano.mele@verdigado.com was: 9:81ef48391a4183a8d6ad0afd901d9c99 but is now: 9:6100858eda05be18e9df907f365cd0a6

One working way would be:

  1. delete from databasechangelog_challenge_; drop table app_auth_challenge;
  2. Deploy jar file with fixed migrations and restart keycloak

Because the content of app_auth_challenge is ephemeral by nature, data losses should be negligible. Just the transition is a bit tricky.

Thanks a lot for your report. I prepare a fix for the next release.

cc: @svenseeberg