[Java + Hibernate] problema DB

[DarthSandr]

Salve a tutti.
In un progetto web spring + hibernate installato su tomcat8 succede un errore che non sono riuscito a capire.
Sembra che se dall'ultimo accesso al DB (il DB è mysql) passa più tempo del wait_timeout impostato sul DB (28800 secondi) la prima query che faccio mi da un errore di accesso alla connessione JDBC.
L'errore è il seguente:

2018-05-21 07:59:23 DEBUG SqlExceptionHelper:124 - Unable to acquire JDBC Connection [n/a]
com.mysql.jdbc.exceptions.jdbc4.CommunicationsExce ption: The last packet successfully received from the server was 234,334,581 milliseconds ago. The last packet sent successfully to the server was 234,334,581 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.
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Construc tor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:40 6)
at com.mysql.jdbc.SQLError.createCommunicationsExcept ion(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3313)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:19 40)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2642)
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(Connec tionImpl.java:5034)
at org.hibernate.engine.jdbc.connections.internal.Poo ledConnections.poll(PooledConnections.java:76)
at org.hibernate.engine.jdbc.connections.internal.Dri verManagerConnectionProviderImpl.getConnection(Dri verManagerConnectionProviderImpl.java:187)
at org.hibernate.internal.AbstractSessionImpl$NonCont extualJdbcConnectionAccess.obtainConnection(Abstra ctSessionImpl.java:386)
at org.hibernate.resource.jdbc.internal.LogicalConnec tionManagedImpl.acquireConnectionIfNeeded(LogicalC onnectionManagedImpl.java:84)
at org.hibernate.resource.jdbc.internal.LogicalConnec tionManagedImpl.getPhysicalConnection(LogicalConne ctionManagedImpl.java:109)
at org.hibernate.resource.jdbc.internal.LogicalConnec tionManagedImpl.getConnectionForTransactionManagem ent(LogicalConnectionManagedImpl.java:227)
at org.hibernate.resource.jdbc.internal.LogicalConnec tionManagedImpl.begin(LogicalConnectionManagedImpl .java:234)
at org.hibernate.resource.transaction.backend.jdbc.in ternal.JdbcResourceLocalTransactionCoordinatorImpl $TransactionDriverControlImpl.begin(JdbcResourceLo calTransactionCoordinatorImpl.java:214)
at org.hibernate.engine.transaction.internal.Transact ionImpl.begin(TransactionImpl.java:52)
at org.hibernate.internal.SessionImpl.beginTransactio n(SessionImpl.java:1525)
at it.telecomitalia.ffserver.conversationmanager.auth .dao.AgentDao.getAgentById(AgentDao.kt:26)
at it.telecomitalia.ffserver.conversationmanager.disp atcher.google.GoogleUCDispatcher.manageRequest(Goo gleUCDispatcher.kt:50)
at it.telecomitalia.ffserver.conversationmanager.endp oints.google.GoogleController.manageDialogFlowRequ est(GoogleController.kt:54)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.bind.annotation.support.Ha ndlerMethodInvoker.invokeHandlerMethod(HandlerMeth odInvoker.java:175)
at org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.invokeHandlerMethod(An notationMethodHandlerAdapter.java:446)
at org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.handle(AnnotationMetho dHandlerAdapter.java:434)
at org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:943)
at org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:877)
at org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:966)
at org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:868)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:648)
at org.springframework.web.servlet.FrameworkServlet.s ervice(FrameworkServlet.java:842)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:729)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilt er(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBas e.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve. invoke(AbstractAccessLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:522)
at org.apache.coyote.http11.AbstractHttp11Processor.p rocess(AbstractHttp11Processor.java:1095)
at org.apache.coyote.AbstractProtocol$AbstractConnect ionHandler.process(AbstractProtocol.java:672)
at org.apache.tomcat.util.net.NioEndpoint$SocketProce ssor.doRun(NioEndpoint.java:1520)
at org.apache.tomcat.util.net.NioEndpoint$SocketProce ssor.run(NioEndpoint.java:1476)
at java.util.concurrent.ThreadPoolExecutor.runWorker( ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$Wrapping Runnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.net.SocketException: Broken pipe (Write failed)
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutp utStream.java:111)
at java.net.SocketOutputStream.write(SocketOutputStre am.java:155)
at java.io.BufferedOutputStream.flushBuffer(BufferedO utputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputS tream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3294)
... 52 more
2018-05-21 07:59:23 WARN SqlExceptionHelper:129 - SQL Error: 0, SQLState: 08S01
2018-05-21 07:59:23 ERROR SqlExceptionHelper:131 - The last packet successfully received from the server was 234,334,581 milliseconds ago. The last packet sent successfully to the server was 234,334,581 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.

Dalla seconda query torna a funzionare e il valore riportato è proprio il lasso di tempo trascorso dall'ultima volta che ho eseguito una query sul DB.
E' come se qualcosa non venisse chiuso correttamente sopo una query ma non capisco cosa.
Di seguito il codice del mio DB manager (java)

private static SessionFactory createSessionFactory() throws Exception {


        logger.debug("getSessionFactory -> Entering method.");
        logger.debug("getSessionFactory -> Getting Hibernate session factory object.");


        try {
            Configuration configuration = new Configuration();
            configuration.addAnnotatedClass(UtentiVO.class);
            configuration.configure();


            Properties prop = configuration.getProperties();
            
            StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder();
            builder.applySettings(configuration.getProperties());
            Utilities.logDebug("getSessionFactory -> Builder created", logger);


            ServiceRegistry servRegistry = builder.build();
            Utilities.logDebug("getSessionFactory -> ServiceRegistry created", logger);


            SessionFactory sessionFactory = configuration.buildSessionFactory(servRegistry);

            return sessionFactory;
        } catch (Exception e) {
            throw new Exception("An error occurred while obtaining session factory." + e.toString());
        }
    }

public static Session getCurrentSession() throws Exception {
        Session session = null;
        if (sessionFactory == null)
            sessionFactory = createSessionFactory();
        session = sessionFactory.getCurrentSession();


        return session;
    }


    public static SessionFactory getSessionFactory() throws Exception {
        if (sessionFactory == null)
            sessionFactory = createSessionFactory();


        return sessionFactory;
    }

e un esempio di funzione in cui eseguo una query (Kotlin)

@Throws(Exception::class)
    fun getUtentiUserAgentByUserAgentId(agentUserId: String, agentId: Int): UtentiUserAgentVO?  {
        var session: Session? = null
        var tx: Transaction? = null
        var res: UtentiUserAgentVO? = null
        try {
            val sql = "SELECT uua.ID_UTENTI_USER_AGENT, uua.ID_USER_AGENT, uua.ID_UTENTE " +
                      "FROM UTENTI_USER_AGENT uua, USER_AGENT ua, UTENTI u "+
                      "WHERE uua.ID_USER_AGENT = ua.ID_USER_AGENT "+
                      "AND uua.ID_UTENTE = u.ID_UTENTE "+
                      "AND ua.AGENT_USERID = :agentUserId "+
                      "AND ua.ID_AGENT = :agentId "


            // session = DBManager.getCurrentSession()
            session = DBManager.getSessionFactory().openSession()
            tx = session!!.beginTransaction()
            val query = session.createSQLQuery(sql)
            query!!.addEntity(UtentiUserAgentVO::class.java)
            query.setParameter("agentUserId", agentUserId)
            query.setParameter("agentId", agentId)
            val utentiUserAgents = query.list() as List<UtentiUserAgentVO>?
            if (utentiUserAgents != null && utentiUserAgents.size > 0) {
                res = utentiUserAgents.get(0)
            }
            else {
                Utilities.logDebug("getUtentiUserAgentByUserAgentId -> nessun risultato trovato :(", logger)
            }
            tx!!.commit()
        } catch (e: Exception) {
            Utilities.logError("getUtentiUserAgentByUserAgentId -> Error: " + e.toString(), logger)
            e.printStackTrace()
            if (session != null)
                if (tx != null)
                    tx.rollback()
            throw Exception("Errore durante la query", e)
        } finally {
            Utilities.logDebug("getUtentiUserAgentByUserAgentId -> Finally Exiting method.", logger)
            if (session != null) {
                Utilities.logDebug("getUtentiUserAgentByUserAgentId -> Closing session", logger)
                session.close()
            }
        }
        return res
    }