[keycloak-user] adding mysql or mariadb backend
Vlasta Ramik
vramik at redhat.com
Wed Mar 27 09:06:01 EDT 2019
Hey,
it's hard to say what could be wrong, anyway you can at least add some
loggers and try to read from those what is happening.
You should see something like
INFO [org.hibernate.dialect.Dialect] (ServerService Thread Pool -- 54) HHH000400: Using dialect: org.hibernate.dialect.MariaDB10Dialect
in the server log (if you've added the logger from the script I've sent
you earlier)
Then I recommend you to go thru wildfly documentation regarding
configuring datasources, installing jdbc drivers etc.
V.
On 3/26/19 3:59 PM, Andrew Meyer wrote:
> I see the database I created. But no Tables. I am using the free
> version....
>
> Welcome to the MariaDB monitor. Commands end with ; or \g.
> Your MariaDB connection id is 8904
> Server version: 10.1.38-MariaDB MariaDB Server
>
> Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
>
> MariaDB [(none)]> show databases;
> +--------------------+
> | Database |
> +--------------------+
> | information_schema |
> | keycloak |
> | test |
> +--------------------+
> 3 rows in set (0.02 sec)
>
> MariaDB [(none)]> use keycloak
> Database changed
> MariaDB [keycloak]> show tables;
> Empty set (0.00 sec)
>
> MariaDB [keycloak]>
>
>
>
> Sent from Yahoo Mail on Android
> <https://go.onelink.me/107872968?pid=InProduct&c=Global_Internal_YGrowth_AndroidEmailSig__AndroidUsers&af_wl=ym&af_sub1=Internal&af_sub2=Global_YGrowth&af_sub3=EmailSignature>
>
> On Tue, Mar 26, 2019 at 9:44 AM, Vlasta Ramik
> <vramik at redhat.com> wrote:
> On 3/26/19 3:15 PM, Andrew Meyer wrote:
> Should I see anything in the database? Tables, etc?
> yes, you should be able to see tables etc.
>
>>
>> On Tuesday, March 26, 2019, 8:44:34 AM CDT, Andrew Meyer
>> <andrewm659 at yahoo.com> <mailto:andrewm659 at yahoo.com> wrote:
>>
>>
>> I got it working.
>>
>> Sent from Yahoo Mail on Android
>> <https://go.onelink.me/107872968?pid=InProduct&c=Global_Internal_YGrowth_AndroidEmailSig__AndroidUsers&af_wl=ym&af_sub1=Internal&af_sub2=Global_YGrowth&af_sub3=EmailSignature>
>>
>> On Tue, Mar 26, 2019 at 4:35 AM, Vlasta Ramik
>> <vramik at redhat.com> <mailto:vramik at redhat.com> wrote:
>>
>> Hey,
>>
>> inline.
>>
>> On 3/22/19 3:08 PM, Andrew Meyer wrote:
>> Yes. Here is what I have in the standalone.xml:
>>
>> <subsystem xmlns="urn:jboss:domain:bean-validation:1.0"/>
>> <subsystem xmlns="urn:jboss:domain:core-management:1.0"/>
>> <subsystem xmlns="urn:jboss:domain:datasources:5.0">
>> <datasources>
>> <datasource jndi-name="java:jboss/datasources/ExampleDS"
>> pool-name="ExampleDS" enabled="true" use-java-context="true">
>> <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
>> <driver>h2</driver>
>> <security>
>> <user-name>sa</user-name>
>> <password>sa</password>
>> </security>
>> </datasource>
>> <datasource jndi-name="java:jboss/datasources/KeycloakDS"
>> pool-name="KeycloakDS" enabled="true" use-java-context="true">
>> <connection-url>jdbc:mysql://10.150.10.20:3306/keycloak?useSSL=false&amp;useLegacyDatetimeCode=false&amp</connection-url>
>> <driver>keycloak</driver>
>> the driver has to correspond to <driver name="mysql" .. so
>> mysql in this case, so you should update the cli scripts
>> accordingly.
>>
>>> <pool>
>>> <min-pool-size>10</min-pool-size>
>>> <max-pool-size>50</max-pool-size>
>>> <prefill>true</prefill>
>>> </pool>
>>> <security>
>>> <user-name>keycloak</user-name>
>>> <password>ChangeMe</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.jdbc.mysql"/>
>>> </drivers>
>>> </datasources>
>>> </subsystem>
>>> <subsystem xmlns="urn:jboss:domain:deployment-scanner:2.0">
>>> <deployment-scanner path="deployments"
>>> relative-to="jboss.server.base.dir" scan-interval="5000"
>>> runtime-failure-causes-rollback="${jboss.deployment.scanner.rollback.on.failure:false}"/>
>>> </subsystem>
>>> <subsystem xmlns="urn:jboss:domain:ee:4.0">
>>> <spec-descriptor-property-replacement>false</spec-descriptor-property-replacement>
>>>
>>>
>>>
>>> I think my connection url line my be incorrect...
>>>
>>> On Friday, March 22, 2019, 5:47:00 AM CDT, Vlasta Ramik
>>> <vramik at redhat.com> <mailto:vramik at redhat.com> wrote:
>>>
>>>
>>> On 3/22/19 4:09 AM, Andrew Meyer wrote:
>>> Yes, I took a look at this. Followed what was in the
>>> examples and still am getting the following:
>>>
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1502)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1502)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1502)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1502)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> io.undertow.servlet.core.DeploymentManagerImpl.start(DeploymentManagerImpl.java:596)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.wildfly.extension.undertow.deployment.UndertowDeploymentService.startContext(UndertowDeploymentService.java:97)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:78)
>>> Mar 21 22:03:54 saml01 standalone.sh: ... 8 more
>>> Mar 21 22:03:54 saml01 standalone.sh: Caused by:
>>> java.lang.RuntimeException: Failed to connect to database
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.getConnection(DefaultJpaConnectionProviderFactory.java:382)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.keycloak.connections.jpa.updater.liquibase.lock.LiquibaseDBLockProvider.lazyInit(LiquibaseDBLockProvider.java:65)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.keycloak.connections.jpa.updater.liquibase.lock.LiquibaseDBLockProvider.lambda$waitForLock$0(LiquibaseDBLockProvider.java:97)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.keycloak.models.utils.KeycloakModelUtils.suspendJtaTransaction(KeycloakModelUtils.java:678)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.keycloak.connections.jpa.updater.liquibase.lock.LiquibaseDBLockProvider.waitForLock(LiquibaseDBLockProvider.java:95)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.keycloak.services.resources.KeycloakApplication$1.run(KeycloakApplication.java:148)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:227)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.keycloak.services.resources.KeycloakApplication.<init>(KeycloakApplication.java:141)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
>>> Method)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> java.lang.reflect.Constructor.newInstance(Constructor.java:423)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.jboss.resteasy.core.ConstructorInjectorImpl.construct(ConstructorInjectorImpl.java:152)
>>> Mar 21 22:03:54 saml01 standalone.sh: ... 31 more
>>> Mar 21 22:03:54 saml01 standalone.sh: Caused by:
>>> javax.naming.NameNotFoundException: datasources/KeycloakDS
>>> [Root exception is java.lang.IllegalStateException]
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.jboss.as.naming.ServiceBasedNamingStore.lookup(ServiceBasedNamingStore.java:153)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.jboss.as.naming.ServiceBasedNamingStore.lookup(ServiceBasedNamingStore.java:83)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.jboss.as.naming.NamingContext.lookup(NamingContext.java:207)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.jboss.as.naming.NamingContext.lookup(NamingContext.java:184)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.jboss.as.naming.InitialContext$DefaultInitialContext.lookup(InitialContext.java:239)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.jboss.as.naming.NamingContext.lookup(NamingContext.java:193)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.jboss.as.naming.NamingContext.lookup(NamingContext.java:189)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> javax.naming.InitialContext.lookup(InitialContext.java:417)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> javax.naming.InitialContext.lookup(InitialContext.java:417)
>>> Mar 21 22:03:54 saml01 standalone.sh: at
>>> org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.getConnection(DefaultJpaConnectionProviderFactory.java:375)
>>> Mar 21 22:03:54 saml01 standalone.sh: ... 43 more
>>> This means exactly what is says that KeycloakDS cannot be
>>> found, have you looked at standalone.xml?
>>>
>>>>
>>>>
>>>>
>>>> Here is the command that I am running. I don't understand
>>>> what I am doing wrong.
>>>>
>>>> [root at saml01 current]# sudo -u keycloak ./bin/jboss-cli.sh
>>>> 'module add --name=com.jdbc.mysql
>>>> --resources=mysql-connector-java-5.1.47.jar
>>>> --dependencies=javax.api,javax.xml.bind.api'
>>>> Module com.jdbc.mysql already exists at
>>>> /opt/keycloak/5.0.0/modules/com/jdbc/mysql/main
>>>> [root at saml01 current]#
>>>>
>>>>
>>>>
>>>> I ran all of the commands as shown in the example and
>>>> keycloak still fails to start.
>>>>
>>>>
>>>>
>>>> On Thursday, March 21, 2019, 7:03:12 AM CDT, Vlasta Ramik
>>>> <vramik at redhat.com> <mailto:vramik at redhat.com> wrote:
>>>>
>>>>
>>>> Hello,
>>>>
>>>> you can take a look at
>>>> https://github.com/keycloak/keycloak/blob/cf35a4648bcb93aaf1ac63918ee5c4b0f422d7d5/testsuite/integration-arquillian/servers/auth-server/jboss/common/jboss-cli/configure-server-jpa.cli
>>>> <https://github.com/keycloak/keycloak/blob/cf35a4648bcb93aaf1ac63918ee5c4b0f422d7d5/testsuite/integration-arquillian/servers/auth-server/jboss/common/jboss-cli/configure-server-jpa.cli
>>>> >
>>>> for inspiration.
>>>>
>>>> V.
>>>>
>>>> On 3/19/19 9:43 PM, Andrew Meyer wrote:
>>>> > Hello,If I am adding a mariadb or mysql backend to
>>>> keycloak v4.8.3 or 5.0.0 what is the correct syntax from
>>>> the jboss-cli.sh tool? This is what I have in my notes.
>>>> > Open the Jboss CLI and add the MySQL driver (you don't
>>>> have to connect with the Jboss websocket).
>>>> > $ ./bin/jboss-cli.sh Is this the correct mysql connector
>>>> version for MariaDB 10.1.x?MySQL/MariaDBjboss-cli$ module
>>>> add --name=com.mysql
>>>> --dependencies=javax.api,javax.transaction.api
>>>> --resources=/root/mysql-connector-java-5.1.47.jar
>>>> >
>>>> > Add the Database driver to the configuration.
>>>> > MySQL/MariaDB# sudo su -
>>>> > Is this the correct syntax for the driver? Should it be
>>>> com.mysql or org.mysql??
>>>> > $ sudo -u keycloak ./bin/jboss-cli.sh
>>>> 'embed-server,/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-class-name=com.mysql.jdbc.Driver)'
>>>> >
>>>> > Remove the h2 KeycloakDS data source and add the MySQL
>>>> KeycloakDS data source. (Don't delete the test database and
>>>> change YOURPASS to something random)
>>>> > MySQL/MariaDB
>>>> > # sudo su -$ sudo -u keycloak ./bin/jboss-cli.sh
>>>> 'embed-server,/subsystem=datasources/data-source=KeycloakDS:remove'
>>>> > $ sudo -u keycloak ./bin/jboss-cli.sh
>>>> 'embed-server,/subsystem=datasources/data-source=asmDS:add(driver-name=com.mysql,enabled=true,use-java-context=true,connection-url="jdbc:mysql://10.150.10.20:3306/keycloak?useSSL=false&useLegacyDatetimeCode=false&serverTimezone=America/Chicago&characterEncoding=UTF-8",jndi-name="java:/jboss/datasources/KeycloakDS",user-name=keycloak,password="ChangeMe",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)'
>>>> > $ sudo -u keycloak ./bin/jboss-cli.sh
>>>> 'embed-server,/subsystem=datasources/data-source=asmDS:test-connection-in-pool'
>>>>
>>>> > _______________________________________________
>>>> > keycloak-user mailing list
>>>> > keycloak-user at lists.jboss.org
>>>> <mailto:keycloak-user at lists.jboss.org>
>>>> > https://lists.jboss.org/mailman/listinfo/keycloak-user
>>>>
More information about the keycloak-user
mailing list