Hi,

I know this subject has been discussed time and again on the forum but I still got errors on getting this c3p0 working. It's killing me that I have to spend so much time on a connection pool. Perhaps someone can give me a hint or a definitive and final "this is how you configure c3p0" response.

I thought I licked it with the SELECT 1 statement because when I killed all threads manually the app recovered after an initial exception. However, on our production server this morning I got the familiar connection errors.

My configuration:
mySQL 4.1.10
Hibernate 2.1.8

Stacktrace
Caused by: net.sf.hibernate.exception.GenericJDBCException: Could not execute query
at net.sf.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:90)
at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:79)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1557)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at nl.tfe.ddb.data.hibernate.ProductPriceDAO.getUpdatedPriceList(ProductPriceDAO.java:163)
... 3 more
Caused by: java.sql.SQLException: You can't operate on a closed connection!!!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:68)
at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:198)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:263)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:236)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:67)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:784)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
... 5 more
Caused by: java.lang.NullPointerException
at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:173)
... 15 more

and this one on the console:
ERROR [19 May 06:04:44] - Communication link failure: java.net.SocketException, underlying cause: Software caused connection abort: recv failed

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Software caused connection abort: recv failed

STACKTRACE:

java.net.SocketException: Software caused connection abort: recv failed
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at java.io.BufferedInputStream.fill(BufferedInputStream.java:183)
at java.io.BufferedInputStream.read1(BufferedInputStream.java:222)
at java.io.BufferedInputStream.read(BufferedInputStream.java:277)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1391)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1538)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:75)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at nl.tfe.ddb.data.hibernate.ProductPriceDAO.getUpdatedPriceList(ProductPriceDAO.java:163)
at nl.tfe.ddb.business.application.ProductPriceHelper.updateProductPrice(ProductPriceHelper.java:115)
at nl.tfe.ddb.business.application.ProductPriceHelper.run(ProductPriceHelper.java:138)
at java.lang.Thread.run(Thread.java:536)

Hibernate configuration
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="dialect">net.sf.hibernate.dialect.MySQLDialect</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost/digibalie</property>
<property name="connection.username">root</property>
<property name="connection.password">*****</property>
<property name="show_sql">false</property>
<property name="cache.provider_class">net.sf.hibernate.cache.OSCacheProvider</property>
<property name="cache.use_query_cache">true</property>
<property name="cache.use_minimal_puts">false</property>

<property name="c3p0.idle_test_period">3000</property>
<property name="c3p0.min_size">1</property>
<property name="c3p0.max_size">100</property>
<property name="c3p0.max_statements">100</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">100</property>
....

c3p0.properties
c3p0.acquireRetryDelay=1000
c3p0.acquireRetryAttempts=60
c3p0.breakAfterAcquireFailure=false
c3p0.preferredTestQuery=SELECT 1
c3p0.testConnectionOnCheckin=true

I can see that the c3p0.properties is picked up from the c3p0 connection string in the console at startup.

Kind regards,

Marc

Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@1a4f927 [ con
nectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@182
a0d0 [ acquireIncrement -> 1, acquireRetryAttempts -> 60, acquireRetryDelay -> 1
000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, factoryClassLocation -> null, forceIgnoreUn
resolvedTransactions -> false, idleConnectionTestPeriod -> 3000, initialPoolSize -> 10, maxIdleTime -> 100, maxPoolSize -> 100, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 10, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@9e2f6 [ description -> null, driverClass -> null, factoryClassLocation -> null, jdbcUrl -> jdbc:mysql://localhost:3306/winschoten_export?autoReconnect=true, properties -> {user=******, password=******} ] , preferredTestQuery -> SELECT 1, propertyCycle -> 300, testConnectionOnCheckin -> true, t
estConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ] ,factoryClassLocation -> null, numHelperThreads -> 3, poolOwnerIdentityToken -> 1
a4f927 ]

Marc

mschipperheyn,

please upgrade to c3p0-0.9.0-pre6. it is near release quality (i just need feedback!), and it fixes an issue whereby following certain kinds of Exceptions, c3p0 would prematurely close a Connection out from underneath the application. I think that's what you are seeing.

Under most circumstances this issue comes up infrequently, and only affects a single Connection following an unexpected Exception. (It can become more serious when anticipated Exceptions leave a Connection in a state where it cannot pass c3p0's default, slow Connection test.) You can ignore the problem, but I'd prefer you upgrade, and yell at me if anything breaks. More recent c3p0s are drop-in replacements for older versions. Just download from sourceforge and replace the jar file.

Thanks!
Steve (c3p0 guy)

I tried updating the library and failed. This morning again Megabytes full of log entries with connection failures.

I'm getting into serious trouble here. What can I do to make this work (and why is it this hard?). The error below is visible throughout the logfiles. The updatepricelist is a process that periodically does some action and therefore keeps generating this exception.

ERROR [22 May 14:58:26] - database exception
nl.tfe.ddb.exception.DataBaseException: DBException in Updateprice
at nl.tfe.ddb.data.hibernate.ProductPriceDAO.getUpdatedPriceList(ProductPriceDAO.java:166)
at nl.tfe.ddb.business.application.ProductPriceHelper.updateProductPrice(ProductPriceHelper.java:115)
at nl.tfe.ddb.business.application.ProductPriceHelper.run(ProductPriceHelper.java:138)
at java.lang.Thread.run(Thread.java:536)
Caused by: net.sf.hibernate.exception.GenericJDBCException: Could not execute query
at net.sf.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:90)
at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:79)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1557)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at nl.tfe.ddb.data.hibernate.ProductPriceDAO.getUpdatedPriceList(ProductPriceDAO.java:163)
... 3 more
Caused by: java.sql.SQLException: Connection.close() has already been called. Invalid operation in this state.
at com.mysql.jdbc.Connection.getMutex(Connection.java:2097)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1539)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:414)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
... 5 more
ERROR [22 May 14:58:26] - Database Exception occured
net.sf.hibernate.exception.GenericJDBCException: Could not execute query
at net.sf.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:90)
at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:79)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1557)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at nl.tfe.ddb.data.hibernate.ProductPriceDAO.getUpdatedPriceList(ProductPriceDAO.java:163)
at nl.tfe.ddb.business.application.ProductPriceHelper.updateProductPrice(ProductPriceHelper.java:115)
at nl.tfe.ddb.business.application.ProductPriceHelper.run(ProductPriceHelper.java:138)
at java.lang.Thread.run(Thread.java:536)
Caused by: java.sql.SQLException: Connection.close() has already been called. Invalid operation in this state.
at com.mysql.jdbc.Connection.getMutex(Connection.java:2097)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1539)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:414)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
... 5 more


Kind regards,

Marc

Turns out a background thread was running outside the servlet scope which didn't benefit from the central closeSession statement.....
:-(

Marc