I have written a web application that connects to a MySQL database. Everything works fine for the first 8 hours, after which the MySQL connection expires. I have changed the hibernate.properties file several times as suggested in the forum, but to no avail.

I use:

Hibernate 2.1.4

C3P0 0.9.0-pre6

MySQL 4.0.18

Tomcat 5.0.16

hibernate.properties:

hibernate.c3p0.acquire_increment=2
hibernate.c3p0.idle_test_period=300
hibernate.c3p0.max_size=100
hibernate.c3p0.min_size=5
hibernate.c3p0.timeout=3000
hibernate.connection.driver_class=org.gjt.mm.mysql.Driver
hibernate.connection.url=###
hibernate.connection.username=###
hibernate.connection.password=###
hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect
hibernate.show_sql=true

Stack trace:

net.sf.hibernate.JDBCException: Could not execute query
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1546)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1520)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1512)
at com.workpool.mis.HibernateToolkit.getLicenseInformation(HibernateToolkit.java:207)
at org.apache.jsp.index_jsp._jspService(index_jsp.java:58)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:211)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:805)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:696)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: Communication link failure: java.net.SocketException
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1629)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:414)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
... 34 more

Hi. Sorry for the slow response. I'm indisposed lately,

Your settings should eliminate this kind of error due to mysql Connection timeouts; I'm not sure why it's not. Nevertheless, you can probably resolve the issue by...

# hibernate 2 only
hibernate.c3p0.validate=true

WITH a c3p0.properties file...

# a good mysql test query
c3p0.preferredTestQuery=SELECT 1

In hibernate 3 you'd do without hibernate.c3p0.validate, and define a c3p0.properties file like this:

# a good mysql test query
c3p0.preferredTestQuery=SELECT 1
c3p0.testConnectionOnCheckout=true

In either case, you want to make sure that both of these parameters (preferredTestQuery, and validate & testConnectionOnCheckout, which do the same thing), because c3p0's default, schema & database independent Connection test is slow. If you set a fast preferredTestQuery, testConnectionOnCheckout will probably be okay for you, and is the most certain way to catch expired or otherwise dead Connections.

Hope this helps!

Steve (c3p0 guy)

Do you have 'autoReconnect=true' in your connection URL:

I am having the same MySQL connection timeout problem that many others are having and all the suggestions in this thead (and others) do not have any affect.

Description of problem: I cannot get my Hibernate application to suvive a idle period greater than the MySQL wait_timeout

I am using:
* MySQL 4.1 on Linux Fedora Core 4 (mysql-server-4.1.15-1.FC4.1).
* Hibernate verion: 3.0.5.
* MySQL connector (JDBC driver): 3.1.12
* JVM: Sun JDK 1.5.0_05 for Linux/i386.

I've tried appending "?autoReconnect=true" to the db url but that seems to make no difference. I've tried with/without c3p0. I've tried the various c3p0 tweeks suggested
(eg c3p0.preferredTestQuery=SELECT 1
c3p0.testConnectionOnCheckout=true
) but it makes no difference.



How to replicate:

1. Set mysql wait_timeout to say 30 seconds by adding to the [mysql] section of
/etc/my.cnf:
wait_timeout=30
Restart mysql and verify new wait_timeout is in effect with command "mysqladmin variables"

2. Run a simple loop that gets a java.sql.Connection object from the Hibernate session and try to execute a simple query on that connection. Sleep for X seconds and repeat.

If X < wait_timeout then loop continues to work. If X > wait_timeout the test app fails on the second iteration.



This is my hibernate.properties:

hibernate.connection.driver_class = com.mysql.jdbc.Driver
hibernate.connection.url = jdbc:mysql://localhost/testdb?autoReconnect=true
hibernate.connection.username = root
hibernate.connection.password =
hibernate.dialect = org.hibernate.dialect.MySQLDialect



Here is the error message:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2351)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:824)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:34)
at MySQLTimeoutTest.main(MySQLTimeoutTest.java:35)

** END NESTED EXCEPTION **

Last packet sent to the server was 98 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2563)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:824)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:34)
at MySQLTimeoutTest.main(MySQLTimeoutTest.java:35)
CONNECTION ERROR OCCURRED!

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

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2351)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:824)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:34)
at MySQLTimeoutTest.main(MySQLTimeoutTest.java:35)

** END NESTED EXCEPTION **




The test code:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.hibernate.Session;

public class MySQLTimeoutTest {

private static final int LOOP_DELAY = 25;
private static final String testSql = "SELECT 333";

public static void main (String[] arg) {

long loopDelay = LOOP_DELAY;
if (arg.length > 0) {
try {
loopDelay = Long.parseLong(arg[0]);
} catch (NumberFormatException e) {
System.err.println ("invalid loop delay: " +e);
}
}

int i = 0;
while (true) {
System.err.print ("Iteration " + (i++) + ": ");
Session session = HibernateUtil.currentSession();
Connection connection = session.connection();
System.err.println ("connection=" + connection);
try {
ResultSet rs = connection.createStatement().executeQuery(testSql);
if (!rs.next()) {
System.err.println ("error: nothing returned");
break;
}
int r = rs.getInt(1);
if (r != 333) {
System.err.println ("error: unexpected return value");
break;
}
} catch (SQLException e) {
System.err.println ("error: " + e);
break;
}

System.err.println (" sleeping for " + loopDelay);
try {
Thread.sleep(loopDelay * 1000);
} catch (InterruptedException e) {
// ignore
}
}
}
}

(HibernatUtil is the same class as the Hibernate doc tutorials)

This is wrong test, "c3p0.testConnectionOnCheckout" parameter forces pool to test connection on checkout, you cache connection object in local and in thred local varibales .

Hi!

I thought we should not really use c3p0.testConnectionOnCheckout unless really needed as it is quite expensive, but use the idleConnectionTestPeriod with preferredTestQuery and even testConnectionOnCheckin...

We have just moved to hibernate 3, and we are seeing the number of total and busy connections growing really fast in a couple of minutes going to the max number of connections and then busy connections just drop but total number of connections keeping to the maximum even with most connections being idle.
Any explanation?

These are our settings:
acquireIncrement -> 5,
acquireRetryAttempts -> 30,
acquireRetryDelay -> 1000,
autoCommitOnClose -> false,
automaticTestTable -> null,
breakAfterAcquireFailure -> false,
checkoutTimeout -> 0,
connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester,
description -> null,
driverClass -> org.gjt.mm.mysql.Driver,
factoryClassLocation -> null,
forceIgnoreUnresolvedTransactions -> false,
identityToken -> 7f957f95,
idleConnectionTestPeriod -> 600,
initialPoolSize -> 3,
jdbcUrl -> jdbc:mysql://10.147.163.102/cfsite,
loginTimeout -> 0,
maxIdleTime -> 3000,
maxPoolSize -> 600,
maxStatements -> 0,
maxStatementsPerConnection -> 0,
minPoolSize -> 30,
numHelperThreads -> 3,
preferredTestQuery -> SELECT 1,
properties -> {user=******, password=******},
propertyCycle -> 300,
testConnectionOnCheckin -> true,
testConnectionOnCheckout -> false,
usesTraditionalReflectiveProxies -> false

Thanks a lot,

Monica

It is a good idea to test mySQL without pool, mySQL connection startup is very fast (if you do not open connections in loop).

Uhmm, a bit surprised to hear that.
I'm not sure it'd apply to us though, as we open of the order of 175.000 connections in a 24hour period...
If I dont use a commercial pool, I'd be implementing my own, which seems a waste of time.
I see your point of removing unnecessary complexity though...

Thanks a lot for answering,

Monica

Try to cache "currentConnection" or "correntSession" in threadlocal, I found it works great for PostgreSQL, mySQL connection is "faster" and it must work better (probably pool with validation queries produces more overhead than mySQL connection startup). Pool is usefull as workaround for license or for very slow connections like LDAP authentication stuff.

Our application is web-based and at the moment we have a DB connection per thread (request), in a threadlocal as you mention.

If we wanted to really go that way we probably would have to implement our own connection pool at the servlet level, which could work but I'm not sure it is the best solution for our application.

I still would like to see hibernate 3 working fine with c3p0. Specially as the move from hibernate 2 to hibernate 3 has shown this problem, so something must have changed that we haven't adapted properly to it, though we've gone through the release notes .

Thank you very much for your help,

monica

Hello,

Have you resolved this issue ?
We also moved from hibernate2 to hibernate3 and are facing this same problem, althought we work with dbcp
instead of c3p0.
I even made the following test:
Using hibernate 2.1 with the same JDBC and MySQL version I am able to connect from my local computer to MySQL server on a remote server across an Internet conection, it works like just fine.
But when I test with hibernate-3.2.0 (and new jars) with the same JDBC and MySQL version I got the same error.


Any advice?
regards,

Dears,

I face the same situation here on 2008. :(

I got
javax.servlet.ServletException: org.hibernate.TransactionException: JDBC commit failed

after some idle period say 60 seconds (As i set MySQL wait_timeout=60 under /my.ini)

I use Hibernate 3, MySQL 4 and my application is web based application.

I tried all mentioned solutions on this thread and on other forums, with no affect.

I got the same exception every time.

I hope you have a solution for this repeated situation.

Thanks for help.

I had the same Problem. I thought i was using c3p0 but i wasn't. It is very important to have this line in your hiberante.cfg.xml:



If this results in a ClassNotFoundException or something similar you need the hibernate-c3p0-3.3.2.GA.jar on your classpath.

Otherwise all c3p0-settings are useless. I think most users won't do such a newbie mistake, but i did it, and maybe it helps someone who looks for an answer, like me.

Greetings, Hauke.

p.s. my hibernate.cfg.xml if someone is interested. Note that my connection-settings like connection.url are in a separate hibernate.properties, but that shouldn't make any difference.

By setting your timeout to 10 aren't you countering many of the benefits of your connectionpool? With a timeout that low, you will be discarding existing connections very frequently. You can probably have the timeout much larger if you have idle_test_period set so low.

I had a lot of problems with the same error as in this thread:



To debug this problem, I added "wait_timeout=10" to the "[mysqld]" section of the MySQL .cnf file (in my case, ~/.my.cnf). This allowed me to reproduce the issue every 10 seconds instead of overnight.

At first, I was using the build-in connection pool. I found out that if I set the pool_size to 0 (turned of the built-in connection pooling), this error went away.



However, then I wasn't using connection pooling! So I set up c3p0 by adding hibernate-c3p0 to my pom.xml and adding some properties to hibernate's configuration.





With an idle_test_period of 1 I did not have the problem anymore because the connections were tested very frequently. I also tried setting the idle_test_period back to 0 and setting the timeout to 1. This also prevented the error. I concluded that when I reset MySQL's wait_timeout to a reasonable number, have the hibernate.c3p0.timeout set a bit lower than that, the issue should go away.

Hope this helps anyone!

hello,
I am having this exception I followed different solution found in threads but it aint happening for me :(
I am using spring + hibernate


this is how my xml-config look like:


and my cp30.properties :


In Java in my DAO class :



now when my code is called after a while like 600 sec i guess it gives me this exception :


Its been 5 days and my head is stucked here ...
I tried many many manyyyyy ... I dont know where I am doing it wrong.. :(