com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure， Caused byNo buffer space available (maxim

Java批量插入MySQL数据异常

最新推荐文章于 2024-12-06 19:36:20 发布

原创最新推荐文章于 2024-12-06 19:36:20 发布 · 3.2w 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#buffer #null #jdbc #user #mysql #url

本文介绍了一种在使用Java批量向MySQL数据库插入大量数据时遇到的“CommunicationsLinkFailure”异常及其解决方法。问题根源在于连接管理和资源释放不当导致连接耗尽。文章详细展示了通过合理管理数据库连接和确保及时关闭资源来避免该问题的有效实践。

用Java往mysql中插入数据（大概几千万条，耗时比较长）时出现错误如下：

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

Last packet sent to the server was 0 ms ago.
        at sun.reflect.GeneratedConstructorAccessor10.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2103)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
        at sun.reflect.GeneratedConstructorAccessor8.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)
        at decompress.DeCompressView.jButton2ActionPerformed(DeCompressView.java:734)
        at decompress.DeCompressView.access$1400(DeCompressView.java:43)
        at decompress.DeCompressView$7.actionPerformed(DeCompressView.java:416)
        at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
        at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
        at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
        at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
        at java.awt.Component.processMouseEvent(Component.java:6263)
        at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
        at java.awt.Component.processEvent(Component.java:6028)
        at java.awt.Container.processEvent(Container.java:2041)
        at java.awt.Component.dispatchEventImpl(Component.java:4630)
        at java.awt.Container.dispatchEventImpl(Container.java:2099)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4574)
        at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
        at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
        at java.awt.Container.dispatchEventImpl(Container.java:2085)
        at java.awt.Window.dispatchEventImpl(Window.java:2478)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): connect

从红色部分文字判断，

应该是连接到了最大数的问题。在网上找了很久，有同样症状，但问题多源于超时，他们的解决方法，都没能解决我的问题。

我想我这还是应该从解决链接最大数入手。最终我的代码修改成如下解决了问题：

String url="jdbc:mysql://localhost/weblog";
String user="root";
String password="";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url,user,password);
Statement stm = conn.createStatement();

while (Line != null)
{

.......
String insql = "insert into log values (null,'"+IPAddr+"',null,null,null)";
try
{
stm.execute(insql);
}
catch (SQLException e)
{
e.printStackTrace();
}
}//end while
stm.close();
conn.close();
}
而原来出问题原因是我把

Connection conn=DriveManager.getConnection(url,user,password);

Statement stm = conn.createStatement();

写在while循环中，而且没有

stm.close()

conn.close()两句。这样当然会出问题了。

后来在while循环中加了关闭连接的两句，但还是出同样的问题。

后来把生成conn, stm的语句，以及关闭的语句都放在while之外，就解决了。

网上同样症状的问题，但是因为超时引起的，解决办法如下地址：

http://blog.csdn.net/leizhendong/archive/2007/04/16/1566484.aspx

http://www.wangzhongyuan.com/archives/610.html

http://www.svn8.com/mysql/200906036013.html