CODEWITHSUNDEEP
mysqlkeycloak
DrTeeth
DrTeeth

Reputation: 1317

How do I get Keycloak to connect to MySQL DB?

I've been crawling a number of sites like this trying to get Keycloak working with a MySQL persistence layer. I am using docker, but I'm using my own images so it pulls passwords and other sensitive data from a secrets manager instead of environment variables or Docker secrets. The images are pretty close to stock besides that however.

Anyway, I have a MySQL 8 container up and running, and from within the Keycloak 12.0.3 container I can connect to the MySQL container fine:

# mysql -h mysql -u keycloak --password=somethingtochangelater -D keycloak -e "SHOW DATABASES;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| keycloak           |
+--------------------+

So there's no problems of connectivity between the instances, and that username/password has access to the keycloak database fine.

So then I ran several commands to configure the Keycloak instance (keycloak is installed at /opt/myco/bin/keycloak):

/opt/myco/bin/keycloak/bin/standalone.sh &

# Pausing for server startup
sleep 20

# Add mysql module - JDBC driver unpacked at /opt/myco/bin/keycloak-install/mysql-connector-java-8.0.23/mysql-connector-java-8.0.23.jar
/opt/myco/bin/keycloak/bin/jboss-cli.sh --connect --command="module add --name=com.mysql --dependencies=javax.api,javax.transaction.api --resources=/opt/myco/bin/keycloak-install/mysql-connector-java-8.0.23/mysql-connector-java-8.0.23.jar --module-root-dir=/opt/myco/bin/keycloak/modules/system/layers/keycloak/"

# Removing h2 datasource
/opt/myco/bin/keycloak/bin/jboss-cli.sh --connect --command="/subsystem=datasources/data-source=KeycloakDS:remove"

# Adding MySQL datasource
/opt/myco/bin/keycloak/bin/jboss-cli.sh --connect --command="/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-class-name=com.mysql.cj.jdbc.Driver)"

# TODO - add connection pooling options here...
# Configuring data source
/opt/myco/bin/keycloak/bin/jboss-cli.sh --connect --command="data-source add --name=KeycloakDS --jndi-name=java:jboss/datasources/KeycloakDS --enabled=true --password=somethingtochangelater --user-name=keycloak --driver-name=com.mysql --use-java-context=true --connection-url=jdbc:mysql://mysql:3306/keycloak?useSSL=false&characterEncoding=UTF-8"

# Testing connection
/opt/myco/bin/keycloak/bin/jboss-cli.sh --connect --command="/subsystem=datasources/data-source=KeycloakDS:test-connection-in-pool"

# Creating admin user
/opt/myco/bin/keycloak/bin/add-user-keycloak.sh -r master -u "admin" -p "somethingelse"

# Shutting down initial server
/opt/myco/bin/keycloak/bin/jboss-cli.sh --connect command=":shutdown"

This all appears to run fine. Note especially the test-connection-in-pool has no problems:

{
    "outcome" => "success",
    "result" => [true],
    "response-headers" => {"process-state" => "reload-required"}
}

However, when I go to start the server back up again, it crashes with several exceptions, starting with:

22:31:52,484 FATAL [org.keycloak.services] (ServerService Thread Pool -- 56) Error during startup: java.lang.RuntimeException: Failed to connect to database
        at [email protected]//org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.getConnection(DefaultJpaConnectionProviderFactory.java:377)
        at [email protected]//org.keycloak.connections.jpa.updater.liquibase.lock.LiquibaseDBLockProvider.lazyInit(LiquibaseDBLockProvider.java:65)
...

it keeps going, though I suspect that Exception ultimately to be fatal, and it eventually dies with:

22:31:53,114 ERROR [org.jboss.as.controller.management-operation] (ServerService Thread Pool -- 40) WFLYCTL0190: Step handler org.jboss.as.controller.AbstractAddStepHandler$1@33063168 for operation add at address [
    ("subsystem" => "jca"),
    ("workmanager" => "default"),
    ("short-running-threads" => "default")
] failed -- java.util.concurrent.RejectedExecutionException: java.util.concurrent.RejectedExecutionException
        at [email protected]//org.jboss.threads.RejectingExecutor.execute(RejectingExecutor.java:37)
        at [email protected]//org.jboss.threads.EnhancedQueueExecutor.rejectShutdown(EnhancedQueueExecutor.java:2029)
...

The module at /opt/myco/bin/keycloak/modules/system/layers/keycloak/com/mysql/main has the jar file and module.xml:

# ls
module.xml  mysql-connector-java-8.0.23.jar
# cat module.xml
<?xml version='1.0' encoding='UTF-8'?>

<module xmlns="urn:jboss:module:1.1" name="com.mysql">

    <resources>
        <resource-root path="mysql-connector-java-8.0.23.jar"/>
    </resources>

    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>

The standalone.xml file looks reasonable to me:

...
        <subsystem xmlns="urn:jboss:domain:datasources:6.0">
            <datasources>
...
                <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
                    <connection-url>jdbc:mysql://mysql:3306/keycloak?useSSL=false&amp;characterEncoding=UTF-8</connection-url>
                    <driver>com.mysql</driver>
                    <security>
                        <user-name>keycloak</user-name>
                        <password>somethingtochangelater</password>
                    </security>
                </datasource>
                <drivers>
                    <driver name="h2" module="com.h2database.h2">
                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                    </driver>
                    <driver name="mysql" module="com.mysql">
                        <driver-class>com.mysql.cj.jdbc.Driver</driver-class>
                    </driver>
                </drivers>
            </datasources>
...

So.... anyone have any idea what's going on? What else do I need to do to get Keycloak talking properly to MySQL? Anything else I can do to debug what the issue is?

Upvotes: 0

Views: 6400

Answers (1)

Martina
Martina

Reputation: 929

Not sure what is wrong with your particular case, but I used jboss/ keycloak image and it connects to MySQL just fine. Maybe you can derive your custom image from there. The full setup in my blog post https://link.medium.com/eK6IRducpeb

Upvotes: 2

Related Questions