Hello all!
In our environment, we use a MariaDB Galera Cluster as the database for Keycloak. When upgrading Keycloak from version 24.0.5-ucs1 to 25.0.6-ucs1, we encountered an issue where the Keycloak container entered a cycle of restarts. In the logs (docker logs keycloak), the following error appeared:
2024-11-06 13:03:51,950 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode
2024-11-06 13:03:51,950 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to update database
2024-11-06 13:03:51,950 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-25.0.0.xml::unique-consentuser-mysql::keycloak:
Reason: liquibase.exception.DatabaseException: (conn=23776) Access denied for user 'keycloak'@'%' to database 'keycloak' [Failed SQL: (1044) CREATE TEMPORARY TABLE TEMP_USER_CONSENT_IDS AS SELECT uc.ID FROM keycloak.USER_CONSENT uc INNER JOIN ( SELECT CLIENT_ID, USER_ID, MAX(LAST_UPDATED_DATE) AS MAX_UPDATED_DATE FROM keycloak.USER_CONSENT GROUP BY CLIENT_ID, USER_ID HAVING COUNT(*) > 1 ) max_dates ON uc.CLIENT_ID = max_dates.CLIENT_ID AND uc.USER_ID = max_dates.USER_ID AND uc.LAST_UPDATED_DATE = max_dates.MAX_UPDATED_DATE]
2024-11-06 13:03:51,951 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-25.0.0.xml::unique-consentuser-mysql::keycloak:
Reason: liquibase.exception.DatabaseException: (conn=23776) Access denied for user 'keycloak'@'%' to database 'keycloak' [Failed SQL: (1044) CREATE TEMPORARY TABLE TEMP_USER_CONSENT_IDS AS SELECT uc.ID FROM keycloak.USER_CONSENT uc INNER JOIN ( SELECT CLIENT_ID, USER_ID, MAX(LAST_UPDATED_DATE) AS MAX_UPDATED_DATE FROM keycloak.USER_CONSENT GROUP BY CLIENT_ID, USER_ID HAVING COUNT(*) > 1 ) max_dates ON uc.CLIENT_ID = max_dates.CLIENT_ID AND uc.USER_ID = max_dates.USER_ID AND uc.LAST_UPDATED_DATE = max_dates.MAX_UPDATED_DATE]
2024-11-06 13:03:51,951 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Migration failed for changeset META-INF/jpa-changelog-25.0.0.xml::unique-consentuser-mysql::keycloak:
Reason: liquibase.exception.DatabaseException: (conn=23776) Access denied for user 'keycloak'@'%' to database 'keycloak' [Failed SQL: (1044) CREATE TEMPORARY TABLE TEMP_USER_CONSENT_IDS AS SELECT uc.ID FROM keycloak.USER_CONSENT uc INNER JOIN ( SELECT CLIENT_ID, USER_ID, MAX(LAST_UPDATED_DATE) AS MAX_UPDATED_DATE FROM keycloak.USER_CONSENT GROUP BY CLIENT_ID, USER_ID HAVING COUNT(*) > 1 ) max_dates ON uc.CLIENT_ID = max_dates.CLIENT_ID AND uc.USER_ID = max_dates.USER_ID AND uc.LAST_UPDATED_DATE = max_dates.MAX_UPDATED_DATE]
2024-11-06 13:03:51,951 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: (conn=23776) Access denied for user 'keycloak'@'%' to database 'keycloak' [Failed SQL: (1044) CREATE TEMPORARY TABLE TEMP_USER_CONSENT_IDS AS SELECT uc.ID FROM keycloak.USER_CONSENT uc INNER JOIN ( SELECT CLIENT_ID, USER_ID, MAX(LAST_UPDATED_DATE) AS MAX_UPDATED_DATE FROM keycloak.USER_CONSENT GROUP BY CLIENT_ID, USER_ID HAVING COUNT(*) > 1 ) max_dates ON uc.CLIENT_ID = max_dates.CLIENT_ID AND uc.USER_ID = max_dates.USER_ID AND uc.LAST_UPDATED_DATE = max_dates.MAX_UPDATED_DATE]
This issue occurs because the MySQL database user lacks the permission to create temporary tables. To resolve it, you need to grant the necessary permissions to the user specified in the Keycloak configuration:
GRANT CREATE TEMPORARY TABLES ON `<database>`.* TO `<user>`@`<host>`;
It would be helpful if the documentation (5. Database configuration — Univention Keycloak app manual 25.0.6) included this permission requirement. Please request that the Univention team update the documentation to include this required permission.