MySQl and autoReconnect=true still gives me exception

Question

I'm using Spring and Hibernate with MySQL and I have a problem with its timeout. I used autoReconnect=true at the end of database URL but I still receive the exception like this below:

2016-08-03 01:01:00 - SCHEDULED ACTIVITY TO DELETE OLD NOTIFICATIONS
2016-08-03 01:01:00 - Threw exception in WakeUpDatabase::smartQuery :com.exception.QueryException: Error using database 
    at com.services.dbservices.DatabaseNotificationServicesImpl.findAllNotifications(DatabaseNotificationServicesImpl.java:160)
    at com.services.NotificationManagementServicesImpl.deleteOldNotifications(NotificationManagementServicesImpl.java:47)
    at com.services.WakeUpDatabase.smartQuery(WakeUpDatabase.java:33)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed: 
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy286.findAll(Unknown Source)
    at com.services.dbservices.tables.NotificationServicesImpl.findAllNotifications(NotificationServicesImpl.java:37)
    at com.services.dbservices.DatabaseNotificationServicesImpl.findAllNotifications(DatabaseNotificationServicesImpl.java:158)
    ... 16 more
Caused by: javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed: 
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1771)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:64)
    at org.springframework.orm.jpa.DefaultJpaDialect.beginTransaction(DefaultJpaDialect.java:67)
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)
    ... 31 more
Caused by: org.hibernate.TransactionException: JDBC begin transaction failed: 
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:76)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162)
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1471)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:61)
    ... 33 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 46,107,953 milliseconds ago.  The last packet sent successfully to the server was 46,107,953 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.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3621)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2429)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
    at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4882)
    at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72)
    ... 36 more
Caused by: java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3603)
    ... 43 more

This is the database bean configuration:

public class AppConfig extends WebMvcConfigurerAdapter implements AsyncConfigurer
   ....
   @Bean(name = "dataSource")
       public BasicDataSource dataSource() {
           BasicDataSource ds = new BasicDataSource();
           ds.setDriverClassName(env.getRequiredProperty(PROPERTY_NAME_DATABASE_DRIVER));
           ds.setUrl(env.getRequiredProperty(PROPERTY_NAME_DATABASE_URL));
           ds.setUsername(env.getRequiredProperty(PROPERTY_NAME_DATABASE_USERNAME));
           ds.setPassword(env.getRequiredProperty(PROPERTY_NAME_DATABASE_PASSWORD));
           return ds;
    }

In my application.properties I have

#DB properties: 
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://dbAddress:3306/schemaName?autoReconnect=true
db.username=username
db.password=password

How can I avoid this tedious exception?Thanks

UPDATE: I add

ds.setValidationQuery("SELECT 1");

to my configuration, now I have to see if the exception will be thrown again

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

You see the following link about the exception :-Why does Hibernate/JDBC/MySQL drop connections after a day or so?

Try to using the setting following property in hibernate configuration

property name="connection.autoReconnect" = true
property name="connection.autoReconnectForPools"= true
property name="connection.is-connection-validation-required" = true

If do not want to use the autoReconnect and wait_time , then go for the ,its is only the suggestion, schedular.

Make the scheduler which run after every 7 hours and run some test query.This make the MySql server active forever.

edited May 23 '17 at 10:28

Community

1
1

answered Aug 03 '16 at 06:59

Ashwani Tiwari

1,497
18
28

The use of autoReconnect is not recommended, so how can I fix this problem? I don't think setting wait_timeout to 48h for example is a solution. Maybe I should catch exception and retry the query, but I would like to avoid duplicate code – luca Aug 03 '16 at 08:43
I already use a query every 3h but it is not enough. You can see about this here http://stackoverflow.com/questions/38454118/hibernate-and-mysql-broken-pipe – luca Aug 03 '16 at 09:12

MySQl and autoReconnect=true still gives me exception

1 Answers1