c3p0.properties

#c3p0.acquireIncrement
#Default: 3
#Determines how many connections at a time c3p0 will try to acquire
#when the pool is exhausted. [See "Basic Pool Configuration"]

c3p0.acquireRetryAttempts=3
#Default: 30
#Defines how many times c3p0 will try to acquire a new Connection
#from the database before giving up. If this value is less than or
#equal to zero, c3p0 will keep trying to fetch a Connection
#indefinitely. [See "Configuring Recovery From Database Outages"]

#c3p0.acquireRetryDelay
#Default: 1000
#Milliseconds, time c3p0 will wait between acquire attempts.
#[See "Configuring Recovery From Database Outages"]

#c3p0.autoCommitOnClose
#Default: false
#The JDBC spec is unforgivably silent on what should happen to
#unresolved, pending transactions on Connection close. C3P0's
#default policy is to rollback any uncommitted, pending work.
#(I think this is absolutely, undeniably the right policy, but
#there is no consensus among JDBC driver vendors.) Setting
#autoCommitOnClose to true causes uncommitted pending work to be
#committed, rather than rolled back on Connection close. [Note:
#Since the spec is absurdly unclear on this question, application
#authors who wish to avoid bugs and inconsistent behavior should
#ensure that all transactions are explicitly either committed or
#rolled-back before close is called.] [See "Configuring Unresolved
#Transaction Handling"]

#c3p0.automaticTestTable
#Default: null
#If provided, c3p0 will create an empty table of the specified name,
#and use queries against that table to test the Connection. If
#automaticTestTable is provided, c3p0 will generate its own test
#query, therefore any preferredTestQuery set will be ignored. You
#should not work with the named table after c3p0 creates it; it
#should be strictly for c3p0's use in testing your Connection. (If
#you define your own ConnectionTester, it must implement the
#QueryConnectionTester interface for this parameter to be useful.)
#[See "Configuring Connection Testing"]

#c3p0.breakAfterAcquireFailure
#Default: false
#If true, a pooled DataSource will declare itself broken and be
#permanently closed if a Connection cannot be obtained from the
#database after making acquireRetryAttempts to acquire one. If
#false, failure to obtain a Connection will cause all Threads
#waiting for the pool to acquire a Connection to throw an Exception,
#but the DataSource will remain valid, and will attempt to acquire
#again following a call to getConnection(). [See "Configuring
#Recovery From Database Outages"]

#c3p0.checkoutTimeout
#Default: 0
#The number of milliseconds a client calling getConnection() will
#wait for a Connection to be checked-in or acquired when the pool
#is exhausted. Zero means wait indefinitely. Setting any positive
#value will cause the getConnection() call to time-out and break
#with an SQLException after the specified number of milliseconds.

#c3p0.connectionTesterClassName
#Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester
#The fully qualified class-name of an implememtation of the
#ConnectionTester interface, or QueryConnectionTester if you would
#like instances to have access to a user-configured preferredTestQuery.
#This can be used to customize how c3p0 DataSources test Connections,
#but with the introduction of automaticTestTable and preferredTestQuery
#configuration parameters, "rolling your own" should be overkill for
#most users. [See "Configuring Connection Testing"

#c3p0.factoryClassLocation
#Default: null
#DataSources that will be bound by JNDI and use that API's
#Referenceable interface to store themselves may specify a URL
#from which the class capable of dereferencing a them may be
#loaded. If (as is usually the case) the c3p0 libraries will
#be locally available to the JNDI service, leave this set as null.

#c3p0.forceIgnoreUnresolvedTransactions
#Default: false
#Strongly disrecommended. Setting this to true may lead to subtle
#and bizarre bugs. This is a terrible setting, leave it alone
#unless absolutely necessary. It is here to workaround broken
#databases / JDBC drivers that do not properly support transactions,
#but that allow Connections' autoCommit flags to go to false
#regardless. If you are using a database that supports transactions
#"partially" (this is oxymoronic, as the whole point of transactions
#is to perform operations reliably and completely, but nonetheless
#such databases are out there), if you feel comfortable ignoring the
#fact that Connections with autoCommit == false may be in the middle
#of transactions and may hold locks and other resources, you may
#turn off c3p0's wise default behavior, which is to protect itself,
#as well as the usability and consistency of the database, by either
#rolling back (default) or committing (see c3p0.autoCommitOnClose above)
#unresolved transactions. This should only be set to true when you
#are sure you are using a database that allows Connections'
#autoCommit flag to go to false, but offers no other meaningful
#support of transactions. Otherwise setting this to true is just
#a bad idea. [See "Configuring Unresolved Transaction Handling"]

#c3p0.idleConnectionTestPeriod=
#Default: 0
#If this is a number greater than 0, c3p0 will test all idle, pooled
#but unchecked-out connections, every this number of seconds.
#[See "Configuring Connection Testing"]

c3p0.initialPoolSize=10
#Default: 3
#Number of Connections a pool will try to acquire upon startup.
#Should be between minPoolSize and maxPoolSize. [See "Basic Pool
#Configuration"]

c3p0.maxIdleTime=600
#Default: 0
#Seconds a Connection can remain pooled but unused before being
#discarded. Zero means idle connections never expire. [See "Basic
#Pool Configuration"]

c3p0.maxPoolSize=128
#Default: 15
#Maximum number of Connections a pool will maintain at any given
#time. [See "Basic Pool Configuration"]

#c3p0.maxStatements
#Default: 0
#The size of c3p0's global PreparedStatement cache. If both
#maxStatements and maxStatementsPerConnection are zero, statement
#caching will not be enabled. If maxStatements is zero but
#maxStatementsPerConnection is a non-zero value, statement
#caching will be enabled, but no global limit will be enforced,
#only the per-connection maximum. maxStatements controls the total
#number of Statements cached, for all Connections. If set, it
#should be a fairly large number, as each pooled Connection
#requires its own, distinct flock of cached statements. As a
#guide, consider how many distinct PreparedStatements are used
#frequently in your application, and multiply that number by
#maxPoolSize to arrive at an appropriate value. Though maxStatements
#is the JDBC standard parameter for controlling statement caching,
#users may find c3p0's alternative maxStatementsPerConnection
#more intuitive to use. [See "Configuring Statement Pooling"]

#c3p0.maxStatementsPerConnection
#Default: 0
#The number of PreparedStatements c3p0 will cache for a single
#pooled Connection. If both maxStatements and
#maxStatementsPerConnection are zero, statement caching will
#not be enabled. If maxStatementsPerConnection is zero but
#maxStatements is a non-zero value, statement caching will be
#enabled, and a global limit enforced, but otherwise no limit
#will be set on the number of cached statements for a single
#Connection. If set, maxStatementsPerConnection should be set
#to about the number distinct PreparedStatements that are used
#frequently in your application, plus two or three extra so
#infrequently statements don't force the more common cached
#statements to be culled. Though maxStatements is the JDBC
#standard parameter for controlling statement caching, users
#may find maxStatementsPerConnection more intuitive to use.
#[See "Configuring Statement Pooling"]

c3p0.minPoolSize=5
#Default: 3
#Minimum number of Connections a pool will maintain at any
#given time. [See "Basic Pool Configuration"]

#c3p0.numHelperThreads
#Default: 3
#c3p0 is very asynchronous. Slow JDBC operations are generally
#performed by helper threads that don't hold contended locks.
#Spreading these operations over multiple threads can
#significantly improve performance by allowing multiple
#operations to be performed simultaneously.

#c3p0.preferredTestQuery
#Default: null
#Defines the query that will be executed for all connection
#tests, if the default ConnectionTester (or some other
#implementation of QueryConnectionTester) is being used.
#Defining a preferredTestQuery that will execute quickly
#in your database may dramatically speed up Connection tests.
#(If no preferredTestQuery is set, the default ConnectionTester
#executes a getTables() call on the Connection's
#DatabaseMetaData. Depending on your database, this may
#execute more slowly than a "normal" database query.) NOTE:
#The table against which your preferredTestQuery will be run
#must exist in the database schema prior to your initialization
#of your DataSource. If your application defines its own schema,
#try automaticTestTable instead. [See "Configuring Connection
#Testing"]

#c3p0.propertyCycle
#Default: 300
#Maximum time in seconds before user configuration constraints
#are enforced. c3p0 enforces configuration constraints continually,
#and ignores this parameter. It is included for JDBC 3 completeness.

#c3p0.testConnectionOnCheckin
#Default: false
#If true, an operation will be performed asynchronously at every
#connection checkin to verify that the connection is valid. Use
#in combination with idleConnectionTestPeriod for quite reliable,
#always asynchronous Connection testing. Also, setting an
#automaticTestTable or preferredTestQuery will usually speed
#up all connection tests. [See "Configuring Connection Testing"]

#c3p0.testConnectionOnCheckout
#Default: false
#Use only if necessary. Expensive. If true, an operation will
#be performed at every connection checkout to verify that the
#connection is valid. Better choice: verify connections periodically
#using idleConnectionTestPeriod. Also, setting an automaticTestTable
#or preferredTestQuery will usually speed up all connection tests.
#[See "Configuring Connection Testing"]

#c3p0.usesTraditionalReflectiveProxies
#Default: false
#c3p0 originally used reflective dynamic proxies for implementations
#of Connections and other JDBC interfaces. As of c3p0-0.8.5,
#non-reflective, code-generated implementations are used instead.
#As this was a major change, and the old codebase had been
#extensively used and tested, this parameter was added to allow
#users to revert of they had problems. The new, non-reflexive
#implementation is faster, and has now been widely deployed and
#tested, so it is unlikely that this parameter will be useful.
#Both the old reflective and newer non-reflective codebases are
#being maintained, but support for the older codebase may (or may not)
#be dropped in the future.

hibernate.properties

hibernate.connection.driver_class=org.postgresql.Driver
hibernate.connection.url=jdbc:postgresql://localhost/hib
hibernate.connection.username=aps
hibernate.connection.password=
hibernate.dialect=net.sf.hibernate.dialect.PostgreSQLDialect

# Set the following to true if you want to see the SQL generated by hibernate
# output to standard out
hibernate.show_sql=false

#The c3p0 connection pooling parameters
#hibernate.c3p0.min_size=2
#hibernate.c3p0.max_size=4
#hibernate.c3p0.timeout=300
#hibernate.c3p0.max_statements=8
#hibernate.c3p0.idle_test_period=3000


# The DBCP Connection Pooling parameters
#Maximum number of checked-out database connections
hibernate.dbcp.maxActive=4

#Maximum number of idle database connections for connection pool
hibernate.dbcp.maxIdle=4

#Maximum idle time for connections in connection pool (expressed in ms).
#Set to -1 to turn off
hibernate.dbcp.maxWait=-1

#Action to take in case of an exhausted DBCP connection pool. Set to 0
#to fail, 1 to block until a connection is made available, or 2 to grow)
hibernate.dbcp.whenExhaustedAction=1

#Validate connection when borrowing connection from pool (defaults to true)
hibernate.dbcp.testOnBorrow=true

#Validate connection when returning connection to pool (optional, true,
# or false)
hibernate.dbcp.testOnReturn=false

#Query to execute for connection validation (optional, requires either
#hibernate.dbcp.testOn Borrow or hibernate.dbcp.testOnReturn)
hibernate.dbcp.validationQuery=SELECT 1+1

#Maximum number of checked-out statements
hibernate.dbcp.ps.maxActive=4

#Maximum number of idle statements
hibernate.dbcp.ps.maxIdle=4

#Maximum idle time for statements (in ms)
hibernate.dbcp.ps.maxWait=60000

#Action to take in case of an exhausted statement pool. Set to 0 to fail,
# 1 to block until a statement is made available, or 2 to grow)
hibernate.dbcp.ps.whenExhaustedAction=1

Hibernate and MySQL Connection Timeouts

Several weeks ago, while trying to finish up a portion of my senior project, my team was having some issues with our persistence layer. The issue had to do with the connection time out in MySQL, and the default C3P0 setup that Hibernate uses. As it took MUCH more research than I think it should have to solve the issue, I thought I would write a blog entry about it. Besides, I haven't set out to give anything back in quite a while. Note that while none of this information is new in any way, I have not been able to find it all gathered together in one place.

Complaint:
After my Spring/Hibernate/MySQL soltuion has been running but inactive for more than 8 hours, I get a broken pipe exception.

Root Cause:
MySQL automatically times out, and closes unused connections after 8 hours, and out of the box, Hibernate does not set up C3P0 to appropriately test/refresh its connection pool when connections go stale.

Solution:
It seemed that as we searched the forums for an answer it wasn't immediately obvious what the right answer would be. We noticed that the C3P0 properties had different names, depending on how your project was configured. The hibernate documentation has some suggestions, but it turns out that the names of the properties are not stated correctly. It is also important to note that if properties are not set in the hibernate configuration they will be overridden by hibernate defaults. Here's a snippet from my hibernate.cfg.xml:

<session-configuration>
<property name="hibernate.c3p0.acquire_increment">3</property>
<property name="hibernate.c3p0.idle_test_period">14400</property>
<property name="hibernate.c3p0.timeout">25200</property>
<property name="hibernate.c3p0.max_size">15</property>
<property name="hibernate.c3p0.min_size">3</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.preferredTestQuery">select 1;</property>
</session-configuration>

The important properties to note above are idle_test_period, and timeout. You want to make sure that C3P0 is configured to test for closed connections and time out unused connections at some rate beneath the threshold set on your MySQL server. With these properties in place you should be good to go.

Testing:
Waiting 8 hours to conduct a test like this would be lame, so let's just change the connection timeout for the MySQL server, re-start the MySQL server, and our application to try it out. You can change the timeout time for MySQL by editing your /ect/my.cnf file (linux) or your my.ini file(windows). You would want to add the following line to the file:

wait_timeout=120

Note that the value after the property is in seconds. Once you're done with your testing, you can remove the property and it will default back to 8 hours.

References:

Configuring C3PO

C3P0 Hibernate Configuration

my_sql options()

Set the MySQL Connection Timeout