Challenge with setting up MySQL

Downloaded 9.0.0 version and trying to setup the usage of my local instance of DB. Based on the documentation created mysql/main folder under
modules/system/layers/keycloak/org

with module.xml and mysql-connector-java.8.0.19.jar

module.xml

<?xml version="1.0" encoding="UTF-8"?>
<resources>
    <resource-root path="mysql-connector-java-8.0.19.jar"/>
</resources>

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

in standalone.xml added

<drivers>
                <driver name="mysql" module="org.mysql">
                    <xa-datasource-class>com.mysql.cj.jdbc.Driver</xa-datasource-class>
                </driver>
                <driver name="h2" module="com.h2database.h2">
                    <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                </driver>
            </drivers>

and datasource as

<datasource jndi-name="java:jboss/datasources/MySQL" pool-name="MySQL" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
                <connection-url>jdbc:mariadb://localhost:13333/keycloak</connection-url>
                <driver>mysql</driver>
                <security>
                    <user-name>root</user-name>
                    <password>password</password>
                </security>
            </datasource>

when start via: sh standalone.sh getting:

10:01:17,667 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.Driver”

and

10:01:31,559 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("add") failed - address: ([
("subsystem" => "datasources"),
("data-source" => "MySQL")

]) - failure description: {
“WFLYCTL0412: Required services that are not installed:” => [“jboss.jdbc-driver.mysql”],
“WFLYCTL0180: Services with missing/unavailable dependencies” => [
“org.wildfly.data-source.MySQL is missing [jboss.jdbc-driver.mysql]”,
“jboss.driver-demander.java:jboss/datasources/MySQL is missing [jboss.jdbc-driver.mysql]”
]
}
10:01:31,612 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation (“add”) failed - address: ([
(“subsystem” => “datasources”),
(“data-source” => “MySQL”)
]) - failure description: {
“WFLYCTL0412: Required services that are not installed:” => [
“jboss.jdbc-driver.mysql”,
“jboss.jdbc-driver.mysql”
],
“WFLYCTL0180: Services with missing/unavailable dependencies” => [
“org.wildfly.data-source.MySQL is missing [jboss.jdbc-driver.mysql]”,
“jboss.driver-demander.java:jboss/datasources/MySQL is missing [jboss.jdbc-driver.mysql]”,
“org.wildfly.data-source.MySQL is missing [jboss.jdbc-driver.mysql]”
]
}

what I am missing / doing wrong ?

Had to use:
com.mysql.cj.jdbc.MysqlXADataSource instead.

1 Like

Hello, i’m new to keycloak and i need some guidance please. I’m trying to store the users in an external DB like MySQL, but in my UML diagrams, the user has a one-to-many relationship with another entity called “Product” how can i implement this ? Thank you

Hi Mshishkov,

Are you able to resolve your issue. I have tried with Keycloak 10 (current latest version) with MysqlXADataSource, but I am getting same errors. Any advice.

Thanks
Shirish

Hi bathe,

Try to add data sources using EAP cli mode and follow the below commands:

  1. connect to the jboss-cli
    $Jboss_home/bin/jboss-cli.bat --controller=x.x.x.x:9990
  2. Add modules
    module add --name=com.mysql --resources=/path/to/mysql.jar --dependencies=javax.api,javax.transaction.api
  3. Add subsystem
    /subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-class-name=com.mysql.jdbc.Driver)

{“outcome” => “success”}

  1. Add data source
    data-source add --name=MysqlDS --jndi-name=“java:jboss/MySqlDS” --driver-name=mysql --driver-class=com.mysql --connection-url=jdbc:mysql://x.x.x.x:3306/sso --user-name=poonam --password=poonam

  2. enable data source
    data-source --name=MysqlDS --enabled=true

  3. reload server
    relaod

Now your data source is ready.

@bathe, @MoroccanGeek, @mshushkov, @pkadian, were u guys able to run keycloak 9/10 with mysql 8 :slight_smile: ? I have used all the configuration as mentioned and facing issues.
More details can be found at Keycloak 10 with MYSQL 8
I would really appreciate any help here

@Nikhiljbhandari unfortunatley no luck, I’ve waisted alot of time trying to run them both but nothing was done. But right now i’m working with MySQL 5.7 and it works just fine

@MoroccanGeek, @others, 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)

1 Like

I will try this and reply to you if it’s working. Thank you

@Nikhiljbhandari can you pls share your module.xml file entry.

Below is the entry in 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.20.jar"/>
</resources>

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

Hi,
I have tried the steps mentioned but still getting the timeout exception with Keycloak 11 & Mysql 8. Can you please share the standalone.xml.

Below is delta file for standalone.xml

Blockquote

<?xml version="1.0" ?>
<profile>
	<!-- ************************************ Change Start ************************************ -->
	
	<subsystem xmlns="urn:jboss:domain:transactions:5.0">
		
		<!-- ************************************ Change Start for mysql8 ************************************ -->
		<coordinator-environment default-timeout="36000" statistics-enabled="${wildfly.transactions.statistics-enabled:${wildfly.statistics-enabled:false}}"/>
		<!-- ************************************ Change End ************************************ -->
	</subsystem>
	
	<subsystem xmlns="urn:jboss:domain:datasources:5.0">
		<datasources>
			<!-- ************************************ Change Start ************************************ -->
			 <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&amp;characterEncoding=UTF-8&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=UTC</connection-url>
                <driver>mysql</driver>
                <pool>
                    <min-pool-size>5</min-pool-size>
                    <max-pool-size>15</max-pool-size>
                </pool>
                <security>
                    <user-name>ADD HERE YOUR DATABASE USER</user-name>
                    <password>ADD HERE YOUR DATABASE PASSWORD</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>
                <statement>
                    <track-statements>true</track-statements>
                </statement>
                <timeout>
                    <blocking-timeout-millis>60000</blocking-timeout-millis>
                    <allocation-retry>3</allocation-retry>
                </timeout>
            </datasource>
           
			<!-- ************************************ Change End ************************************** -->
		
		
			<drivers>
				<!-- ************************************ Change Start: Add the below  drivers ************************************ -->
			
				<driver name="mysql" module="com.mysql">
					<!-- use this for mysql 5.x version
                    <driver-class>com.mysql.jdbc.Driver</driver-class> -->
					<!-- use this for mysql8 version -->
					<xa-datasource-class>com.mysql.cj.jdbc.MysqlXADataSource</xa-datasource-class>
				</driver>
				<!-- ************************************ Change End ************************************** -->
			</drivers>
		</datasources>
	</subsystem>	
	
	
</profile>

Many Thanks. It worked.

Hi, can I ask what version of Mysql 8 you’re using? I still get timeout exceptions. Also on what collation is your keycloak database configured?

@Veinoshau,
I am using mysql 8.0.21 & collation is utf8_unicode_ci. You can try with below command at the time of startup:
standalone.bat -Djboss.as.management.blocking.timeout=3600

Finally it worked! Thanks :grinning: