C3P0 connection pool gives connection timeout error with this configuration

Question

I am using resin server + spring framework and c3p0 connection pooling. I have configured the connection pool with the following properties file. But somehow every 24 hours or so my website faces connection timeout errors and then i have to restart my resin server to make the website live again. Please tell me whats wrong in the following configuration file and what im missing here.

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.databaseURL=jdbc:mysql://localhost/my_database1_url
jdbc.StockDatabaseURL=jdbc:mysql://localhost/my_database2_url
jdbc.username=my_username
jdbc.password=my_password
jdbc.acquireIncrement=10
jdbc.minPoolSize=20
jdbc.maxPoolSize=30
jdbc.maxStockPoolSize=30
jdbc.maxStatements=100
jdbc.numOfHelperThreads=6
jdbc.testConnectionOnCheckout=true
jdbc.testConnectionOnCheckin=true
jdbc.idleConnectionTestPeriod=30
jdbc.prefferedTestQuery=select curdate();
jdbc.maxIdleTime=7200
jdbc.maxIdleTimeExcessConnections=5

Answer 1

So, a bunch of things.

• c3p0 has built-in facilities for observing and debugging for Connection leaks. Please set the configuration parameters unusedConnectionTimeout unreturnedConnectionTimeout and debugUnreturnedConnectionStackTraces. Set an unreturnedConnectionTimeout that defines a period of time after which c3p0 should presume a Connection has leaked, and so close it. Set debugUnreturnedConnectionStackTraces to ask c3p0 to log the stack trace that checked out the Connection that did not get checked in properly. See Configuring to Debug and Workaround Broken Client Applications.
• You are configuring c3p0 in a nonstandard way. That might be fine, or not, but you want to verify that the config that you intend to set is the config c3p0 gets. c3p0 DataSources dump their config at INFO on pool initialization. Please consider checking that to be sure you are getting the config you intend. Alternatively, you can check your DataSource's runtime config via JMX.
• Besides the nonstandard means of configuration, several of your configuration properties seem amiss. prefferedTestQuery should be preferredTestQuery. numOfHelperThreads should be numHelperThreads.

• The following are not c3p0 configuration names at all. Perhaps you are internally mapping them to c3p0 configuration, but you'd want to verify this. Here are the not-c3p0-property-names:

  jdbc.driverClassName=com.mysql.jdbc.Driver
  jdbc.databaseURL=jdbc:mysql://localhost/my_database1_url
  jdbc.StockDatabaseURL=jdbc:mysql://localhost/my_database2_url
  jdbc.username=my_username
  jdbc.maxStockPoolSize=30
  

  In a standard c3p0.properties form, what you probably mean is

  c3p0.driverClass=com.mysql.jdbc.Driver
  c3p0.jdbcURL=jdbc:mysql://localhost/my_database1_url
  # no equivalent -- jdbc.StockDatabaseURL=jdbc:mysql://localhost/my_database2_url
  c3p0.user=my_username
  # no equivalent -- jdbc.maxStockPoolSize=30
  

  Please see Configuration Properties. Again, c3p0 knows nothing about jdbc.-prefixed properties, but perhaps something in your own libraries or middleware picks those up.

Note: I love to see @NiSay's way of checking for Connection leaks, because I love to see people using more advanced c3p0 API. It will work, as long as you don't hot-update your DataSource's config. But you don't need to go to that much trouble, and there's no guarantee this approach will continue to work in future versions c3p0 makes no promises about ConnectionCustomizer lifecycles. ConnectionCustomizers are intended to be stateless. It is easier and safer to use c3p0's built-in leak check facility, described in the first bullet-point above.