Java and MySql connection problem

Question

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.

please can u feel free to post ur jdbc drivers registered code here — developer
– developer, Commented May 17, 2011 at 6:00
Please show us your code. My guess is that you're opening the connection for every record, but forget to close it (you should only open it once and use a parameterized query afterwards). — Bobby
– Bobby, Commented May 17, 2011 at 7:35

Denilson · Accepted Answer · 2012-08-06 20:36:53Z

2

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(...)

answered Aug 6, 2012 at 20:36

Denilson

1045 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jigar Joshi · Accepted Answer · 2011-05-17 05:54:07Z

1

It seems connection isn't being closed properly. I would recommend you to use connection-pooling

Also See

Connection pooling with MySQL Connector/J

answered May 17, 2011 at 5:54

Jigar Joshi

241k42 gold badges409 silver badges446 bronze badges

3 Comments

user751637 Over a year ago

Thanks for reply, I am opening and closing connections properly. If this is the case then why does it work for first couple of hundred thousands records?

Jigar Joshi Over a year ago

error message says (maximum connections reached?) probably after some transactions connection aren't being closed properly and so the result..

user751637 Over a year ago

I double checked and didnt find any problem with total number of connections.

Ravi Parekh · Accepted Answer · 2011-05-19 12:46:13Z

0

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.

answered May 19, 2011 at 12:46

Ravi Parekh

5,69010 gold badges49 silver badges59 bronze badges

Comments

Buddy · Accepted Answer · 2017-04-03 18:10:44Z

0

I was having the exact same problem and I fixed it by connectionname.close(); statement after every time an executable statement is called.

edited Apr 3, 2017 at 18:10

Buddy

11k5 gold badges47 silver badges63 bronze badges

answered Apr 3, 2017 at 18:08

JavaGeek

5915 silver badges14 bronze badges

Comments

developer · Accepted Answer · 2011-05-17 06:01:08Z

-1

this can resolve your issue now :) CommunicationsException

answered May 17, 2011 at 6:01

developer

9,54830 gold badges95 silver badges155 bronze badges

1 Comment

Ahmad Hajjar Over a year ago

plz post the answer here rather than linking to your wordpress

Collectives™ on Stack Overflow

Java and MySql connection problem

5 Answers 5

Comments

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related