mybatis batch insert or update is a good solution to save resources and connections in many business scenarios. However, sometimes when the database configuration, especially the connection timeout and waiting time, is unreasonable, the following errors may be reported when performing batch operations.

1. Examples of error reporting:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 9 milliseconds ago.  The last packet sent successfully to the server was 18 milliseconds ago.
	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:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3751)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2512)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
	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:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy230.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	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:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy228.update(Unknown Source)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	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:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	at com.sun.proxy.$Proxy109.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy214.batchUpdate(Unknown Source)
	at com.huobi.open.mis.assets.service.sys.impl.rateServiceImpl.batchUpdate(SymbolRateServiceImpl.java:41)
	at com.huobi.open.mis.assets.task.asyn.rateTask.scheduled(SymbolRateTask.java:276)
	at com.huobi.open.mis.assets.task.asyn.rateTask.execute(SymbolRateTask.java:66)
	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:498)
	at com.huobi.open.mis.assets.task.base.ScheduleRunnable.run(ScheduleRunnable.java:44)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266)
	at java.util.concurrent.FutureTask.run(FutureTask.java)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	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(SocketOutputStream.java:111)
	at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
	at java.io.BufferedOutputStream.write(BufferedOutputStream.java:122)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3732)
	... 51 common frames omitted

2. Solution:

The solutions to such problems are generally diverse, such as properly configuring the connection time, waiting time and execution time of the database; At the same time, the number of pieces of data executed in batch each time should be reasonable or less, generally not more than 500 pieces; Comprehensively solve the above problems. For example:

We can check the default connection waiting time of your mysql through the following methods:

show global variables like 'wait_timeout';

Display:

The time unit of this query is seconds (s)

Wait_ During the timeout time, the mysql connection is in a waiting state. Once this time is exceeded, mysql 5 will be closed. However, there is still a legal connection in the connection pool in our project. When your program needs to access the database again, the above exception will appear. However, if you access it again, you can get data from the database normally.

This time can be modified. In windows, it can be set to 24 days at most. You need to modify the my.ini file in the installation folder of mysql5.

The maximum time in LInux is 365 days. You need to modify the / etc/my.cnf file. Add a line to the configuration file: wait_timeout=1814400 (about 21 days) takes effect after restarting the mysql service.

Alternatively, sql can be used for modification, but it will recover to 8 hours after restart. Therefore, this method is not recommended:

set global wait_timeout=1814400;

However, even if you modify the time, if the system is not used for a long time, once the time you set is exceeded, the above exceptions will still occur when you visit again. Therefore, I have found another way to solve the problem permanently. Configure in the configuration file, permanent.