Keycloak换sql server2008r2跳坑记(MS SQL jdbc driver 6 and Wildfly)

Keycloak换sql server2008r2跳坑记(MS SQL jdbc driver 6 and Wildfly)

351发表于2019-12-22

在介绍Keycloak换sql server2008r2之前说说我配置keycloak数据库的心酸历程。

这几天在研究Keycloak,遇到一个奇怪的问题就是使用Keycloak8.0.1配置mysql8 win10下总是不成功,在jpa创建表的时候总是事务回滚。

类似如下的错误:

Caused by: java.lang.RuntimeException: Failed to update database
        at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:116)
        at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:81)
        at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory$2.run(DefaultJpaConnectionProviderFactory.java:336)
        at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:227)
        at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.update(DefaultJpaConnectionProviderFactory.java:329)
        at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.migration(DefaultJpaConnectionProviderFactory.java:301)
        at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.lambda$lazyInit$0(DefaultJpaConnectionProviderFactory.java:182)
        at org.keycloak.models.utils.KeycloakModelUtils.suspendJtaTransaction(KeycloakModelUtils.java:682)
        at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.lazyInit(DefaultJpaConnectionProviderFactory.java:133)
        at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:81)
        at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:59)
        at org.keycloak.services.DefaultKeycloakSession.getProvider(DefaultKeycloakSession.java:195)
        at org.keycloak.models.jpa.JpaRealmProviderFactory.create(JpaRealmProviderFactory.java:51)
        at org.keycloak.models.jpa.JpaRealmProviderFactory.create(JpaRealmProviderFactory.java:33)
        at org.keycloak.services.DefaultKeycloakSession.getProvider(DefaultKeycloakSession.java:195)
        at org.keycloak.services.DefaultKeycloakSession.realmLocalStorage(DefaultKeycloakSession.java:152)
        at org.keycloak.models.cache.infinispan.RealmCacheSession.getRealmDelegate(RealmCacheSession.java:148)
        at org.keycloak.models.cache.infinispan.RealmCacheSession.getMigrationModel(RealmCacheSession.java:141)
        at org.keycloak.migration.MigrationModelManager.migrate(MigrationModelManager.java:89)
        at org.keycloak.services.resources.KeycloakApplication.migrateModel(KeycloakApplication.java:246)
        at org.keycloak.services.resources.KeycloakApplication.migrateAndBootstrap(KeycloakApplication.java:187)
        at org.keycloak.services.resources.KeycloakApplication$1.run(KeycloakApplication.java:146)
        at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:227)
        at org.keycloak.services.resources.KeycloakApplication.<init>(KeycloakApplication.java:137)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at org.jboss.resteasy.core.ConstructorInjectorImpl.construct(ConstructorInjectorImpl.java:152)
        ... 31 more
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set META-INF/jpa-changelog-1.4.0.xml::1.4.0::bburke@redhat.com:
     Reason: liquibase.exception.UnexpectedLiquibaseException: java.sql.SQLException: IJ031070: Transaction cannot proceed: STATUS_ROLLEDBACK
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:607)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79)
        at liquibase.Liquibase.update(Liquibase.java:214)
        at liquibase.Liquibase.update(Liquibase.java:192)
        at liquibase.Liquibase.update(Liquibase.java:188)
        at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.updateChangeSet(LiquibaseJpaUpdaterProvider.java:182)
        at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:102)
        ... 59 more
Caused by: liquibase.exception.UnexpectedLiquibaseException: java.sql.SQLException: IJ031070: Transaction cannot proceed: STATUS_ROLLEDBACK
        at liquibase.database.jvm.JdbcConnection.getURL(JdbcConnection.java:79)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:66)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:113)
        at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1277)
        at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1259)
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:582)
        ... 66 more
Caused by: java.sql.SQLException: IJ031070: Transaction cannot proceed: STATUS_ROLLEDBACK
        at org.jboss.jca.adapters.jdbc.WrapperDataSource.checkTransactionActive(WrapperDataSource.java:271)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.checkTransactionActive(WrappedConnection.java:1989)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:2004)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:1978)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.getMetaData(WrappedConnection.java:977)
        at liquibase.database.jvm.JdbcConnection.getURL(JdbcConnection.java:77)
        ... 71 more

这要导致创建表只创建了部分,再次启动又会报错“表已经存在”。

奇怪的是,同样是win10 连Linux的64位的mysql8.0.13没有问题,但是连Windows的64位的mysql8.0.13且报上面的错误(zip和msi都试过)

后来我换成连windows下的mysql5.5.35(32位),又没有问题。

于是我怀疑可能是mysql的驱动或keycloak版本问题,换了多个版本问题依旧。甚至我自己下了keycloak的源码(keycloak-4.8.3.Final)build,连Windows的64位的mysql8.0.13。又报下面各种的问题:

21:48:48,694 INFO  [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-3) WFLYJCA0019: Stopped Driver service with driver-name = mysql
21:48:48,998 WARN  [com.arjuna.ats.arjuna] (ServerService Thread Pool -- 54) ARJUNA012077: Abort called on already aborted atomic action 0:ffff0a0c0322:1f32a936
:5dff7300:12
21:48:48,999 ERROR [org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService] (ServerService Thread Pool -- 54) Database error during release lock:
 liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: IJ031040: Connection is not associated with a managed conn
ection: org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK8@7789cfd3
        at liquibase.database.AbstractJdbcDatabase.commit(AbstractJdbcDatabase.java:1159)
        at org.keycloak.connections.jpa.updater.liquibase.lock.CustomLockService.releaseLock(CustomLockService.java:184)
        at org.keycloak.connections.jpa.updater.liquibase.lock.LiquibaseDBLockProvider.lambda$releaseLock$1(LiquibaseDBLockProvider.java:126)
        at org.keycloak.models.utils.KeycloakModelUtils.suspendJtaTransaction(KeycloakModelUtils.java:678)
        at org.keycloak.connections.jpa.updater.liquibase.lock.LiquibaseDBLockProvider.releaseLock(LiquibaseDBLockProvider.java:123)
        at org.keycloak.services.resources.KeycloakApplication$1.run(KeycloakApplication.java:152)
        at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:227)

总之各种启动报错,报错之前都是要hung住挺长一段时间。不知道是不是win10下的mysql问题,还是keycloak的问题?


后来我想想是不是该放弃windows下的mysql8,要不要试试sqlserver呢?

1、首先,去下载Microsoft JDBC Driver 6.0 for SQL Server  

下载后解压找jdk版本对应的驱动文件

from clipboard

2、创建Module

创建目录\modules\system\layers\base\com\microsoft\sqlserver\main,把sqljdbc42.jar拷贝到main文件夹,创建文件module.xml

from clipboard

module.xml内容如下:

<?xml version="1.0" encoding="UTF-8"?>
<module name="com.microsoft.sqlserver" xmlns="urn:jboss:module:1.3">
    <resources>
        <resource-root path="sqljdbc42.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

3、修改datasource与driver

文件:keycloak-4.8.3.Final\standalone\configuration\standalone.xml

<drivers>增加一个driver节点:


 <driver name="sqlserver" module="com.microsoft.sqlserver">
                        <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
                        <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
                   </driver>


datasources增加一个datasource


<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
                    <connection-url>jdbc:sqlserver://localhost:1433;databaseName=keycloak-4.8.3.Final</connection-url>
                    <driver>sqlserver</driver>
                    <security>
                        <user-name>sa</user-name>
                        <password>sa</password>
                    </security>
                </datasource>
然后把原来h2或mysql datasource注释掉。


最终如下:

from clipboard


4、启动bin下面的standalone.bat


from clipboard


报如下错误:

Caused by: java.lang.ClassNotFoundException: javax.xml.bind.DatatypeConverter from [Module "com.microsoft.sqlserver" from local module loader @3fb6a447

找不到,核对了目录和配置没有问题呢!

网上再找了找资料原来还少了依赖。

感谢帖子:https://developer.jboss.org/thread/273816

from clipboard

最终的module.xml如下:

from clipboard

修改保存后,再次重启standalone.bat完美解决报错问题。

from clipboard

输入地址:http://localhost:8080创建管理员账号


小编蓝狐