[jboss-user] [Datasource Configuration] - JBoss AS 7 connection pooling issues with Mysql

[Prabhu Natarajan]

Prabhu Natarajan [https://community.jboss.org/people/prabhun] created the discussion

"JBoss AS 7 connection pooling issues with Mysql"

To view the discussion, visit: https://community.jboss.org/message/763486#763486

--------------------------------------------------------------
Hi,

We are facing below two issues.

1. Unable to get managed connections for java:jboss/datasources/testDS
2. 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




For the first issue, 

from my understanding following may be the root cause

1. Some connections are not closed properly and there are no more connections available
2. 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
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/763486#763486]

Start a new discussion in Datasource Configuration at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20121005/3bf8bd00/attachment.html