I am experiencing the dreaded CommunicationsException that occurs when an application sits around idle for 8 hours and MySql cuts it off. I've read many many posts on this subject and have found infinite variety in the way that people configure hibernate to talk to MySql. I've become rather confused as to which properties are for what and what the exact syntax of them is and how to tell if they're actually being used or not. I think my setup is fairly simple. I'm running the Sun Application Server 9.1 and using Hibernate to connect to MySql. I'd like to come up with a good configuration for running in a production environment under reasonable load, and that of course avoids the exception below.


** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: The last packet successfully received from the server was62645 seconds ago.The last packet sent successfully to the server was 62645 seconds ago, which is longer than the server configured value of wait_timeout. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property autoReconnect=true to avoid this problem.

I am configuring Hibernate via the APIs rather than via hibernate.cfg.xml because I want to programmatically change the url, username and password so I can connect to multiple databases. The code I am using is below. Could anyone tell me if this looks correct? If I specfy parameters like "hibernate.dbcp.*" does that mean Hibernate automatically starts using DBCP? One person I saw was using "dbcp.ps.*". I'm really not sure what I'm doing here. Any help is much appreciated.

String url = MessageFormat.format("jdbc:mysql://{0}/{1}", hostName, databaseName);

Configuration cfg = new Configuration()
.setProperty("hibernate.connection.url", url)
.setProperty("hibernate.connection.username", userName)
.setProperty("hibernate.connection.password", password)
.setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver")
.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLDialect")
.setProperty("hibernate.current_session_context_class", "thread")
.setProperty("hibernate.cache.provider_class", "org.hibernate.cache.NoCacheProvider")
.setProperty("hibernate.transaction.factory_class", "org.hibernate.transaction.JDBCTransactionFactory")
.setProperty("hibernate.show_sql", "true")
.setProperty("hibernate.autoReconnect", "true")
.setProperty("hibernate.dbcp.maxActive", "15")
.setProperty("hibernate.dbcp.maxIdle", "5")
.setProperty("hibernate.dbcp.maxWait", "120000")
.setProperty("hibernate.dbcp.whenExhaustedAction", "1")
.setProperty("hibernate.dbcp.testOnBorrow", "true")
.setProperty("hibernate.dbcp.testOnReturn", "true")
.setProperty("hibernate.dbcp.validationQuery", "select 1 from dual")
.setProperty("hibernate.dbcp.maxActive", "8")
.setProperty("hibernate.dbcp.maxIdle", "0")
.setProperty("hibernate.dbcp.maxWait", "-1")
.setProperty("hibernate.dbcp.timeBetweenEvictionRunsMillis", "10000")
.setProperty("hibernate.dbcp.minEvictableIdleTimeMillis", "60000")
.setProperty("hibernate.dbcp.whenExhaustedAction", "2");

Hi,

have you tried to switch to another connection pool, for example c3po? I think the support might be better (http://forum.hibernate.org/viewtopic.php?t=933530).

--Hardy