Before reporting an issue

[X] I have read and understood the above terms for submitting issues, and I understand that my issue may be closed without action if I do not follow them.

Area

core

Describe the bug

After upgrading to v25 I got the following error on logs:

```
2024-06-20 17:55:27,238 INFO [com.arjuna.ats.jbossatx] (main) ARJUNA032014: Stopping transaction recovery manager
2024-06-20 17:55:27,273 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode
2024-06-20 17:55:27,273 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) Error details:: java.lang.RuntimeException: Failed to update database
at org.keycloak.quarkus.runtime.storage.legacy.liquibase.QuarkusJpaUpdaterProvider.update(QuarkusJpaUpdaterProvider.java:122)
at org.keycloak.quarkus.runtime.storage.legacy.liquibase.QuarkusJpaUpdaterProvider.update(QuarkusJpaUpdaterProvider.java:85)
at org.keycloak.quarkus.runtime.storage.legacy.database.QuarkusJpaConnectionProviderFactory.update(QuarkusJpaConnectionProviderFactory.java:282)
at org.keycloak.quarkus.runtime.storage.legacy.database.QuarkusJpaConnectionProviderFactory.createOrUpdateSchema(QuarkusJpaConnectionProviderFactory.java:264)
at org.keycloak.quarkus.runtime.storage.legacy.database.QuarkusJpaConnectionProviderFactory.postInit(QuarkusJpaConnectionProviderFactory.java:125)
at org.keycloak.quarkus.runtime.integration.QuarkusKeycloakSessionFactory.init(QuarkusKeycloakSessionFactory.java:105)
at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.createSessionFactory(QuarkusKeycloakApplication.java:64)
at org.keycloak.services.resources.KeycloakApplication.startup(KeycloakApplication.java:91)
at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.onStartupEvent(QuarkusKeycloakApplication.java:51)
at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication_Observer_onStartupEvent_GNZ8m5QenZ9h9VNelo7awjUZFDE.notify(Unknown Source)
at io.quarkus.arc.impl.EventImpl$Notifier.notifyObservers(EventImpl.java:346)
at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:328)
at io.quarkus.arc.impl.EventImpl.fire(EventImpl.java:82)
at io.quarkus.arc.runtime.ArcRecorder.fireLifecycleEvent(ArcRecorder.java:155)
at io.quarkus.arc.runtime.ArcRecorder.handleLifecycleEvents(ArcRecorder.java:106)
at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy_0(Unknown Source)
at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy(Unknown Source)
at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
at io.quarkus.runtime.Application.start(Application.java:101)
at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:111)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:71)
at org.keycloak.quarkus.runtime.KeycloakMain.start(KeycloakMain.java:139)
at org.keycloak.quarkus.runtime.cli.command.AbstractStartCommand.run(AbstractStartCommand.java:35)
at picocli.CommandLine.executeUserObject(CommandLine.java:2026)
at picocli.CommandLine.access$1500(CommandLine.java:148)
at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)
at picocli.CommandLine$RunLast.handle(CommandLine.java:2453)
at picocli.CommandLine$RunLast.handle(CommandLine.java:2415)
at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273)
at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)
at picocli.CommandLine.execute(CommandLine.java:2170)
at org.keycloak.quarkus.runtime.cli.Picocli.parseAndRun(Picocli.java:119)
at org.keycloak.quarkus.runtime.KeycloakMain.main(KeycloakMain.java:99)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at io.quarkus.bootstrap.runner.QuarkusEntryPoint.doRun(QuarkusEntryPoint.java:62)
at io.quarkus.bootstrap.runner.QuarkusEntryPoint.main(QuarkusEntryPoint.java:33)
Caused by: liquibase.exception.CommandExecutionException: 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: Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions. [Failed SQL: (1787) 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]

at liquibase.command.CommandScope.execute(CommandScope.java:253)
at liquibase.Liquibase.lambda$update$0(Liquibase.java:245)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.Liquibase.runInScope(Liquibase.java:1419)
at liquibase.Liquibase.update(Liquibase.java:234)
at liquibase.Liquibase.update(Liquibase.java:212)
at liquibase.Liquibase.update(Liquibase.java:194)
at org.keycloak.quarkus.runtime.storage.legacy.liquibase.QuarkusJpaUpdaterProvider.updateChangeSet(QuarkusJpaUpdaterProvider.java:192)
at org.keycloak.quarkus.runtime.storage.legacy.liquibase.QuarkusJpaUpdaterProvider.update(QuarkusJpaUpdaterProvider.java:107)
... 36 more
Caused by: 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: Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions. [Failed SQL: (1787) 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]

at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:151)
at liquibase.command.core.AbstractUpdateCommandStep.lambda$run$0(AbstractUpdateCommandStep.java:110)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.command.core.AbstractUpdateCommandStep.run(AbstractUpdateCommandStep.java:108)
at liquibase.command.core.UpdateCommandStep.run(UpdateCommandStep.java:105)
at liquibase.command.CommandScope.execute(CommandScope.java:217)
... 47 more
Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-25.0.0.xml::unique-consentuser-mysql::keycloak:

 Reason: liquibase.exception.DatabaseException: Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions. [Failed SQL: (1787) 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]

at liquibase.changelog.ChangeSet.execute(ChangeSet.java:797)
at liquibase.changelog.visitor.UpdateVisitor.executeAcceptedChange(UpdateVisitor.java:119)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:68)
at liquibase.changelog.ChangeLogIterator$2.lambda$run$0(ChangeLogIterator.java:133)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:122)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.Scope.child(Scope.java:252)
at liquibase.Scope.child(Scope.java:256)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:89)
... 55 more
Caused by: liquibase.exception.DatabaseException: Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions. [Failed SQL: (1787) 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]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:470)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:77)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:179)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1291)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1273)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:755)
... 70 more
Caused by: java.sql.SQLException: Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:770)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:653)
at io.agroal.pool.wrapper.StatementWrapper.execute(StatementWrapper.java:220)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:464)
```

Version

25.0.0

Regression

[X] The issue is a regression

Expected behavior

startup should working fine post any db migrations/validations

Actual behavior

statup fails

How to Reproduce?

• Using Google Cloud MySQL with replication/GTID;
• upgrade to v25
• upgrade fails with "Statement violates GTID consistency "

Anything else?

I assume this is a issue with mysql alone since I don't think GTID is a thing on the others, and we could temporarily disable GTID consistency, but its probably best KC does this is a way that does need the work around.

Also server is Gcloud SQL mysql 5.7, and bin logging is enabled which IIRC makes disabling this a bit trickier and certainly not something we would want to go trough in production