Keycloak 10 with MYSQL 8

Hi,

I am using latest keycloak version 10.0.2 along with mysql8 and it is failing when started while updating the database. Below are the logs for the same.

ak container
2020-06-08 10:13:15.340Z INFO [ServerService T] o.k.c.i.DefaultInfinispanConnectionProvi {{}} Node name: pun1dt11351, Site name: null
2020-06-08 10:13:18.502Z INFO [ServerService T] o.k.c.j.u.l.LiquibaseJpaUpdaterProvider {{}} Initializing database schema. Using changelog META-INF/jpa-changelog-master.xml
2020-06-08 10:18:15.359Z WARN [Transaction Rea] c.a.a.arjuna {{}} ARJUNA012117: TransactionReaper::check timeout for TX 0:ffff0a2b0205:30337369:5eddc1e1:e in state RUN
2020-06-08 10:18:15.373Z WARN [Transaction Rea] c.a.a.arjuna {{}} ARJUNA012121: TransactionReaper::doCancellations worker Thread[Transaction Reaper Worker 0,5,main] successfully canceled TX 0:ffff0a2b0205:30337369:5eddc1e1:e
2020-06-08 10:18:17.050Z WARN [Transaction Rea] c.a.a.arjuna {{}} ARJUNA012117: TransactionReaper::check timeout for TX 0:ffff0a2b0205:30337369:5eddc1e1:11 in state RUN
2020-06-08 10:18:17.052Z WARN [Transaction Rea] c.a.a.arjuna {{}} ARJUNA012121: TransactionReaper::doCancellations worker Thread[Transaction Reaper Worker 0,5,main] successfully canceled TX 0:ffff0a2b0205:30337369:5eddc1e1:11
2020-06-08 10:18:18.002Z WARN [Transaction Rea] c.a.a.arjuna {{}} ARJUNA012117: TransactionReaper::check timeout for TX 0:ffff0a2b0205:30337369:5eddc1e1:14 in state RUN
2020-06-08 10:18:18.006Z WARN [Transaction Rea] c.a.a.arjuna {{}} ARJUNA012095: Abort of action id 0:ffff0a2b0205:30337369:5eddc1e1:14 invoked while multiple threads active within it.
2020-06-08 10:18:18.011Z WARN [Transaction Rea] c.a.a.arjuna {{}} ARJUNA012381: Action id 0:ffff0a2b0205:30337369:5eddc1e1:14 completed with multiple threads - thread ServerService Thread Pool – 68 was in progress with java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
java.net.SocketInputStream.read(SocketInputStream.java:171)
java.net.SocketInputStream.read(SocketInputStream.java:141)
com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107)
com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150)
com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180)
java.io.FilterInputStream.read(FilterInputStream.java:133)
com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)
com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52)
com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41)
com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54)
com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44)
com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:535)
com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:711)
com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:650)
com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:949)
com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:895)
com.mysql.cj.NativeSession.execSQL(NativeSession.java:1111)
com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:724)
com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
org.jboss.jca.adapters.jdbc.WrappedStatement.execute(WrappedStatement.java:198)
liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:307)
liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:113)
liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1277)
liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1259)
liquibase.changelog.ChangeSet.execute(ChangeSet.java:582)
liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79)
liquibase.Liquibase.update(Liquibase.java:214)
liquibase.Liquibase.update(Liquibase.java:192)
liquibase.Liquibase.update(Liquibase.java:188)
org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.updateChangeSet(LiquibaseJpaUpdaterProvider.java:182)
org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:102)
org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:81)
org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory$2.run(DefaultJpaConnectionProviderFactory.java:341)
org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:227)
org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.update(DefaultJpaConnectionProviderFactory.java:334)
org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.migration(DefaultJpaConnectionProviderFactory.java:306)
org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.lambda$lazyInit$0(DefaultJpaConnectionProviderFactory.java:182)
org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory$$Lambda$858/1044540519.run(Unknown Source)
org.keycloak.models.utils.KeycloakModelUtils.suspendJtaTransaction(KeycloakModelUtils.java:682)
org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.lazyInit(DefaultJpaConnectionProviderFactory.java:133)
org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:81)
org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:59)
org.keycloak.services.DefaultKeycloakSession.getProvider(DefaultKeycloakSession.java:204)
org.keycloak.models.jpa.JpaRealmProviderFactory.create(JpaRealmProviderFactory.java:51)
org.keycloak.models.jpa.JpaRealmProviderFactory.create(JpaRealmProviderFactory.java:33)
org.keycloak.services.DefaultKeycloakSession.getProvider(DefaultKeycloakSession.java:204)
org.keycloak.services.DefaultKeycloakSession.realmLocalStorage(DefaultKeycloakSession.java:157)
org.keycloak.models.cache.infinispan.RealmCacheSession.getRealmDelegate(RealmCacheSession.java:148)
org.keycloak.models.cache.infinispan.RealmCacheSession.getMigrationModel(RealmCacheSession.java:141)
org.keycloak.migration.MigrationModelManager.migrate(MigrationModelManager.java:97)
org.keycloak.services.resources.KeycloakApplication.migrateModel(KeycloakApplication.java:244)
org.keycloak.services.resources.KeycloakApplication.migrateAndBootstrap(KeycloakApplication.java:185)
org.keycloak.services.resources.KeycloakApplication$1.run(KeycloakApplication.java:147)
org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:227)
org.keycloak.services.resources.KeycloakApplication.startup(KeycloakApplication.java:138)
org.keycloak.services.resources.KeycloakApplication$$Lambda$834/704250421.run(Unknown Source)
org.keycloak.provider.wildfly.WildflyPlatform.onStartup(WildflyPlatform.java:29)
org.keycloak.services.resources.KeycloakApplication.(KeycloakApplication.java:125)
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:423)
org.jboss.resteasy.core.ConstructorInjectorImpl.construct(ConstructorInjectorImpl.java:152)
org.jboss.resteasy.spi.ResteasyProviderFactory.createProviderInstance(ResteasyProviderFactory.java:2805)
org.jboss.resteasy.spi.ResteasyDeployment.createApplication(ResteasyDeployment.java:369)
org.jboss.resteasy.spi.ResteasyDeployment.startInternal(ResteasyDeployment.java:281)
org.jboss.resteasy.spi.ResteasyDeployment.start(ResteasyDeployment.java:92)
org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.init(ServletContainerDispatcher.java:119)
org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.init(HttpServletDispatcher.java:36)
io.undertow.servlet.core.LifecyleInterceptorInvocation.proceed(LifecyleInterceptorInvocation.java:117)
org.wildfly.extension.undertow.security.RunAsLifecycleInterceptor.init(RunAsLifecycleInterceptor.java:78)
io.undertow.servlet.core.LifecyleInterceptorInvocation.proceed(LifecyleInterceptorInvocation.java:103)
io.undertow.servlet.core.ManagedServlet$DefaultInstanceStrategy.start(ManagedServlet.java:305)
io.undertow.servlet.core.ManagedServlet.createServlet(ManagedServlet.java:145)
io.undertow.servlet.core.DeploymentManagerImpl$2.call(DeploymentManagerImpl.java:585)
io.undertow.servlet.core.DeploymentManagerImpl$2.call(DeploymentManagerImpl.java:556)
io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:42)
io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
org.wildfly.extension.undertow.security.SecurityContextThreadSetupAction.lambda$create$0(SecurityContextThreadSetupAction.java:105)
org.wildfly.extension.undertow.security.SecurityContextThreadSetupAction$$Lambda$790/429250079.call(Unknown Source)
org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1541)
org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction$$Lambda$791/103393049.call(Unknown Source)
org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1541)
org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction$$Lambda$791/103393049.call(Unknown Source)
org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1541)
org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction$$Lambda$791/103393049.call(Unknown Source)
org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1541)
org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction$$Lambda$791/103393049.call(Unknown Source)
io.undertow.servlet.core.DeploymentManagerImpl.start(DeploymentManagerImpl.java:598)
org.wildfly.extension.undertow.deployment.UndertowDeploymentService.startContext(UndertowDeploymentService.java:97)
org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:78)
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
java.util.concurrent.FutureTask.run(FutureTask.java:266)
org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1982)
org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1486)
org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1377)
java.lang.Thread.run(Thread.java:748)
org.jboss.threads.JBossThread.run(JBossThread.java:485)

2020-06-08 10:18:18.060Z WARN [Transaction Rea] c.a.a.arjuna {{}} ARJUNA012108: CheckedAction::check - atomic action 0:ffff0a2b0205:30337369:5eddc1e1:14 aborting with 1 threads active!
2020-06-08 10:18:18.061Z WARN [Transaction Rea] c.a.a.arjuna {{}} ARJUNA012121: TransactionReaper::doCancellations worker Thread[Transaction Reaper Worker 0,5,main] successfully canceled TX 0:ffff0a2b0205:30337369:5eddc1e1:14
2020-06-08 10:18:18.339Z WARN [ServerService T] c.a.a.arjuna {{}} ARJUNA012077: Abort called on already aborted atomic action 0:ffff0a2b0205:30337369:5eddc1e1:14
2020-06-08 10:18:18.340Z WARN [ServerService T] c.a.a.arjuna {{}} ARJUNA012077: Abort called on already aborted atomic action 0:ffff0a2b0205:30337369:5eddc1e1:11
2020-06-08 10:18:18.342Z WARN [ServerService T] c.a.a.arjuna {{}} ARJUNA012077: Abort called on already aborted atomic action 0:ffff0a2b0205:30337369:5eddc1e1:e
2020-06-08 10:18:18.342Z FATAL [ServerService T] o.k.services {{}} java.lang.RuntimeException: Failed to update database
2020-06-08 10:18:18.344Z INFO [ Thread-2] o.j.a.server {{}} WFLYSRV0220: Server shutdown has been requested via an OS signal

the datasource configuration in standalone.xml is as below:

connection url : jdbc:mysql://localhost:3306/keycloak?useSSL=false&allowPublicKeyRetrieval=true&connectTimeout=300000&characterEncoding=UTF-8
driver: mysql
pool:
min-pool-size 10
max-pool-size 30

security
username: keycloak
password : keycloak

validation:
valid-connection-checker class-name
validate-on-match: true
exception-sorter class-name:

timeout:

idle-timeout-minutes 100

statement
track-statements true

And the driver definition in standalone.xml is as below:
(driver name= mysql module = com.mysql)

com.mysql.cj.jdbc.MysqlXADataSource (xa-datasource-class com.mysql.cj.jdbc.MysqlXADataSource)

Database created as below:

image

Did someone faced this issue and figured out the resolution for this?

Thanks in Advance :slight_smile:

@abstractj, @stianst, @vramik, @mposolda, @others, Please do see and let know if you can figure out this issue

@abstractj, @stianst, @vramik, @mposolda, @others, Please do see and let know if you can figure out this issue

Can anyone help here?

Finally I was able to resolve issue with mysql8. I used mysql 8.0.20 with keycloak 10.0.2 along with mysql-connector-java-8.0.20.jar . Following changes i have to make in order to make it run

standalone.xml
under mysql Datasource, you need to add below property as
Preformatted text
`

Preformatted text

"<timeout> <blocking-timeout-millis>6000</blocking-timeout-millis> <allocation-retry>3</allocation-retry> </timeout>Preformatted text` "

the entry in standalone.xml then will look like this:

Blockquote
" datasource jndi-name=“java:jboss/datasources/KeycloakDS”
pool-name=“KeycloakDS” enabled=“true” use-java-context=“true”>
connection-url=jdbc:mysql://localhost:3306/keycloak?useSSL=false&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
driver =mysql
pool
min-pool-size=5
max-pool-size=15
security
user-name=ADD HERE YOUR DATABASE USER
password=ADD HERE YOUR DATABASE PASSWORD

                <validation>
                    <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                    <validate-on-match>true</validate-on-match>
                    <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                </validation>
                <statement>
                    <track-statements>true</track-statements>
                </statement>
                <timeout>
                    <blocking-timeout-millis>6000</blocking-timeout-millis>
                    <allocation-retry>3</allocation-retry>
                </timeout>
            </datasource>"

Blockquote

also you need to add below change in standalone.xml

“<coordinator-environment default-timeout=“36000” …/>”

apart from this the other change that i made is

Blockquote

driver name=“mysql” module=“com.mysql”>
xa-datasource-class =com.mysql.cj.jdbc.MysqlXADataSource

Once you do all the above changes keycloak will start working with mysql8.
The only issue that you will get here is while starting server for first time, it will take around 10-12 minutes but later on it will work smoothly.

SQL

mysql> CREATE USER ‘keycloak’@’%’ IDENTIFIED BY ‘keycloak’;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE DATABASE keycloak CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected, 2 warnings (0.13 sec)

mysql> GRANT ALL PRIVILEGES ON keycloak.* TO ‘keycloak’@’%’;
Query OK, 0 rows affected (0.07 sec)