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

用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