Problems connecting mysql server with keycloak

Getting advice
1

Hi everybody,

i tried to connect the keycloak settings to a mysql database instead of the keycloak database that was already present at delivery

Unfortunately so far without success or error when restarting keycloak.

Maybe someone has an idea where the problem can be or what I have implemented wrong.

1.) Installed mysql 5.7 and created database “keycloak” with user
2.) Created folder …/modules/system/layers/keycloak/com/mysql/main/module.xml and added mysql-connector-java-8.0.24.jar

3.) Edit …/standalone/configuration/standalone.xml (drivers, datasource and spi)

Error:

Failed to connect to database - Unable to create connection

Maybe someone here can help me

Many thanks for your help

Using Keycloak 12.0.4 (Distribution powered by WildFly) on Cent OS 7

2

@xkey
Hello,

Not sure how to fix your issue, but I have succeeded in using MySQL. My documents for this is really long so Im going to giving you the MySQL configurations I have. You may have to adjust these steps for your needs.

Install mysql java connector

cd /opt/
wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.47.zip
unzip mysql-connector-java-5.1.47.zip

Configure database

mysql -u root -p
CREATE DATABASE keycloak CHARACTER SET utf8 COLLATE utf8_unicode_ci;
GRANT ALL PRIVILEGES ON keycloak.* TO 'keycloak'@'localhost' identified by 'Password1';
FLUSH privileges;
exit;

These missing steps here was: Enable and start MySQL, Create a keycloak user, Download keycloak, Create keycloak as a service.

Configure wildfly

./bin/jboss-cli.sh 
module add --name=org.mysql --dependencies=javax.api,javax.transaction.api --resources=/opt/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar
exit
./bin/jboss-cli.sh 'embed-server,/subsystem=datasources/jdbc-driver=org.mysql:add(driver-name=org.mysql,driver-module-name=org.mysql,driver-class-name=com.mysql.jdbc.Driver)'
./bin/jboss-cli.sh 'embed-server,/subsystem=datasources/data-source=KeycloakDS:remove'
./bin/jboss-cli.sh 'embed-server,/subsystem=datasources/data-source=KeycloakDS:add(driver-name=org.mysql,enabled=true,use-java-context=true,connection-url="jdbc:mysql://localhost:3306/keycloak?useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Europe/Budapest&characterEncoding=UTF-8",jndi-name="java:/jboss/datasources/KeycloakDS",user-name=keycloak,password="Password1",valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker,validate-on-match=true,exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker)'
./bin/add-user-keycloak.sh -u admin -p Password1 -r master

Create Directory for MySQL Java Connector

mkdir -p /opt/keycloak/modules/system/layers/keycloak/com/mysql/main
cd /opt/keycloak/modules/system/layers/keycloak/com/mysql/main
cp /opt/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47-bin.jar .

Create module XML file

touch module.xml

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

Hope that helps

1 Like
3

@gsmith many thank for your reply, I will compare your config with mine.

I get only one ERROR when i restart keycloak:

08:xx:xx,585 ERROR [org.jboss.as.controller.management-operation] (ServerService Thread Pool – 32) WFLYCTL0013: Operation (“add”) failed - address: ([
(“subsystem” => “datasources”),
(“jdbc-driver” => “mysql”)
]) - failure description: “WFLYJCA0114: Failed to load datasource class: com.mysql.cj.jdbc.MysqlXADataSource”

4

Hello,

I havent seen that error yet, but I came across these sites today, Maybe it will help you troubleshoot.

How to Use Keycloak with MySQL Database (Quarkus) - TutorialsBuddy.com.

Hope that helps.

1 Like
5

08:xx:xx,585 ERROR [org.jboss.as.controller.management-operation] (ServerService Thread Pool – 32) WFLYCTL0013: Operation (“add”) failed - address: ([
(“subsystem” => “datasources”),
(“jdbc-driver” => “mysql”)
]) - failure description: “WFLYJCA0114: Failed to load datasource class: com.mysql.cj.jdbc.MysqlXADataSource”

your xa-datasource-class path is wrong

example:
my postgresql.jar is under modules/system/layers/keycloak/org/postgresql/main/

main
|-- module.xml
`-- postgresql.jar

module: “org.postgresql”
- xa-datasource-class: “org.postgresql.xa.PGXADataSource”

1 Like
6

@gsmith do you get error messages when starting keycloak after database configuration?

I have taken it 1:1 so in my test environment ",bin/standalone.sh -b 0.0.0.0 -bmanagement 0.0.0.0
"

7

@firefox this is my current configuration in the file "./standalone/configuration/standalone.xml
"

com.mysql.jdbc.Driver com.mysql.cj.jdbc.MysqlXADataSource org.h2.jdbcx.JdbcDataSource
8

In any case, if you are using mysql-connector-java 8.0.xx,
currently you can not use versions above 8.0.22.
There is an open keycloak ticket which can not be fixes by library Update.

Technical Background is Liquibase issue due to mysql change because of a change in MySQL 8.0.23

1 Like
9

@psytester many thanks for your replay

I first tried to use the newest mysql-connector-java from https://mvnrepository.com/artifact/mysql/mysql-connector-java “mysql-connector-java-8.0.24.jar” then I also used “mysql-connector-java-8.0.21.jar” and at the moment I use “mysql-connector-java-5.1.47-bin.jar” like @gsmith who shared his MySQL configuration description with us.

1 Like
10

No, not that I recall. I did have permission issues. In the beginning it was kinda hard to set up , hence why I made a documention on how to set it up for later use.

1 Like
11

Here is a snipet of my standalone.xml file not sure if this helps, It was for my lab testing Keycloak /w MySQL.

image
image1664×671 212 KB

Hope that helps

EDIT:
This link below was what I used to start with and was able to get things going.

1 Like
12

Now I’ve been able to solve the problem after a few more attempts as follows:

<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>
				   
		    <driver name="org.mysql" module="org.mysql">
                           <driver-class>com.mysql.jdbc.Driver</driver-class>
                      </driver>
</drivers>
1 Like