Hibernate/Mysql, connection lost

Question

I'm working on a Java project using Hibernate, Spring and MySQL. My system works perfectly in localhost using XAMPP but when I upload it to a remote server it has a strange behavior. The moment I deploy my application it works fine (I can login into my website, etc) but after a few seconds of inactivity (I reckon 20 seconds more or less) I cannot connect anymore to my database. This is the error displayed in the catalina log:

Apr 12, 2016 5:55:54 PM org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter doFilter
SEVERE: An internal error occurred while trying to authenticate the user.
org.springframework.security.authentication.InternalAuthenticationServiceException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 31,464 milliseconds ago.  The last packet sent successfully to the server was 24 milliseconds ago.
    at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:126)
    at org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:143)
    at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:167)
    at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:192)
    at org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:93)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:217)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:120)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:120)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:53)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:213)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:176)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:957)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Caused by: javax.persistence.PersistenceException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 31,464 milliseconds ago.  The last packet sent successfully to the server was 24 milliseconds ago.
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1700)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:48)
org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:114)
    ... 40 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 31,464 milliseconds ago.  The last packet sent successfully to the server was 24 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:981)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3465)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3365)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3805)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
    at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4874)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:912)
    at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:67)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:238)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214)
    at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:52)
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1512)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:45)
    ... 45 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2957)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3375)
    ... 58 more

The configuration is done in a file called 'persistence.xml', this is the configuration in this file:

<persistence-unit name="project" transaction-type="RESOURCE_LOCAL">
        <description>My Persistence Unit</description>
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
            <property name="javax.persistence.jdbc.url"
                value="jdbc:mysql://127.0.0.1:3306/my_db" />

            <property name="javax.persistence.jdbc.user" value="MyUser" />
            <property name="javax.persistence.jdbc.password" value="MyPass" />

            <property name="hibernate.connection.provider_class" value="org.hibernate.c3p0.internal.C3P0ConnectionProvider"/>

            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.hbm2ddl.auto" value="validate" />

            <property name="hibernate.c3p0.max_statements" value="50" />
            <property name="hibernate.cache.use_query_cache" value="false" />
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.EhCacheProvider" />
            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.use_sql_comments" value="true" />
            <property name="hibernate.c3p0.timeout" value="1800" />
            <property name="hibernate.c3p0.testConnectionOnCheckout" value="true" />
            <property name="hibernate.c3p0.initialPoolSize" value="3" />
            <property name="hibernate.c3p0.acquire_increment" value="3" />
        </properties>
    </persistence-unit>

I didn't write this code, this was done by a colleage so I don't know if something is missing in the code regarding the configuration. Maybe the problem is in the server configuration.

Any help would be appreciated.

once try change provider_class value and check <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" /> and remove this line <property name="connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" /> — Piyush Gupta
– Piyush Gupta, Commented Apr 13, 2016 at 9:03
@PiyushGupta I don't find that file in my project, everything is in the persistence.xml. Is this hibernate file totally necessary? I don't have any experience with hibernate. I have also tried changed the code line you mentioned but the same error remains. — Luis Pascual
– Luis Pascual, Commented Apr 13, 2016 at 9:10
After add that modification you need to clean and build project again then check, it is working or not?....or do one more thing check connection also using ping command — Piyush Gupta
– Piyush Gupta, Commented Apr 13, 2016 at 9:30
@PiyushGupta After the modification is done it happens the same, I can connect but if the website is inactive for some seconds the jdbc connection is lost. I have also ping the connection and it is correct. — Luis Pascual
– Luis Pascual, Commented Apr 13, 2016 at 9:35
May be this is interactive_timeout issue, so open check SHOW VARIABLES like 'interactive_timeout' ; and increase this value using this command SET interactive_timeout=32000 ; — Piyush Gupta
– Piyush Gupta, Commented Apr 13, 2016 at 9:53

Luis Pascual · Accepted Answer · 2016-04-14 07:31:14Z

1

Finally I have managed to solve this problem. Hibernate configuration was good, the issue was in the MySQL parameters in the server provider. I talked to them and the 'wait_timeout' parameter was 20 seconds (it totally fits with my calculation) so I asked them to upgrade this timeout to 28800 seconds and now it's working properly.

answered Apr 14, 2016 at 7:31

Luis Pascual

2551 gold badge4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hibernate/Mysql, connection lost

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related