Connect mysql server to keycloak

Keycloak works with embedded H2 database. This is the default database that Keycloak uses in the development environment. But for production, the proper thing to do is to connect with an external database like mysql, mongo db. Below we will see the steps for using MySQL Server:
MYSQL SETUP
Create a database for keycloak

1. mysql> CREATE USER ‘keycloak’@’%’ IDENTIFIED BY ‘keycloak’;
2. mysql> CREATE DATABASE keycloak CHARACTER SET utf8 COLLATE utf8_unicode_ci

3.mysql> GRANT ALL PRIVILEGES ON keycloak.* TO ‘keycloak’@’%’;a

JDBC SETUP

Step 1: Download the mysql Connector from
https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.42.zip
Step 2: Create a file named module.xml( the file exention should be .xml)
Step 3: Copy and paste the code below it inside the file you created above

<?xml version=”1.0" ?>
<module xmlns=”urn:jboss:module:1.3" name=”com.mysql”>
<resources>
<resource-root path=”mysql-connector-java-5.1.42-bin.jar” />
</resources>
<dependencies>
<module name=”javax.api”/>
<module name=”javax.transaction.api”/>
</dependencies>
</module>

Step 4:Cd to KEYCLOAK_HOME/modules/system/layers/keycloak/com
Step 5: Create a folder named ‘mysql’ inside ‘com’ folder then create a folder named ‘main’ inside the mysql folder
it should like this “KEYCLOAK_HOME/modules/system/layers/keycloak/com/mysql/main”
Step 6: Copy the mysql connector you downloaded and paste it inside the main folder
Step 7: Copy the module.xml file you created above, and paste inside the main folder
Step 8: Declare your packeaged JBDC driver .
You have to edit “KEYCLOAK_HOME/standalone/configuration/standalone.xml”
Inside drivers XML block within the datasources subsystem, you should see a pre-defined driver declared for the H2 JDBC driver. This is where you’ll declare the MySQL JDBC driver.
You should have something like this after declaring your mysql JBDC driver in the datasouces subsystem

<subsystem xmlns=”urn:jboss:domain:datasources:4.0">
<datasources>
…
<drivers>
<driver name=”mysql” module=”com.mysql”>
<driver-class>com.mysql.jdbc.Driver</driver-class>
</driver>
<driver name=”h2" module=”com.h2database.h2">
<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
</driver>
</drivers>
</datasources>
</subsystem>

Step 9: Now we have to modify the existing datasource configuration that Keycloak uses to connect it to MySQL. This has to be done in the same file registered the mysql jdbc driver
Add the data source configuration to change from h2 database to MySQL.
My Sample configuration looks like this.

<datasource jndi-name=”java:/jboss/datasources/KeycloakDS” pool-name=”KeycloakDS” enabled=”true”>
<connection-url>jdbc:mysql://localhost:3306/keycloak?useSSL=false&characterEncoding=UTF-8</connection-url>
<driver>mysql</driver>
<pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>15</max-pool-size>
</pool>
<security>
<user-name>keycloak</user-name>
<password>keycloak</password>
</security>
<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>
</datasource>

NOTE: make sure that you changed the database server IP, Username and Password for the database server as per your configurations.
You will have to comment out this place

<! — <datasource jndi-name=”java:jboss/datasources/KeycloakDS” pool-name=”KeycloakDS” enabled=”true” use-java-context=”true” statistics-enabled=”${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}”>
<connection-url>jdbc:h2:${jboss.server.data.dir}/keycloak;AUTO_SERVER=TRUE</connection-url>
<driver>h2</driver>
<security>
<user-name>sa</user-name>
<password>sa</password>
</security>
</datasource> !-->

Step 10: Lastly, you have to increase keyclaok server’s timeout, defaultly its 5 minutes, but its takes more than 5 minutes for keycloak to connect with mysql initially, so it keeps rolling back when its 5 minutes exactly.
To do this, You have to edit KEYCLOAK_HOME/standalone/configuration/standalone-ha.xml

• Change the session-bean defualt accesstime out to 20000 from 5000

<session-bean>
<stateless>
<bean-instance-pool-ref pool-name=”slsb-strict-max-pool”/>
</stateless>
<stateful default-access-timeout=”20000" cache-ref=”distributable” passivation-disabled-cache-ref=”simple”/>
<singleton default-access-timeout=”20000"/>
</session-bean>

• Change the pool instance-acquisition-timeout in the pool tag from 5 minutes to 20 minutes

<pools>
<bean-instance-pools>
<strict-max-pool name=”mdb-strict-max-pool” derive-size=”from-cpu-count” instance-acquisition-timeout=”20" instance-acquisition-timeout-unit=”MINUTES”/>
<strict-max-pool name=”slsb-strict-max-pool” derive-size=”from-worker-pools” instance-acquisition-timeout=”20" instance-acquisition-timeout-unit=”MINUTES”/>
</bean-instance-pools>
</pools>

• Change the distributed-cache expiration interval from 300000 900000 minutes

<distributed-cache name=”actionTokens” owners=”2">
<object-memory size=”-1"/>
<expiration max-idle=”-1" interval=”900000"/>
</distributed-cache>

Restart your server after this process, BOOM!!! your keyclaok server is connected. Thank you. I hope this help

Comments

[salamskr]

I am getting the following error:
12:48:02,871 FATAL org.keycloak.services Error during startup: java.lang.RuntimeException: Failed to connect to database
at org.keycloak.keycloak-model-jpa@15...//org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.getConnection(DefaultJpaConnectionProviderFactory.java:391)
at org.keycloak.keycloak-model-jpa@15...//org.keycloak.connections.jpa.updater.liquibase.lock.LiquibaseDBLockProvider.lazyInit(LiquibaseDBLockProvider.java:65)
at org.keycloak.keycloak-model-jpa@15...//org.keycloak.connections.jpa.updater.liquibase.lock.LiquibaseDBLockProvider.lambda$waitForLock$2(LiquibaseDBLockProvider.java:96)
at org.keycloak.keycloak-server-spi-p...//org.keycloak.models.utils.KeycloakModelUtils.suspendJtaTransaction(KeycloakModelUtils.java:758)
at org.keycloak.keycloak-model-jpa@15...//org.keycloak.connections.jpa.updater.liquibase.lock.LiquibaseDBLockProvider.waitForLock(LiquibaseDBLockProvider.java:94)
at org.keycloak.keycloak-services@15.0.2//org.keycloak.services.resources.KeycloakApplication$1.run(KeycloakApplication.java:134)
at org.keycloak.keycloak-server-spi-p...//org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:250)
at org.keycloak.keycloak-services@15.0.2//org.keycloak.services.resources.KeycloakApplication.startup(KeycloakApplication.java:128)
at org.keycloak.keycloak-wildfly-exte...//org.keycloak.provider.wildfly.WildflyPlatform.onStartup(WildflyPlatform.java:36)
at org.keycloak.keycloak-services@15.0.2//org.keycloak.services.resources.KeycloakApplication.(KeycloakApplication.java:114)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
at org.jboss.resteasy.resteasy-jaxrs@...//org.jboss.resteasy.core.ConstructorInjectorImpl.construct(ConstructorInjectorImpl.java:152)
at org.jboss.resteasy.resteasy-jaxrs@...//org.jboss.resteasy.spi.ResteasyProviderFactory.createProviderInstance(ResteasyProviderFactory.java:2835)
at org.jboss.resteasy.resteasy-jaxrs@...//org.jboss.resteasy.spi.ResteasyDeployment.createApplication(ResteasyDeployment.java:376)
at org.jboss.resteasy.resteasy-jaxrs@...//org.jboss.resteasy.spi.ResteasyDeployment.startInternal(ResteasyDeployment.java:288)
at org.jboss.resteasy.resteasy-jaxrs@...//org.jboss.resteasy.spi.ResteasyDeployment.start(ResteasyDeployment.java:98)
at org.jboss.resteasy.resteasy-jaxrs@...//org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.init(ServletContainerDispatcher.java:140)
at org.jboss.resteasy.resteasy-jaxrs@...//org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.init(HttpServletDispatcher.java:42)
at [e-mail]//io.undertow.servlet.core.LifecyleInterceptorInvocation.proceed(LifecyleInterceptorInvocation.java:117)
at org.wildfly.extension.undertow@23....//org.wildfly.extension.undertow.security.RunAsLifecycleInterceptor.init(RunAsLifecycleInterceptor.java:78)
at [e-mail]//io.undertow.servlet.core.LifecyleInterceptorInvocation.proceed(LifecyleInterceptorInvocation.java:103)
at [e-mail]//io.undertow.servlet.core.ManagedServlet$DefaultInstanceStrategy.start(ManagedServlet.java:305)
at [e-mail]//io.undertow.servlet.core.ManagedServlet.createServlet(ManagedServlet.java:145)
at [e-mail]//io.undertow.servlet.core.DeploymentManagerImpl$2.call(DeploymentManagerImpl.java:588)
at [e-mail]//io.undertow.servlet.core.DeploymentManagerImpl$2.call(DeploymentManagerImpl.java:559)
at [e-mail]//io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:42)
at [e-mail]//io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
at org.wildfly.extension.undertow@23....//org.wildfly.extension.undertow.security.SecurityContextThreadSetupAction.lambda$create$0(SecurityContextThreadSetupAction.java:105)
at org.wildfly.extension.undertow@23....//org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1530)
at org.wildfly.extension.undertow@23....//org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1530)
at org.wildfly.extension.undertow@23....//org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1530)
at org.wildfly.extension.undertow@23....//org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1530)
at [e-mail]//io.undertow.servlet.core.DeploymentManagerImpl.start(DeploymentManagerImpl.java:601)
at org.wildfly.extension.undertow@23....//org.wildfly.extension.undertow.deployment.UndertowDeploymentService.startContext(UndertowDeploymentService.java:97)
at org.wildfly.extension.undertow@23....//org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:78)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at [e-mail]//org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
at [e-mail]//org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1990)
at [e-mail]//org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1486)
at [e-mail]//org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1377)
at java.base/java.lang.Thread.run(Thread.java:833)
at [e-mail]//org.jboss.threads.JBossThread.run(JBossThread.java:513)
Caused by: javax.naming.NameNotFoundException: datasources/KeycloakDS [Root exception is java.lang.IllegalStateException]

[ferpalma92]

Thank you very much for your help!! Of all the tutorials this is the only one that has worked. I have a question, the changes made in the standalone-ha.xml file should be left or should I return to the default settings.