Best way to debug MySQL connections that are being closed on me after ~39 minutes?

Question

I have hibernate 3.3, c3p0, MySql 5.1, and Spring. The MySQL connections in my service calls are consistently being closed after ~39 minutes. The natural running time of my service call is on the order of ~5 hours.

I've tried changing various c3p0 config, etc, to avoid the 39 minute cap. No luck.

Is there a more direct, systematic way to log or troubleshoot this? i.e. can I find out why the connection is being closed, and by whom, at which layer?

Update: stack trace

24 Oct 2010 02:22:12,262 [WARN] 012e323c-df4b-11df-89ed-97e9a9c1ac19 (Foobar Endpoint : 3) org.hibernate.util.JDBCExceptionReporter: SQL Error: 0, SQLState: 08003
24 Oct 2010 02:22:12,264 [ERROR] 012e323c-df4b-11df-89ed-97e9a9c1ac19 (Foobar Endpoint : 3) org.hibernate.util.JDBCExceptionReporter: No operations allowed after connection closed.
24 Oct 2010 02:22:12,266 [ERROR] 012e323c-df4b-11df-89ed-97e9a9c1ac19 (Foobar Endpoint : 3) org.hibernate.event.def.AbstractFlushingEventListener: Could not synchronize database state with session

Answer 1

Auto reconnect configuration

http://hibernatedb.blogspot.com/2009/05/automatic-reconnect-from-hibernate-to.html

Answer 2

I have hibernate 3.3, c3p0, MySql 5.1, and Spring. The MySQL connections in my service calls are consistently being closed after ~39 minutes. The natural running time of my service call is on the order of ~5 hours.

I'm not sure I understood. Do you have processes that are supposed to run for 5 hours but currently get aborted after ~39mn (or probably 2400 seconds). Can you confirm? What is previously working? Did you change anything?

Meanwhile, here are some ideas:

• start with the database (see B.5.2.11. Communication Errors and Aborted Connections)
  • start the server with the --log-warnings option and check the logs for suspicious messages
  • see if you can reproduce the problem using a MySQL client from the db host
  • if it works, do the same thing from the app server machine
  • it if works, you'll know MySQL is ok
• move at the app server level
  • activate logging (of Hibernate and C3P0) to get a full stack trace and/or more hints about the culprit
  • also please show your C3P0 configuration settings

And don't forget that C3P0's configuration when using Hibernate is very specific and some settings must go in a c3p0.properties file.