yankj12 / blog

技术研究、管理实践、其他的一些文章
MIT License
1 stars 2 forks source link

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. #26

Open yankj12 opened 6 years ago

yankj12 commented 6 years ago
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
### Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
    at com.sinosoft.prpallVinAnalysis.service.impl.VinPlatLogDaoServiceSpringImpl.insertVinPlatLog(VinPlatLogDaoServiceSpringImpl.java:22)
    at com.sinosoft.prpallVinAnalysis.AyalysisMain.analysisLogFile(AyalysisMain.java:66)
    at com.sinosoft.prpallVinAnalysis.AyalysisMain.main(AyalysisMain.java:42)
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:172)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:201)
    at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:196)
    at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.getConnection(UnpooledDataSource.java:93)
    at org.apache.ibatis.datasource.pooled.PooledDataSource.popConnection(PooledDataSource.java:404)
    at org.apache.ibatis.datasource.pooled.PooledDataSource.getConnection(PooledDataSource.java:90)
    at org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:139)
    at org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:61)
    at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:338)
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    ... 4 more
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    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.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:59)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:103)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:149)
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:165)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:92)
    at com.mysql.cj.NativeSession.connect(NativeSession.java:152)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:982)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:852)
    ... 22 more
Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): connect
    at java.net.DualStackPlainSocketImpl.connect0(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:589)
    at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:173)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:66)
    ... 25 more
yankj12 commented 6 years ago

问题背景:

要将一个文本文件中每一行读取出来保存到数据库,文本文件有20w行,java代码实现是每读取一行,获取一个session然后保存。

问题现象:

每次读取到1.6w的时候,就会报如上错误

问题解决:

使用mybatis的批量保存,每读取1000行数据,保存一次。减少短连接次数。

问题分析:

No buffer space available (maximum connections reached?): connect 因为程序只运行了几分钟,所以排除网上其他的说法。 有不当程序创建了大量的短连接,由于程序的不健壮,导致 SOCKET 处于忙碌或者 TIME_WAIT 状态,或者服务器和客户端有一方出现异常,而另一方没有得到通知,还在连接,持续不断的做写操作 (无大量Socket 连接,高峰时间有8-10个 TIME_WAIT 状态的连接)

mybatis批量提交