Challenge with setting up MySQL

mshushkov · February 25, 2020, 5:26pm

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 ?

mshushkov · February 25, 2020, 5:53pm

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

MoroccanGeek · April 20, 2020, 4:01pm

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

bathe · May 9, 2020, 4:45am

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

pkadian · May 9, 2020, 6:02pm

Hi bathe,

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

connect to the jboss-cli
$Jboss_home/bin/jboss-cli.bat --controller=x.x.x.x:9990
Add modules
module add --name=com.mysql --resources=/path/to/mysql.jar --dependencies=javax.api,javax.transaction.api
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”}

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
enable data source
data-source --name=MysqlDS --enabled=true
reload server
relaod

Now your data source is ready.

Nikhiljbhandari · June 10, 2020, 6:56am

@bathe, @MoroccanGeek, @mshushkov, @pkadian, were u guys able to run keycloak 9/10 with mysql 8 ? 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

MoroccanGeek · June 10, 2020, 10:48am

@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

Nikhiljbhandari · June 17, 2020, 10:53am

@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)

MoroccanGeek · June 17, 2020, 12:39pm

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

vaibhavjpr · June 18, 2020, 7:22am

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

Nikhiljbhandari · June 19, 2020, 12:52am

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>

souravdalal · August 13, 2020, 7:37am

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.

Nikhiljbhandari · August 13, 2020, 10:21am

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>

souravdalal · August 13, 2020, 3:41pm

Many Thanks. It worked.

Veinoshau · September 15, 2020, 1:57am

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?

souravdalal · September 15, 2020, 6:05am

@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

Veinoshau · September 15, 2020, 8:54am

Finally it worked! Thanks

Topic		Replies	Views
Configuring MySQL with KeyCloak Configuring the server	6	1150	March 12, 2021
Keycloak setting up with mysql Configuring the server database	2	5766	June 10, 2020
Slave failing to connect to database Configuring the server	1	915	July 24, 2020
Problems connecting mysql server with keycloak Getting advice	11	6764	May 1, 2021
Unable to connect to external database (AWS RDS) Configuring the server	3	4616	June 18, 2020

Challenge with setting up MySQL

Related Topics