Application is not Accessible due to stale connection in Connection pool

[karna1824]

hi,  I am using Jboss AS 7.1.1 Final. My application was deployed and running on AWS. Daily morning when i tried to access my application using URL, it was not accessible. I have been facing this problem from so many days. Daily i need to be restart the server to access my application because of the stale connection in connection pool. I have made the min pool size to 0 and tried,it worked but that was not the correct solution, that is just a workaround.  when i checked the log file, i got below statements.

09:59:21,403 ERROR [org.apache.shiro.realm.jdbc.JdbcRealm] (http--0.0.0.0-80-1) There was a SQL error while authenticating user : com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.

  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.7.0_19]

  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) [rt.jar:1.7.0_19]

  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.7.0_19]

  at java.lang.reflect.Constructor.newInstance(Constructor.java:525) [rt.jar:1.7.0_19]

  at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.Util.getInstance(Util.java:382) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1213) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1200) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4203) [mysql-connector-java-5.1.15-bin.jar:]

  at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:677)

  at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:663)

  at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:404)

  at org.apache.shiro.realm.jdbc.JdbcRealm.getPasswordForUser(JdbcRealm.java:280) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.realm.jdbc.JdbcRealm.doGetAuthenticationInfo(JdbcRealm.java:221) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.realm.AuthenticatingRealm.getAuthenticationInfo(AuthenticatingRealm.java:568) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.authc.pam.ModularRealmAuthenticator.doSingleRealmAuthentication(ModularRealmAuthenticator.java:180) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.authc.pam.ModularRealmAuthenticator.doAuthenticate(ModularRealmAuthenticator.java:267) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.authc.AbstractAuthenticator.authenticate(AbstractAuthenticator.java:198) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.mgt.AuthenticatingSecurityManager.authenticate(AuthenticatingSecurityManager.java:106) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.mgt.DefaultSecurityManager.login(DefaultSecurityManager.java:270) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.subject.support.DelegatingSubject.login(DelegatingSubject.java:256) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.filter.authc.AuthenticatingFilter.executeLogin(AuthenticatingFilter.java:53) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.filter.authc.FormAuthenticationFilter.onAccessDenied(FormAuthenticationFilter.java:154) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.filter.AccessControlFilter.onAccessDenied(AccessControlFilter.java:133) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.filter.AccessControlFilter.onPreHandle(AccessControlFilter.java:162) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.filter.PathMatchingFilter.isFilterChainContinued(PathMatchingFilter.java:203) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.filter.PathMatchingFilter.preHandle(PathMatchingFilter.java:178) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:131) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383) [shiro-core-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) [shiro-web-1.2.1.jar:1.2.1]

  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280) [jbossweb-7.0.13.Final.jar:]

  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.13.Final.jar:]

  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [jbossweb-7.0.13.Final.jar:]

  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [jbossweb-7.0.13.Final.jar:]

  at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153) [jboss-as-web-7.1.1.Final.jar:7.1.1.Final]

  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155) [jbossweb-7.0.13.Final.jar:]

  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [jbossweb-7.0.13.Final.jar:]

  at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567) [jbossweb-7.0.13.Final.jar:]

  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [jbossweb-7.0.13.Final.jar:]

  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368) [jbossweb-7.0.13.Final.jar:]

  at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [jbossweb-7.0.13.Final.jar:]

  at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671) [jbossweb-7.0.13.Final.jar:]

  at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930) [jbossweb-7.0.13.Final.jar:]

  at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_19]

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 83,401,204 milliseconds ago.  The last packet sent successfully to the server was 83,401,205 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.7.0_19]

  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) [rt.jar:1.7.0_19]

  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.7.0_19]

  at java.lang.reflect.Constructor.newInstance(Constructor.java:525) [rt.jar:1.7.0_19]

  at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3358) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1970) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119) [mysql-connector-java-5.1.15-bin.jar:]

  at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281) [mysql-connector-java-5.1.15-bin.jar:]

  at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)

  at org.apache.shiro.realm.jdbc.JdbcRealm.getPasswordForUser(JdbcRealm.java:284) [shiro-core-1.2.1.jar:1.2.1]

  ... 38 more

Caused by: java.net.SocketException: Connection timed out

  at java.net.SocketOutputStream.socketWrite0(Native Method) [rt.jar:1.7.0_19]

  at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) [rt.jar:1.7.0_19]

  at java.net.SocketOutputStream.write(SocketOutputStream.java:153) [rt.jar:1.7.0_19]

  at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) [rt.jar:1.7.0_19]

  at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) [rt.jar:1.7.0_19]

  at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3339) [mysql-connector-java-5.1.15-bin.jar:]

  ... 45 more

Can anyone please help me to get rid of this...?

kindly ask me if you need more info to slove this...

Thank you in advance...

[jfclere]

how is the connection pool defined? Any 'autoReconnect=true'?

[karna1824]

<subsystem xmlns="urn:jboss:domain:datasources:1.0">

            <datasources>

                <datasource jta="false" jndi-name="java:jboss/datasources/yba" pool-name="yba" enabled="true" use-java-context="true">

                    <connection-url>jdbc:mysql://myinstance.csnddbzwlmfj.us-east-1.rds.amazonaws.com:3306/yba</connection-url>

                    <driver>m2mmysql</driver>

                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                    <pool>

                        <min-pool-size>5</min-pool-size>

                        <max-pool-size>20</max-pool-size>

                        <prefill>true</prefill>

                        <use-strict-min>true</use-strict-min>

                    </pool>

                    <security>

                        <user-name>myuser</user-name>

                        <password>mydatabase</password>

                    </security>

                      <timeout>         

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

                        <idle-timeout-minutes>5</idle-timeout-minutes>

                    </timeout>

                </datasource>

                <datasource jta="false" jndi-name="java:jboss/datasources/m2m" pool-name="m2m" enabled="true" use-java-context="true">

                    <connection-url>jdbc:mysql://myinstance.csnddbzwlmfj.us-east-1.rds.amazonaws.com:3306/m2m</connection-url>

                    <driver>m2mmysql</driver>

                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                    <pool>

                        <min-pool-size>5</min-pool-size>

                        <max-pool-size>20</max-pool-size>

                        <prefill>true</prefill>

                        <use-strict-min>true</use-strict-min>

                    </pool>

                    <security>

                        <user-name>myuser</user-name>

                        <password>mydatabase</password>

                    </security>

                     <timeout>

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

                        <idle-timeout-minutes>5</idle-timeout-minutes>

                    </timeout>                   

                 </datasource>

                <datasource jta="false" jndi-name="java:jboss/datasources/wib" pool-name="wib" enabled="true" use-java-context="true">

                    <connection-url>jdbc:mysql://myinstance.csnddbzwlmfj.us-east-1.rds.amazonaws.com:3306/wib</connection-url>

                    <driver>m2mmysql</driver>

                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                    <pool>

                        <min-pool-size>5</min-pool-size>

                        <max-pool-size>20</max-pool-size>

                        <prefill>true</prefill>

                        <use-strict-min>true</use-strict-min>

                    </pool>

                    <security>

                        <user-name>myuser</user-name>

                        <password>mydatabase</password>

                    </security>  

                      <timeout>    

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

                        <idle-timeout-minutes>5</idle-timeout-minutes>

                    </timeout>

                </datasource>

                <drivers>

                    <driver name="h2" module="com.h2database.h2">

                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                    </driver>

                    <driver name="m2mmysql" module="m2m.mysql">

                        <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                    </driver>

                </drivers>

            </datasources>

i tried with 'autoReconnect=true' too... But that didn't help me to get through...

[rhanus]

mysql automatically closes connections after 8 hours of inactivity

you should define:

1.) how to validate a connection before is taken out of pool

2.) which driver exceptions are fatal so that the failing connections should be discarded from pool

3.) optionally strategy to clean up the pool in case of an error

<validation>

   <check-valid-connection-sql>/* ping */</check-valid-connection-sql> [1]

     <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySqlExceptionSorter"/> [2]

</validation>

<pool>

     ...

     <flush-strategy>IdleConnections</flush-strategy> [3]

</pool>

[karna1824]

Thank you for ur Reply Radim Hanus....

Actually i tried with

<validation>

     <check-valid-connection-sql>select 1</check-valid-connection-sql>

</validation>

<timeout>

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

      <idle-timeout-minutes>5</idle-timeout-minutes>

</timeout>

Application working fine... but is there any better solution than this?..

Because, my Senior said, the usage of the validation tag is costly thing.... we can't rely on that..

So... is there any better option than this?

[rhanus]

validation isn't costly if you use lighweight ping instead of full query, so use following config option as I suggested above:

   <check-valid-connection-sql>/* ping */</check-valid-connection-sql>

see details in mysql refman, find section:

23.3.15.12: What should you do if you receive error messages similar to the following: "Communications link failure - Last packet sent to the server was X ms ago"?

if you are still in doubt of validation costs you may play around with backround validation setting such as <background-validation> and <background-validation-millis>

[karna1824]

Can you tell me how to configure with <background-validation> and <background-validation-millis> ....?

i didn't find this section...

mysql refman, 23.3.15.12: What should you do if you receive error messages similar to the following: "Communications link failure - Last packet sent to the server was X ms ago"?

[rhanus]

you should look at jboss sources not mysql ones

for instnce jboss datasource schema ${JBOSS_HOME}/docs/schema/jboss-as-datasources_1_0.xsd

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

turns on background validation of connection (a separate thread)

and by using <background-validation-millis>5000</<background-validation-millis> you limit execution of that validation task for given amount of time (5000ms) to save server resources

[karna1824]

Do i need to put them like,

<validation>

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

      <background-validation-millis>5000</background-validation-millis>

</validation>

i tried to keep those 2 tags out of the validation tag, i got this error..

10:46:36,867 ERROR [org.jboss.as.server] JBAS015956: Caught exception during boot: org.jboss.as.controller.persistence.ConfigurationPersistenceException: JBAS014676: Failed to parse configuration

  at org.jboss.as.controller.persistence.XmlConfigurationPersister.load(XmlConfigurationPersister.java:141) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]

  at org.jboss.as.server.ServerService.boot(ServerService.java:266) [jboss-as-server-7.1.1.Final.jar:7.1.1.Final]

  at org.jboss.as.controller.AbstractControllerService$1.run(AbstractControllerService.java:155) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]

  at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_21]

Caused by: javax.xml.stream.XMLStreamException: org.jboss.as.connector.util.ParserException: IJ010061: Unexpected element: background-validation

  at org.jboss.as.connector.subsystems.datasources.DataSourcesExtension$DataSourceSubsystemParser.readElement(DataSourcesExtension.java:784)

  at org.jboss.as.connector.subsystems.datasources.DataSourcesExtension$DataSourceSubsystemParser.readElement(DataSourcesExtension.java:333)

  at org.jboss.staxmapper.XMLMapperImpl.processNested(XMLMapperImpl.java:110) [staxmapper-1.1.0.Final.jar:1.1.0.Final]

  at org.jboss.staxmapper.XMLExtendedStreamReaderImpl.handleAny(XMLExtendedStreamReaderImpl.java:69) [staxmapper-1.1.0.Final.jar:1.1.0.Final]

  at org.jboss.as.server.parsing.StandaloneXml.parseServerProfile(StandaloneXml.java:894) [jboss-as-server-7.1.1.Final.jar:7.1.1.Final]

  at org.jboss.as.server.parsing.StandaloneXml.readServerElement_1_1(StandaloneXml.java:330) [jboss-as-server-7.1.1.Final.jar:7.1.1.Final]

  at org.jboss.as.server.parsing.StandaloneXml.readElement(StandaloneXml.java:127) [jboss-as-server-7.1.1.Final.jar:7.1.1.Final]

  at org.jboss.as.server.parsing.StandaloneXml.readElement(StandaloneXml.java:100) [jboss-as-server-7.1.1.Final.jar:7.1.1.Final]

  at org.jboss.staxmapper.XMLMapperImpl.processNested(XMLMapperImpl.java:110) [staxmapper-1.1.0.Final.jar:1.1.0.Final]

  at org.jboss.staxmapper.XMLMapperImpl.parseDocument(XMLMapperImpl.java:69) [staxmapper-1.1.0.Final.jar:1.1.0.Final]

  at org.jboss.as.controller.persistence.XmlConfigurationPersister.load(XmlConfigurationPersister.java:133) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]

  ... 3 more

Caused by: org.jboss.as.connector.util.ParserException: IJ010061: Unexpected element: background-validation

  at org.jboss.as.connector.subsystems.datasources.DsParser.parseDataSource(DsParser.java:714)

  at org.jboss.as.connector.subsystems.datasources.DsParser.parseDataSources(DsParser.java:194)

  at org.jboss.as.connector.subsystems.datasources.DsParser.parse(DsParser.java:165)

  at org.jboss.as.connector.subsystems.datasources.DataSourcesExtension$DataSourceSubsystemParser.readElement(DataSourcesExtension.java:776)

  ... 13 more

[rhanus]

sure you do because of schema definition

see jboss-as-datasources_1_0.xsd for details

[karna1824]

I tried with the options you suggested,

<validation>

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

      <background-validation-millis>5000</background-validation-millis>

</validation>

But still i'm getting this error and can't able to access my application....

08:14:06,891 INFO  [stdout] (http--0.0.0.0-80-2) Level 1

08:14:06,898 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-80-2) SQL Error: 0, SQLState: 08S01

08:14:06,902 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-80-2) The last packet successfully received from the server was 44,203,054 milliseconds ago.  The last packet sent successfully to the server was 44,203,055 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

[rhanus]

because you didn't define exception-sorter and check-valid-connection-sql config elements

you need somthing like this:

                <datasource ...>

                    <connection-url>...</connection-url>

                    <driver>mysql</driver>

                    <pool>

                        <min-pool-size>...</min-pool-size>

                        <max-pool-size>...</max-pool-size>

                        <flush-strategy>IdleConnections</flush-strategy>

                    </pool>

                    <security>

                        <user-name>...</user-name>

                        <password>...</password>

                    </security>

                    <validation>

                        <check-valid-connection-sql>/* ping */</check-valid-connection-sql>

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

                        <background-validation-millis>5000</background-validation-millis>

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

                    </validation>

                    <timeout>

                        <idle-timeout-minutes>2</idle-timeout-minutes>

                    </timeout>

                </datasource>