我正在尝试将一些导出的迁移数据导入到 MariaDB 数据库。
我可以成功地导入到 H2 数据库。
但是当你想在 MariaDB 第一，它创造了 87 数据库中的表而不是 91 表，并最终出错：
2018-04-22 14:13:33,275 INFO [org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider] (ServerService Thread Pool -- 58) Initializing database schema. Using changelog META-INF/jpa-changelog-master.xml
2018-04-22 14:18:22,393 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0348: Timeout after [300] seconds waiting for service container stability. Operation will roll back. Step that first updated the service container was 'add' at address '[
("core-service" => "management"),
("management-interface" => "http-interface")
]'这个新的日志块显示它几乎需要5mn的时间。太长了。
stacktrace的更多信息：
16:16:55,690 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 58) MSC000001: Failed to start service jboss.undertow.deployment.default-server.default-host./auth: org.jboss.msc.service.StartException in service jboss.undertow.deployment.default-server.default-host./auth: java.lang.RuntimeException: RESTEASY003325: Failed to construct public org.keycloak.services.resources.KeycloakApplication(javax.servlet.ServletContext,org.jboss.resteasy.core.Dispatcher)
at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:84)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
at org.jboss.threads.JBossThread.run(JBossThread.java:320)
Caused by: java.lang.RuntimeException: RESTEASY003325: Failed to construct public org.keycloak.services.resources.KeycloakApplication(javax.servlet.ServletContext,org.jboss.resteasy.core.Dispatcher)
at org.jboss.resteasy.core.ConstructorInjectorImpl.construct(ConstructorInjectorImpl.java:162)
at org.jboss.resteasy.spi.ResteasyProviderFactory.createProviderInstance(ResteasyProviderFactory.java:2298)
at org.jboss.resteasy.spi.ResteasyDeployment.createApplication(ResteasyDeployment.java:340)
at org.jboss.resteasy.spi.ResteasyDeployment.start(ResteasyDeployment.java:253)
at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.init(ServletContainerDispatcher.java:120)
at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.init(HttpServletDispatcher.java:36)
at io.undertow.servlet.core.LifecyleInterceptorInvocation.proceed(LifecyleInterceptorInvocation.java:117)
at org.wildfly.extension.undertow.security.RunAsLifecycleInterceptor.init(RunAsLifecycleInterceptor.java:78)
at io.undertow.servlet.core.LifecyleInterceptorInvocation.proceed(LifecyleInterceptorInvocation.java:103)
at io.undertow.servlet.core.ManagedServlet$DefaultInstanceStrategy.start(ManagedServlet.java:250)
at io.undertow.servlet.core.ManagedServlet.createServlet(ManagedServlet.java:133)
at io.undertow.servlet.core.DeploymentManagerImpl$2.call(DeploymentManagerImpl.java:565)
at io.undertow.servlet.core.DeploymentManagerImpl$2.call(DeploymentManagerImpl.java:536)
at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:42)
at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
at org.wildfly.extension.undertow.security.SecurityContextThreadSetupAction.lambda$create$0(SecurityContextThreadSetupAction.java:105)
at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1508)
at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1508)
at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1508)
at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1508)
at io.undertow.servlet.core.DeploymentManagerImpl.start(DeploymentManagerImpl.java:578)
at org.wildfly.extension.undertow.deployment.UndertowDeploymentService.startContext(UndertowDeploymentService.java:100)
at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:81)
... 6 more
Caused by: java.lang.RuntimeException: Failed to update database
at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:102)
at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:67)
at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.update(DefaultJpaConnectionProviderFactory.java:322)
at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.migration(DefaultJpaConnectionProviderFactory.java:292)
at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.lambda$lazyInit$0(DefaultJpaConnectionProviderFactory.java:179)
at org.keycloak.models.utils.KeycloakModelUtils.suspendJtaTransaction(KeycloakModelUtils.java:544)
at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.lazyInit(DefaultJpaConnectionProviderFactory.java:130)
at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:78)
at org.keycloak.connections.jpa.DefaultJpaConnectionProviderFactory.create(DefaultJpaConnectionProviderFactory.java:56)
at org.keycloak.services.DefaultKeycloakSession.getProvider(DefaultKeycloakSession.java:163)
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:163)
at org.keycloak.models.cache.infinispan.RealmCacheSession.getDelegate(RealmCacheSession.java:144)
at org.keycloak.models.cache.infinispan.RealmCacheSession.getMigrationModel(RealmCacheSession.java:137)
at org.keycloak.migration.MigrationModelManager.migrate(MigrationModelManager.java:76)
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:422)
at org.jboss.resteasy.core.ConstructorInjectorImpl.construct(ConstructorInjectorImpl.java:150)
... 28 more
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set META-INF/jpa-changelog-2.1.0.xml::2.1.0::bburke@redhat.com:
Reason: liquibase.exception.UnexpectedLiquibaseException: java.sql.SQLException: IJ031040: Connection is not associated with a managed connection: org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK8@55194ba1
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:573)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73)
at liquibase.Liquibase.update(Liquibase.java:210)
at liquibase.Liquibase.update(Liquibase.java:190)
at liquibase.Liquibase.update(Liquibase.java:186)
at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.updateChangeSet(LiquibaseJpaUpdaterProvider.java:135)
at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:88)
... 53 more
Caused by: liquibase.exception.UnexpectedLiquibaseException: java.sql.SQLException: IJ031040: Connection is not associated with a managed connection: org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK8@55194ba1
at liquibase.database.jvm.JdbcConnection.getURL(JdbcConnection.java:79)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:122)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1247)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1230)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:548)
... 60 more
Caused by: java.sql.SQLException: IJ031040: Connection is not associated with a managed connection: org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK8@55194ba1
at org.jboss.jca.adapters.jdbc.WrappedConnection.lock(WrappedConnection.java:164)
at org.jboss.jca.adapters.jdbc.WrappedConnection.getMetaData(WrappedConnection.java:913)
at liquibase.database.jvm.JdbcConnection.getURL(JdbcConnection.java:77)
... 65 more导出命令是：
$KEYCLOAK_HOME/bin/standalone.sh -Dkeycloak.migration.action=export -Dkeycloak.migration.provider=dir -Dkeycloak.migration.dir=exported_realms -Dkeycloak.migration.strategy=OVERWRITE_EXISTING失败的导入命令是：
$KEYCLOAK_HOME/bin/standalone.sh -Dkeycloak.migration.action=export -Dkeycloak.migration.provider=dir -Dkeycloak.migration.dir=exported_realms -Dkeycloak.migration.strategy=OVERWRITE_EXISTING以下是在 standalone/configuration/standalone.xml 文件：
<datasource jndi-name="java:/jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true">
<connection-url>jdbc:mysql://localhost:3306/keycloak?useSSL=false&characterEncoding=UTF-8</connection-url>
<driver>mysql</driver>
<pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>15</max-pool-size>
</pool>
<security>
<user-name>keycloak</user-name>
<password>xxxxxx</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>
</datasource>我在用 keycloak-3.4.1.Final 以及 mariadb-10.1.24 在java版本上 1.8.0_60 .
运行 ./mysqltuner.pl 实用程序显示：
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 2.0G/222.6M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 256.0M * 2/2.0G should be equal 25%
[OK] InnoDB buffer pool instances: 2
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[!!] InnoDB Read buffer efficiency: 63.85% (802 hits/ 1256 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
General recommendations:
Control warning line(s) into /home/stephane/programs/install/mariadb/mariadb.error.log file
1 CVE(s) found for your MySQL release. Consider upgrading your version !
MySQL started within last 24 hours - recommendations may be inaccurate
Dedicate this server to your database for highest performance.
Reduce or eliminate unclosed connections and network issues
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
3条答案
按热度按时间zbq4xfa01#
你的ulimit-a报告显示'打开的文件'是1024ulimit-n1000将扩大你的容量，以更好地容纳mysql。
在show global status中报告的1318秒中，我们统计了33个com\u回滚项，1个handler\u回滚可能是上面记录的所有java失败的结果。
为my.cnf-ini[mysqld]部分考虑的建议，可能会加快处理速度。
back to basics，inc.对本次进口加工的建议，2018年5月24日
复制并粘贴在[mysqld}的末尾为了快速测试，在时间允许的情况下从[mysqld]的顶部删除重复的变量名。这些不会解决记录的错误，但会加快处理速度。
如果时间允许，请提供反馈。
busg9geu2#
安装mysqltuner实用程序后：
我知道我的数据库服务器配置不当，写入速度太慢，导致导入操作超时。
然后我配置了
my.cnf与指令一起归档：允许导入成功完成的一个指令是：
更新：我评论了
innodb_flush_log_at_trx_commit = 2指令，以便再次触发错误。然后我可以根据下面的评论收集额外的信息。完整的
my.cnf文件：环境状态和变量
其他命令输出：
top、iostat和free命令是在导入迁移脚本执行期间执行的。
完整的mysql调谐器输出
xdyibdwo3#
这个错误的根本原因是linux服务器需要增加打开文件的数量。实际上，您需要首先调整您的bdd，因为当它非常慢时，这会导致超时。使用以下命令检查“打开文件”属性：
就我而言，我用了20万。
请用这个例子：