Java与MySql连接问题
我使用的是Java+MySql。我正在尝试添加批量数据（大约 4000 万条记录）。我的应用程序对于几十万条记录工作正常，但之后它开始出现以下异常：
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.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 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2333)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor5.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.jboss.Database.DatabaseConnection.getConnection(DatabaseConnection.java:48)
at net.jboss.emp.idm.adta(mde.java:96)
at net.jboss.emp.idm.main(mde.java:69)
Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:257)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:294)
... 16 more
我用谷歌搜索了这个问题并尝试了几乎所有选项（更新 Windows 注册表、更改 my.cnf 文件、更改 mysql 全局参数等），但它不是在职的。
I am using Java+MySql. I am trying to add bulk data (around 40 million records). My application works fine for couple of hundred thousands records but after that it starts giving me following exception:
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.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 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2333)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor5.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.jboss.Database.DatabaseConnection.getConnection(DatabaseConnection.java:48)
at net.jboss.emp.idm.adta(mde.java:96)
at net.jboss.emp.idm.main(mde.java:69)
Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:257)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:294)
... 16 more
I googled this problem and tried almost every option (updating windows registry, changing my.cnf file, changing mysql global parameters etc.) but its not working.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
几个小时前我遇到了类似的问题。使用默认设置，我可以在单个事务中写入大约 15K 记录。尝试写更多的内容给了我完全相同的消息（“没有可用的缓冲区空间（达到最大连接数？）：连接”）。
然后，我将事务分解为 1000 条记录的块（我的应用程序要求允许这样做），并且在第 15 次迭代后我仍然收到该消息。我的应用程序正在努力创建一个新的 EntityManager 并在每次迭代时关闭它。
解决方案是在迭代之间清除该特定类型实体的缓存（您也可以按实体清除缓存）。
EntityManagerFactory.getCache().evictAll 或 evict(...)
I was hitting a similar problem a couple of hours ago. With the default settings, I could write some 15K records in a single transaction. Attempting to write any more than that gave me the exact same message ("No buffer space available (maximum connections reached?): connect") .
I then broke down the transactions in chunks of 1000 records (my application requirements allowed for it) and I still received the message after the 15th iteration. My application was diligently creating a new EntityManager and closing it for each iteration.
The solution was to clear the cache for that particular type of entity in between iterations (you can also clear the cache by entity) .
EntityManagerFactory.getCache().evictAll or evict(...)
看来连接没有正确关闭。我建议您使用 connection-pooling
另请参阅
It seems connection isn't being closed properly. I would recommend you to use connection-pooling
Also See
这可能是与 mysql 失去连接或
您的代码无法关闭现有连接，因为您说它可以启动一些记录。因此，如果 mysql 接受来自其他程序的连接，请增加与 mysql 的最大连接，或者当您收到此错误消息时尝试检查其他程序。
this could be probably either lost connection with mysql or
your code could not be able to close existing connetction as u said it work for starting some records. so incresce max connection with mysql or try to check with other program when u get this error message if mysql accept connection from other program.
我遇到了完全相同的问题，并且在每次调用可执行语句后通过
connectionname.close();语句修复了它。I was having the exact same problem and I fixed it by
connectionname.close();statement after every time an executable statement is called.这现在可以解决您的问题:)
通讯异常
this can resolve your issue now :)
CommunicationsException