3 Replies Latest reply on Dec 5, 2012 5:50 AM by wdfink

JBoss AS 7 connection pooling issues with Mysql

prabhun Oct 5, 2012 9:09 AM

Hi,

We are facing below two issues.

Unable to get managed connections for java:jboss/datasources/testDS
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver ........

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 58,888,102 milliseconds ago. The last packet sent successfully to the server was 58,888,103 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.

We use JBoss EAP 6.0.

Standalone xml configuration is given below

<datasource-class>org.apache.commons.dbcp.BasicDataSource</datasource-class>

<pool>

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

<min-pool-size>2</min-pool-size>

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

</pool>

For the first issue,

from my understanding following may be the root cause

Some connections are not closed properly and there are no more connections available
Max connection of 20 is not enough to handle the load, increasing the max pool size may help

Is there any other reasons?

How to fix this apart from closing all connections if any?

Do we need to add <idle-timeout-minutes> and <blocking-timeout-millis> also?

For the second issue,

We observed this occurs when we leave the application idle for say 8-10 hours and accessing it next day.

From JBoss forum I read we need to add validation settings also.

The default wait_timeout in MySQL is 8 hours. If we specify <idle-timeout-minutes> of say 30 minutes, whether this will override MySQL wait timeout?

And whether this will resolve this issue?

Or do we need to add the validation settings, like background-validation and <background-validation-millis> ?

Thanks,

Prabhu

1. Re: JBoss AS 7 connection pooling issues with Mysql

mandrosen Dec 4, 2012 4:10 PM (in response to prabhun)

Have you figured anything out to fix this? We are having a very similary situation. As soon as the connection pool runs into a situation where all the connections are busy, it waits for the blocking timeout milis and then throws the exception complaining that it can't open a managed connection.
Actions
2. Re: JBoss AS 7 connection pooling issues with Mysql

prabhun Dec 5, 2012 4:50 AM (in response to mandrosen)

Hello Marc,

In our case we found that in one place the connection is not closed. Spring DataSourceUtils.getConnection was called and developers missed to close the connection by calling DataSourceUtils.releaseConnection.
We replaced that call with Spring JDBCTemplate which took care of closing the resources.

We then increased the max pool size, as all the connections are busy due to long running transactions and not released immediately.

In your case whether all connections are taking too much time to get released?
Actions
3. Re: JBoss AS 7 connection pooling issues with Mysql

wdfink Dec 5, 2012 5:50 AM (in response to mandrosen)

You might have a chance to check it. IF you run in such situation you should stop new clients from accessing and see whether the connections are all back to pool if the requests are finished.
Other option is to monitor the in-use and max-in-use counter, if you see it growing under a similar load during the time you will have a connection leak.

You can also add debug=true or error=true to the cached-connection manager of the JCA subsystem. this will track whether an application does not close the connection at the end.
A Exception or a log statement (error/debug) will show you where the connection is used in your code.
Actions

Go to original post