I am having a problem which I believe is related to the wait_timeout (8 hour) setting.

After 8 hours of inactivity I will get errors, typically with code "SQL Error: 0, SQLState: 08S01"
and a stack trace saying hibernate has lost connection to MySQL.

Seems from the forums that lots of people are having this problem.

In spite of having implemented many of the suggestions, I am still having the problem.

The problem is only occuring on my Hibernate connections.

All the Hibernate files, including the hibernate.cfg.xml were generated by the hibernate wizard in MyEclipse.

Here are the relevant settings I currently have in the <session-factory> in hibernate.cfg.xml:


I also noticed in the MySQL doc that it suggests making wait_timeout on the MySQL server longer.

I tried adding a wait_timeout property to the my.ini file (in various places) and restarting MySQL service, but when I do a SHOW VARIABLES wait_timeout still has the default 8 hour value.

How can I change wait_timeout on the Community edition?

What connection pool or data source are you using? there is usually a setting that enables testing a connection usually by a dummy sql command before checking out from connection pool and that needs to be activated to get around this problem.




Farzad-

The settings in romcat-context-5.5.xml:

<Resource
name="jdbc/ccsprod"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
maxActive="100"
maxIdle="30"
maxWait="10000"
username="@DB-USERNAME@"
password="@DB-PASSWORD@"
driverClassName="@DB-DRIVERNAME@"
defaultAutoCommit="true"
url="@DB-URL@"
validationQuery="SELECT 1"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
/>

Umm, then what is the configuration in the hibernate file?



Farzad-

I don't understand the question.
Which "hibernate file"?

The relevant parameters from hibernate-cfg.xml are above.

The <Resource ....> is a tomcat data source, so I assume in hibernate config file (the first post) you should not have any connection setting because all hibernate needs to do is to get a connection from the tomcat's data source.


Farzad-

More detail on hibernate-cfg.xml:



I'm no expert, but it doesn't look like this is referencing the tomcat definition???

I tried adding

<property name="validationQuery">SELECT 1</property>

and it seems happy to take it.
Would that help?

Nah, set this hibernate.connection.datasource to the data source name in the tomcat file instead of all other connection properties. If this is a web application then you also need to put the data source resource ref in the web.xml file too. See Resource Definitions in http://tomcat.apache.org/tomcat-5.5-doc/config/context.html



Farzad-

Thanks Farzad, I'll look at that.

By the way, do you know how to set the MySQL wait_timeout? Right now it keeps defaulting to 8 hours which makes testing a very slow process. If I could set it to 1 minute, I could test my solutions more quickly.

As I mentioned above, I tried setting wait_timeout in my.ini and that didn't work.

I don't know. I have a feeling I could set the value in the admin console but that was quite a while ago and I might have mixed it up with something else.



Farzad=

Right again, Farzad.
To set the MySQL wait_timeout in the Administrator go to Startup Variables | Advanced Networking, select wait_timeout and specify the value.

When I used SHOW VARIABLES from a command line it still showed the old value, but several minutes after my initial app query I tried another and MySQL threw a connection error.

To use the tomcat datasource I did the following:

Changed all the properties in hibernate.cfg.xml <session-factory> to



Note the java:comp/env/ on the front of the name.

I also added the following to web.xml:



Now to see if it still throws MySQL connection errors.

Success!!!

You are a miracle worker Farzad.

The application has stayed up now for a couple of days.

Thanks so much for your help.

Please could you print how and what are you change in your code for good work?????? Please!