JDBCConnectionException: Cannot open connection | Properties OK (?)

[dit]

Hi
DB: MYSQL
My Hibernate and C3P0 Settings are:

try {
            Configuration cfg = new Configuration()
                    .setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver")
                    .setProperty("hibernate.dialect", "de.jawb.avtostop.db.CustomMysqlDialect")
                    .setProperty("hibernate.connection.url", "jdbc:mysql://localhost/poputki")
                    .setProperty("hibernate.connection.username", "localeaccess")
                    .setProperty("hibernate.connection.password", "N4Y7sLenUp8wW7JC")
                    .setProperty("hibernate.connection.charSet", "UTF-8")
                    .setProperty("hibernate.connection.useUnicode", "true")
                    .setProperty("hibernate.connection.characterEncoding", "UTF-8")
                    .setProperty("hibernate.connection.autoReconnect", "true") 
                    .setProperty("hibernate.connection.autoReconnectForPools", "true") 
//                    .setProperty("hibernate.hbm2ddl.auto", "create") //update, create 
                    .setProperty("hibernate.show_sql", "false")
                    
                    //c3po
                    .setProperty("hibernate.connection.provider_class", "org.hibernate.connection.C3P0ConnectionProvider")
                    .setProperty("c3p0.min_size", "5")
                    .setProperty("c3p0.max_size", "20")
                    .setProperty("c3p0.timeout", "1800")
                    .setProperty("c3p0.max_statements", "50")
                    
                    
                  //...beans
            
            sessionFactory = cfg.buildSessionFactory();
        } catch (Throwable ex) {
            throw new ExceptionInInitializerError(ex);
        }

got Exception:

Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handle JspException(JspServletWrapper.java:521)
org.apache.jasper.servlet.JspServletWrapper.servic e(JspServletWrapper.java:430)
org.apache.jasper.servlet.JspServlet.serviceJspFil e(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspSe rvlet.java:260)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
org.springframework.web.servlet.view.InternalResou rceView.renderMergedOutputModel(InternalResourceVi ew.java:238)
org.springframework.web.servlet.view.AbstractView. render(AbstractView.java:250)
org.springframework.web.servlet.DispatcherServlet. render(DispatcherServlet.java:1047)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:817)
org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:719)
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:669)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:574)
javax.servlet.http.HttpServlet.service(HttpServlet .java:617)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
de.jawb.avtostop.controller.filter.CharsetFilter.d oFilter(CharsetFilter.java:41)

root cause

org.hibernate.exception.JDBCConnectionException: Cannot open connection
org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:99)
org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:66)
org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:52)
org.hibernate.jdbc.ConnectionManager.openConnectio n(ConnectionManager.java:449)
org.hibernate.jdbc.ConnectionManager.getConnection (ConnectionManager.java:167)
org.hibernate.jdbc.JDBCContext.connection(JDBCCont ext.java:160)...
org.springframework.web.servlet.view.InternalResou rceView.renderMergedOutputModel(InternalResourceVi ew.java:238)
org.springframework.web.servlet.view.AbstractView. render(AbstractView.java:250)
org.springframework.web.servlet.DispatcherServlet. render(DispatcherServlet.java:1047)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:817)...

root cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsExce ption: The last packet successfully received from the server was 44,190,616 milliseconds ago. The last packet sent successfully to the server was 44,190,616 milliseconds ago. 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.
sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInsta nce(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(Unknown Source)
java.lang.reflect.Constructor.newInstance(Unknown Source)
com.mysql.jdbc.Util.handleNewInstance(Util.java:41 1)
com.mysql.jdbc.SQLError.createCommunicationsExcept ion(SQLError.java:1116)
com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3364)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:19 83)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :2163)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2618)
com.mysql.jdbc.ConnectionImpl.setAutoCommit(Connec tionImpl.java:5000)
com.mchange.v2.c3p0.impl.NewProxyConnection.setAut oCommit(NewProxyConnection.java:881)
org.hibernate.connection.C3P0ConnectionProvider.ge tConnection(C3P0ConnectionProvider.java:83)
org.hibernate.jdbc.ConnectionManager.openConnectio n(ConnectionManager.java:446)
org.hibernate.jdbc.ConnectionManager.getConnection (ConnectionManager.java:167)
org.hibernate.jdbc.JDBCContext.connection(JDBCCont ext.java:160)
org.hibernate.transaction.JDBCTransaction.begin(JD BCTransaction.java:81)...
org.springframework.web.servlet.view.InternalResou rceView.renderMergedOutputModel(InternalResourceVi ew.java:238)
org.springframework.web.servlet.view.AbstractView. render(AbstractView.java:250)
org.springframework.web.servlet.DispatcherServlet. render(DispatcherServlet.java:1047)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:817)
org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:719)
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:669)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:574)
javax.servlet.http.HttpServlet.service(HttpServlet .java:617)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
de.jawb.avtostop.controller.filter.CharsetFilter.d oFilter(CharsetFilter.java:41)

root cause

java.net.SocketException: Broken pipe
java.net.SocketOutputStream.socketWrite0(Native Method)
java.net.SocketOutputStream.socketWrite(Unknown Source)
java.net.SocketOutputStream.write(Unknown Source)
java.io.BufferedOutputStream.flushBuffer(Unknown Source)
java.io.BufferedOutputStream.flush(Unknown Source)
com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3345)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:19 83)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :2163)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2618)
com.mysql.jdbc.ConnectionImpl.setAutoCommit(Connec tionImpl.java:5000)
com.mchange.v2.c3p0.impl.NewProxyConnection.setAut oCommit(NewProxyConnection.java:881)
org.hibernate.connection.C3P0ConnectionProvider.ge tConnection(C3P0ConnectionProvider.java:83)
org.hibernate.jdbc.ConnectionManager.openConnectio n(ConnectionManager.java:446)...
org.springframework.web.servlet.view.InternalResou rceView.renderMergedOutputModel(InternalResourceVi ew.java:238)
org.springframework.web.servlet.view.AbstractView. render(AbstractView.java:250)
org.springframework.web.servlet.DispatcherServlet. render(DispatcherServlet.java:1047)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:817)
org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:719)
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:669)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:574)
javax.servlet.http.HttpServlet.service(HttpServlet .java:617)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
de.jawb.avtostop.controller.filter.CharsetFilter.d oFilter(CharsetFilter.java:41)

How can i solve the problem?

Thx

[Marten Deinum]

Ehrm.... Why are you using that way to setup your sessionfactory? Use one of springs SessionFactoryBean implementations to bootstrap hibernate. However that isn't related to your problem, you have ran into a documented issue with MySQL which times out connections and doesn't release them properly. The solution is already in your stacktrace.

com.mysql.jdbc.exceptions.jdbc4.CommunicationsExce ption: The last packet successfully received from the server was 44,190,616 milliseconds ago. The last packet sent successfully to the server was 44,190,616 milliseconds ago. 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.

When you use Spring you could also set a test sql for your connection (not sure if that is possible with the basic hibernate settings) before it will be used, when it fails the pool will then discard the connection and get a new one.

[dit]

Hi

could you pls explain how can i setup the sessionfactory with SessionFactoryBean? I found examples with Spring 2 only (XML <bean> definition), but im using Spring v3...

property 'autoReconnect=true' is setted btw

[Marten Deinum]

The XML stuff still works in Spring 3. If you use Spring 3 with java based configuration simply add the factorybean and return it (if you use hibernate4 there is a LocalSessionFactoryBuilder you can use.).