[Datasource Configuration] - JBoss AS 7 connection pooling issues with Mysql
by 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&con...]
11 years, 7 months
[Datasource Configuration] - Re: Oracle XA ds, ojdbc 11.2.0.3.0, jboss-6.0.0.Final, multiple connection properties
by Mateusz User
Mateusz User [https://community.jboss.org/people/urbanq] created the discussion
"Re: Oracle XA ds, ojdbc 11.2.0.3.0, jboss-6.0.0.Final, multiple connection properties"
To view the discussion, visit: https://community.jboss.org/message/763405#763405
--------------------------------------------------------------
I have the same problem in jboss 5.1.0.GA.
Found that XADataSourceDeploymentMetaData is setting 'XADataSourceProperties' property with pattern:
name+ "=" + value+ "\n".
So if you define:
<xa-datasource-property name="ImplicitCachingEnabled">true</xa-datasource-property>
<xa-datasource-property name="MaxStatements">32</xa-datasource-property>
<xa-datasource-property name="ConnectionProperties">defaultRowPrefetch=100\noracle.jdbc.StreamBufferSize=30</xa-datasource-property>
it will set: XADataSourceProperties="ImplicitCachingEnabled=true\nMaxStatements=32\nConnectionProperties=defaultRowPrefetch=100\noracle.jdbc.StreamBufferSize=30"
and setting properties on OracleXADS will be:
xds.setImplicitCachingEnabled(true);
xds.setMaxStatements(32);
xds.setConnectionProperties(java.util.Properties(defaultRowPrefetch=100));
*xds.setOracle.jdbc.StreamBufferSize(30); <- !! no such method exception!*
as we expect:
xds.setImplicitCachingEnabled(true);
xds.setMaxStatements(32);
xds.setConnectionProperties(java.util.Properties(defaultRowPrefetch=100,oracle.jdbc.StreamBufferSize=30));
I didn't found any workaroud for this problem.
OracleXADataSource API is broken and only way is to JBoss will take care of it :)
--------------------------------------------------------------
Reply to this message by going to Community
[https://community.jboss.org/message/763405#763405]
Start a new discussion in Datasource Configuration at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&con...]
11 years, 7 months