MySQL Connection configuration
Hi Community,
I have used Wildfly for some time already and also MySQL-Datasources.
I use the mysql connector 5.1.4 and non-xa-datasource.
When I run wildfly on the same machine as the mysql server everything works as expected. But when I use a remote database server strange connection timeouts and "application stucks" happen. Wildfly brings up errors about non valid connections.
What should I do? I guess that the TCP-connections are terminated by the mysql server or somebody between th 2 servers when there is no traffic for some while and then the connections get stale ...
That is why I configured connection validation but it did not help...
Can you please help me and post a valid well proven mysql configuration? That would be great!
My Configuration:
Name: DS_COM_V1
JNDI: java:jboss/datasources/DS_COM_V1
Is enabled?: true
Statistics enabled?: false
Driver: mysql
Connection URL: jdbc:mysql://172.18.0.7/kcloud_dev_pp_appgw_common_v1_internal_db?useSSL=false
New Connection Sql:
Transaction Isolation:
Use JTA?: true
Use CCM?: true
Min Pool Size:
Initial Pool Size:
Max Pool Size:
Prefill: false
Flush Strategy: Gracefully
Strict Minimum: false
Use Fast Fail: false
Decrementer Class:
Decrementer Properties:
Incrementer Class:
Incrementer Properties:
Username: root
Password: ********
Security Domain:
Allow Multiple Users: false
No properties
Valid Connection Checker: org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker
Check Valid Sql:
Validate On Match: true
Background Validation: true
Background Validation Millis: 500
Stale Connection Checker:
Exception Sorter: org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter
Use Try Lock:
Blocking Timeout Millis:
Idle Timeout Minutes:
Set Transaction Query Timeout: false
Query Timeout:
Allocation Retry:
Allocation Retry Wait Millis:
Track Statements:
Share Prepared Statements: false
Statement Cache Size:
Version Info:
WildFly Full | ||
Product version:
| 10.1.0.Final | |
Profile:
| COMMUNITY | |
HAL version:
| 2.8.27.Final | |
Core version:
| 2.8.27.Final |
Error:
2017-03-02 12:55:13,134 WARN [org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory] (default task-64) IJ030027: Destroying connection that is not valid, due to the following exception: com.mysql.jdbc.JDBC4Connection@39b2c10f: java.sql.SQLException: Ping failed: java.lang.reflect.InvocationTargetException
at org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker.isValidConnection(MySQLValidConnectionChecker.java:93)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.isValidConnection(BaseWrapperManagedConnectionFactory.java:1272)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.getInvalidConnections(BaseWrapperManagedConnectionFactory.java:1085)
at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:378)
at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:617)
at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:589)
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:590)
at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:429)
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:747)
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)
at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:66)
at org.eclipse.persistence.sessions.JNDIConnector.connect(JNDIConnector.java:135)
at org.eclipse.persistence.sessions.DatasourceLogin.connectToDatasource(DatasourceLogin.java:162)
at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.connectInternal(DatasourceAccessor.java:346)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.connectInternal(DatabaseAccessor.java:309)
at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.reconnect(DatasourceAccessor.java:581)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.reconnect(DatabaseAccessor.java:1634)
at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.incrementCallCount(DatasourceAccessor.java:321)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:615)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2056)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:258)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2740)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2693)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:559)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1175)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1134)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:460)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1222)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
at org.eclipse.persistence.internal.jpa.QueryImpl.getSingleResult(QueryImpl.java:521)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:400)
at de.perfectpattern.commons.domain.service.tenencipal.TenencipalService.getTenant(TenencipalService.java:88)
at de.perfectpattern.commons.domain.service.tenencipal.TenencipalService.verifyTenencipal(TenencipalService.java:73)
at de.perfectpattern.commons.domain.service.tenencipal.TenencipalService.verifyAndSetContext(TenencipalService.java:39)
at de.perfectpattern.commons.domain.service.tenencipal.TenencipalService$Proxy$_$$_WeldClientProxy.verifyAndSetContext(Unknown Source)
at de.perfectpattern.commons.webApp.jax_servlet.filter.securityContextFilter.SecurityContextFilter.doFilter(SecurityContextFilter.java:68)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at de.perfectpattern.commons.webApp.jax_servlet.filter.requestTransformerFilter.RequestTransformerFilter.doFilter(RequestTransformerFilter.java:74)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at de.perfectpattern.commons.webApp.jax_servlet.filter.methodOverrideFilter.MethodOverrideFilter.doFilter(MethodOverrideFilter.java:53)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at de.perfectpattern.commons.webApp.jax_servlet.filter.headerOverrideFilter.HeaderOverrideFilter.doFilter(HeaderOverrideFilter.java:55)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at de.perfectpattern.commons.webApp.jax_servlet.filter.wrapFilter.WrapFilter.doFilter(WrapFilter.java:49)
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
at org.wildfly.extension.undertow.security.SecurityContextAssociationHandler.handleRequest(SecurityContextAssociationHandler.java:78)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131)
at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
at io.undertow.security.handlers.NotificationReceiverHandler.handleRequest(NotificationReceiverHandler.java:50)
at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler.handleRequest(JACCContextIdHandler.java:61)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:292)
at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:81)
at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:138)
at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135)
at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:272)
at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:81)
at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:104)
at io.undertow.server.Connectors.executeRootHandler(Connectors.java:202)
at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:805)
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)
2017-03-02 12:55:13,135 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (default task-64) IJ000621: Destroying connection that could not be validated: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@5d4fced8[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@1e05cb3f connection handles=0 lastReturned=1488457252871 lastValidated=1488456689439 lastCheckedOut=1488457252867 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5218204f mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@410f3b78[pool=DS_COM_V1] xaResource=LocalXAResourceImpl@117b2bd9[connectionListener=5d4fced8 connectionManager=305f607b warned=false currentXid=null productName=MySQL productVersion=5.5.45-log jndiName=java:jboss/datasources/DS_COM_V1] txSync=null]
