3 Replies Latest reply on Mar 12, 2017 5:14 AM by Wolfgang Mayer

    MySQL Connection configuration

    Lukas Lentner Newbie

      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:

       

      Product name:

       

      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]

        • 1. Re: MySQL Connection configuration
          Wolfgang Mayer Master

          Did you verify that the network connection to your MySql server is running smoothly?

          What is your operating system, JDK and MySql version?

          Do you get any messages in the mysqld.log?

          I understand you are using EclipseLink. Did you use the latest eclipselink (eclipselink-2.6.4.jar)?

          You should also verify your mysql connector. The latest version is mysql-connector-java-5.1.41-bin.jar

          • 2. Re: MySQL Connection configuration
            Micheal Delson Nadar Newbie

            May be you are missing autoReconnect=true in url connection.... below is the set of config for mysql

             

            <datasource jta="true" jndi-name="java:jboss/MySqlDSmydb" pool-name="MySqlDSmydb" enabled="true" use-java-context="true" use-ccm="true" statistics-enabled="true">

                                    <connection-url>jdbc:mysql://mydbserverip:3306/mydb?autoReconnect=true&amp;zeroDateTimeBehavior=convertToNull</connection-url>

                                    <driver-class>com.mysql.jdbc.Driver</driver-class>

                                    <driver>mysql</driver>

                                    <pool>

                                        <min-pool-size>20</min-pool-size>

                                        <initial-pool-size>20</initial-pool-size>

                                        <max-pool-size>200</max-pool-size>

                                        <prefill>true</prefill>

                                        <flush-strategy>AllGracefully</flush-strategy>

                                    </pool>

                                    <security>

                                        <user-name>mydbuser</user-name>

                                        <password>mypasswd</password>

                                    </security>

                                    <validation>

                                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>

                                        <background-validation>true</background-validation>

                                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>

                                    </validation>

                                    <timeout>

                                        <blocking-timeout-millis>5000</blocking-timeout-millis>

                                        <idle-timeout-minutes>15</idle-timeout-minutes>

                                    </timeout>

                                </datasource>

            • 3. Re: MySQL Connection configuration
              Wolfgang Mayer Master

              "The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly." ()MySQL :: MySQL Connector/J 5.1 Developer Guide :: 5.1 Driver/Datasource Class Names, URL Syntax and Configuration Proper…