Hi,
I m getting this exception. I have seen various links for this and all possible forums and have tried all but i m still getting this exception.

For testing purposes i have configured parameters as shown below
mysql wait_timeout=100s;
<property name="c3p0.acquire_increment">5</property>
<property name="c3p0.idle_test_period">5</property> <!-- seconds -->
<property name="c3p0.max_size">200</property>
<property name="c3p0.max_statements">100</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">94</property>
<property name="hibernate.transaction.auto_close_session">true</property>

c3p0.testConnectionOnCheckout=true
c3p0.maxConnectionAge=94
c3p0.automaticTestTable=tester


Plz help me solve this prob as i have tried every thing i got on the net

** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2692)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2621)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2150)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:117)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1607)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:121)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:169)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:745)
at com.osiris.moulinette.AutoReconnect.run(AutoReconnect.java:25)


** END NESTED EXCEPTION **



Last packet sent to the server was 1 ms ago.

STACKTRACE:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

Do you always get this exception or not ? Because it seems like you're not able to access your db.

Could you test a simple query doing pure jdbc or even accessing your db from your host using a jdbc client ? (like the DTP plugin for eclipse, for example).

No i am able to access my db thru jdbc.Are my configurations correct. I mean i have tried all permutation and combinations but m not able to get the correct solution.The wait_timeout mentioned is 100s if i access after say 5 mins then the access fails.As far asi can think c3p0 is still using stale connection,which should not be the case acc to the way i have configured c3p0.

OK, so maybe the connections are out of order because timeout. I think I've already read about such problem in the forum, you should check the archive.

Maybe you should try and see how to configure C3P0 to send every n seconds a query in each connection. Obviously, this query must be something the least time consuming possible ("select 1" or something like that).

I don't know exactly what's possible with C3P0, but with DBCP you can use the "testOnBorrow" connection feature : this will send a query in the connection before giving it to you. This way, you prevent your code for crashing when receiving a bad state connection. You see what I mean ?

Hi.

The way that you have c3p0 configured, your application should not see stale Connections, as your Connections are being tested on checkout, and you have set both hibernate.c3p0.timeout [ maxIdleTime ] and c3p0.maxConnectionAge to very short values. (Why precisely 94 seconds?) Are you sure this is a stale Connection issue? Do you see these Exceptions only after the database has been running for several hours?

Can you please post c3p0's config dump, which is logged at INFO level when it's initialized? It's possible that there's a configuration issue, that the configuration you are expecting is not "taking". Please also post c3p0's version and build banner [also logged at INFO on initialization]. Note that maxConnectionAge only works with recent versions of c3p0 [c3p0-0.9.1-pre7+].

[c3p0.testConnectionOnCheckout, c3p0.maxConnectionAge, and c3p0.automaticTestTable are set in a c3p0.properties file, right?]

Thanks!

Steve

Steve,
I m using c3p0-9.1 11th version .I m posting the dump below
plz help me out and thanks in advance

[ acquireIncrement -> 5,
acquireRetryAttempts -> 30,
acquireRetryDelay -> 1000,
autoCommitOnClose -> false,
automaticTestTable -> tester,
breakAfterAcquireFailure -> false,
checkoutTimeout -> 0,
connectionCustomizerClassName -> null,
connectionTesterClassName -> null,
debugUnreturnedConnectionStackTraces -> false,
factoryClassLocation -> null,
forceIgnoreUnresolvedTransactions -> false,
identityToken ->
2w3nme7j31an2zga5uw|1db4492,
idleConnectionTestPeriod -> 5,
initialPoolSize -> 10,
maxAdministrativeTaskTime -> 0,
maxConnectionAge -> 94,
maxIdleTime -> 94,
maxIdleTimeExcessConnections -> 0,
maxPoolSize -> 200,
maxStatements -> 100,
maxStatementsPerConnection -> 0,
minPoolSize -> 10,
nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@57d3774a [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 2w3nme7j31an2zga5uw|67fe80, jdbcUrl -> jdbc:mysql://localhost:3306/dms, properties -> {user=******, password=******} ], preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> 2w3nme7j31an2zga5uw|1e6f0ef, numHelperThreads -> 3 ]



here tester is the table which c3p0 fires a query to check if connection is stale or not.It is firing the query bit i think the pool still consists of stale object.it is not releasing objects i guess.

i also tried this (http://dev.mysql.com/doc/refman/5.0/en/ ... 3-1-8.html)
connectionTesterClassName=com.mysql.jdbc.integration.c3p0.MysqlConne
ctionTester

this also failed.

Akshay,

So your config looks good for preventing stale Connections.

But I just noticed something. The stack trace you posted does not go through any c3p0 proxies -- it is using a MySql Connection directly. I'm guessing that you've got c3p0 configured enough to be initialized (obviously you are seeing it start up), but there as at least some code path that is using the MySql driver directly, without c3p0.

You might monitor c3p0 by using DEBUG logging for com.mchange.*, or via JMX, to get some sense of whether it's being used at all. But hibernate would have to work very hard to call a com.mysql.jdbc.PreparedStatement.executeQuery() without first calling a method on a c3p0 proxy PreparedStatement, if the statement came from a c3p0-wrapped Connections. Somehow, sometimes or all the time, you are using unpooled Connections, and these are sometimes failing.

Good luck!

Steve

Steve,
Is it not the duty of hibernate to check that its using c3p0.I mean i went thru my code and there is no code where i have directly used a mysql connection.


I added the following lines to check for c3p0 logs.I m not sure if u meant this or not.Plz correct me if i am wrong.

log4j.logger.com.mchange.*=DEBUG, c3p0_LOG

log4j.appender.c3p0_LOG=org.apache.log4j.RollingFileAppender

log4j.appender.c3p0_LOG.File=../webapps/dms/log/c3p0.log

log4j.appender.c3p0_LOG.MaxFileSize=5000KB

log4j.appender.c3p0_LOG.MaxBackupIndex=10

log4j.appender.c3p0_LOG.layout=org.apache.log4j.PatternLayout

log4j.appender.c3p0_LOG.layout.ConversionPattern=%d %5p %c{1}:%M:%L - %m%n


The file
c3p0_LOG was empty thru out

Akshay,

I don't know whose duty is what, but we all do the best with what we've got. Somehow, either your app is using a direct, non-c3p0-issued JDBC Connection, or something very strange is going on. I don't why this might be happening, but if you want to understand why your c3p0 configuration isn't preventing you from seeing stale Connections, you'll have to understand why your application is sometimes bypassing c3p0.

You might try setting the connection provider explicitly in your hibernate config, rather than relying on hibernate's automagically using c3p0 if certain c3p0 parameters are set.

Debugging log4j configuration is probably beyond my competence. A quick glance has me wondering about "log4j.logger.com.mchange.*". I think log4j loggers inherit from subcategories automatically, so "log4j.logger.com.mchange" would be sufficient. And the "*" might be interpreted literally, breaking stuff. (In my previous comment, I just used com.mchange.* to imply you want debug logs for all the com.mchange stuff...) You might also verify that c3p0 is logging to log4j. Look for a line (logged at INFO to whatever logging library c3p0 is using) about "MLog" clients, and make sure they are logging to log4j. c3p0 is logging stuff. The config dump you reproduced above was c3p0 logging at INFO.

You can also write a c3p0 ConnectionCustomizer if you want to log for yourself when Connections are being acquired, checked out, checked in, or destroyed via c3p0. See c3p0's docs for more.

I can tell you're frustrated, that stuff that should "just work" isn't working for you. These kinds of issues are annoying. I'm glad to talk you through any c3p0 configuration problems... but for configuration issues at the log4j or hibernate level, you'll have to find others to chime in...

Good luck!

Steve

p.s. you might try posting your full hibernate config.

Hi Steve,
Thanks a lot for helping me out.Actually there was a problem with my code.At same places i was not closing hibernate session so the coonection pooled object was getting checkout but not checked in

Thanks a lot
Akshay

Obviously, the right thing seems to be to close the connections. But, you can configure Hibernate so as it would release connections after transaction.

Just add this property in your hibernate.cfg.xml file :
(default value is on_close).

Hi batmat,
i added <property name="connection.release_mode">after_transaction</property> in the hibernate.cfg.xml file and ran the following code

Session session=sessionfactory.openSession();
session.beginTransaction();
SmHostBean sm=new SmHostBean();
sm.setIpaddress("new sm");
sm.setPort("new sm port");
session.save(sm);
session.getTransaction().commit();

After the transaction is committed the session is not closed automatically inspite of the adding the mentioned propery in cfg.xml.
I had read some where that for openSession() ,we need to close the session explicitly which i thought the added property wud do.

Please clarify.

Thanks
Akshay

Akshay,

If you are using a recent prerelease of 0.9.1, you can use c3p0 to help debug unreturned Connections. In c3p0, setting c3p0.unreturnedConnectionTimeout will cause checked-out Connections to be automatically destroyed after the provided number of seconds. If unreturnedConnectionTimeout is set, setting c3p0.debugUnreturnedConnectionStackTraces will show the stack trace of the Connection check-out whose Connection failed to be returned in time. See c3p0's docs for more. (...not the online docs, which are still 0.9.0.x, but the docs that come with your 0.9.1-prerelease distribution. c3p0-0.9.1-pre11 has somewhat improved documentation.)

smiles and good luck,
Steve