The following exceptions when happened, this means there is something wrong on the environment especially if those exceptions happened only on production environment with big number of users without any problem in testing environment with normal number of users.
1. Could not open connection: org.hibernate.exception.GenericJDBCException: Could not open connection.
2. java.sql.SQLRecoverableException: Closed Connection.
3. java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
My environment information:
windows server 2012, oracle DB, jboss 7.1 application server and java application use hibernate 4 with c3p0 connection pools
I searched a lot about the related topics and I found many useful topics but the most useful topics was those two topics. topic_1 topic_2
I simulate this case when I decrease the configuration maxStatements inside c3p0 configuration file but as the definition for this parameter which defined here , it is the size of c3p0's global PreparedStatement cache.
I have the following questions:
- Why I got error cannot open connection when I decrease maxStatements attributes which should effect only on the performance since this is cache attribute and this error disappears when I decrease it?
- Why I got also cannot open connection when I increase maxStatements value with big number? How can I know the limitation from the database?
- Is there any limitation from the database can limit this attributes and how can I check it?
- I checked the connection pool maximum attributes with the database session parameter and I found that, the database session parameter is greater than the maximum pool size for my application which is right. I checked also the active and idle connections on the database and no problem in those numbers. Is there any other DB attributes should be checked instead of session parameter for database to be sure the database can give me the configured number of connection pools?
My c3p0-config is the following:
<default-config>
<property name="checkoutTimeout">300</property>
<property name="idleConnectionTestPeriod">70</property>
<property name="initialPoolSize">50</property>
<property name="maxIdleTime">270</property>
<property name="maxPoolSize">500</property>
<property name="minPoolSize">50</property>
<property name="maxStatements">400</property>
<property name="maxStatementsPerConnection">0</property>
<property name="testConnectionOnCheckout">true</property>
<property name="testConnectionOnCheckin">true</property>
<property name="preferredTestQuery">SELECT 1 from dual</property>
<property name="acquireRetryAttempts">0</property>
<property name="acquireRetryDelay">1000</property>
<property name="breakAfterAcquireFailure">false</property>
<property name="unreturnedConnectionTimeout">270</property>
<property name="debugUnreturnedConnectionStackTraces">true</property>
</default-config>
