Derby error

[ae00019]

I use the following code in my class to connect with derby db:

CachedRowSet rowSet = null;
Class.forName(driver);
rowSet = new CachedRowSetImpl();
rowSet.setUrl(url); // set database URL
rowSet.setUsername(uname); // set username
rowSet.setPassword(dpass); // set password

I was wondering why I get the following error:

No buffer space available (maximum connections reached?): connect.

Is there a better implementation than the above for avoiding that error?

Thank you

[dingfelder]

this link talks about your issue and a possible solution:

http://kb2.adobe.com/cps/400/kb400700.html

they are saying:

This error typically occurs when a database query is called and there are a large number of TCP connections in TIME_WAIT state -- the interval between when the connection is closed and when its release back into the pool for reuse. By default this interval is four (4) minutes. If there is a large number of connections on the server, then each connection can wait up to 4 minutes before their resources are released for reuse by other connections.
Solution

Decreasing the TIME_WAIT interval will allow TCP to release closed connections faster, thus increasing the number of available resources for open connections. The TIME_WAIT value is governed by the TCPTimedWaitDelay Windows Registry setting.

Now... not sure if you are on windows or if there is a better way to tweak the time wait via db settings but maybe it will get you started in the right direction?

[mdeggers]

Are you cleaning up and closing your connections after doing your database
access?

In web applications using connection pools this error is often due to not:

1. Cleaning up your objects, even when there's an exception
2. Releasing connection back to the pool, even when there's an exception

A good discussion on this can be found here:

http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/

I wrote and posted a simple base class for non-transaction database
access (using a connection pool). Take a look at the following for an
example:

http://forums.netbeans.org/viewtopic.php?t=40604

Even if you're not writing a web application or using JNDI-based connection
pools, cleaning up properly is a good idea.

. . . . just my two cents.
/mde/

[ae00019]

Thank you very much for your replies!!

@mdeggers: I am using CachedRowSetImpl, so i am not opening any db connection as you are describing it.

Every time i need to make a call to the database i use the following code:

Class.forName(driver);
rowSet = new CachedRowSetImpl();
rowSet.setUrl(url); // set database URL
rowSet.setUsername(uname); // set username
rowSet.setPassword(dpass); // set password

String sqlCmnd = "Select * from Table";

rowSet.setCommand(sqlCmnd);
rowSet.execute();



I do not really understand which piece of the above code opens a connection to the db, and therefore causing the maximum connections reached problem.

Should I close the rowSet everytime or set it to null??

Thank you!!!

[mdeggers]

I've not used CachedRowSet before, but it looks sort of interesting.

According to the javadoc (which I recommend that you read), a
CachedRowSet object only makes a connection to the database while reading
or updating information. Otherwise it's just another object.

However, it looks like a lot of methods throw SQLExceptions, and you do
need to call close() in order to release the objects.

Taking the above into consideration, here's a quick (untested) hack of the
BaseDAO I posted in another thread. Clean it up, and then possibly extend
it to implement your actual queries and updates.

I've not included the imports, so this class will not compile as is. However
it should give you a base idea as to what is going on (provided I
understand what is going on).

[ae00019]

Thank you for your help!!!

I do all that you have advised but i still get the same error

[mdeggers]

Maybe if you post your entire class you use to connect and query the
database?? It's hard to determine without more information. That's why
logging exceptions is so important.

You could also run it through the debugger and see exactly where it stops.

. . . . just my two cents.
/mde/

[mdeggers]

OK, I just hacked together a quick project and it works as expected.

It really doesn't use the interesting features of CachedRowSetImpl, but
at least it works.

As an exercise, you could modify the code in SalesRecordInfo.java
to return a CachedRowSetImpl object, and then make sure you clean up
(with an appropriate cleanup routine) when you're done with it.

I hope you find the code useful. Be sure to read the quick notes I wrote
in a notes directory, telling you a little bit about the database and the
environment this runs on.

Hope this helps.

. . . . just my two cents.
/mde/

[ae00019]

Still the same problem.

Is there a way to monitor the open database connections in Netbeans 7.0?

Thank you!!

[mdeggers]

If you have the JConsole plugin installed, you can launch JConsole and
take a look at the MBeans.

JConsole is usually at netbeans_jdkhome/bin/jconsole.

I've attached some png flies to show you how it looks on my system.

Fedora 15
Gnome 3
JDK 1.6.0_26
NetBeans 7.0.1

1. Start up JConsole
2. Attach to the correct process
3. Click on the MBeans tab
4. Expand the org.apache.derby node
5. Expand the NetworkServer node
6. Expand the serialized name below
7. Click on the Attributes node to see the values
8. Periodically click on the the Refresh button to update the information

I've attached some screen shots.

netbeans-derby.png shows NetBeans connected to one of several Derby
databases.

jconsole-derby-02.png shows you the default look when you attach
JConsole to a Derby server (or any process, actually).

jconsole-derby-05.png shows you browsing the attributes under the
Derby server, with all of the interesting information available.

I hope this helps you debug your connections. The project I posted in my
previous response works cleanly on my system.

The first query: asr = sri.getAllRecords();

returns 194 rows.

The second query: nsr = sri.getNameRecord("Rice Ice");

returns 22 rows.

. . . . just my two cents.
/mde/

[jerry0503222]

Are you cleaning up and closing your connections after doing your database
access?

In web applications using connection pools this error is often due to not:

1. Cleaning up your objects, even when there's an exception
2. Releasing connection back to the pool, even when there's an exception







____________________________________________
microsoft office professional 2010,outlook 2010

[ae00019]

Thank you so much for your help mdeggers!!

I still get the same error despite the fact that maximum 3 connections are open at a time!!

I have attached the screenshot from the JConsole preview.

I have noticed that AccumulatedConnectionCount is really big, is that ok?

THANK YOU!!!!

[ae00019]

Could anybody pls explain why AccumulatedConnectionCount (pls refer to previous post) it gets so big? And if this is related to the error :

java.sql.SQLTransientConnectionException: java.net.SocketException : Error connecting to server localhost on port 1527 with message No buffer space available (maximum connections reached?): connect.

Thank you!!!

[mdeggers]

Sorry for the delay - life gets in the way sometimes :-p.

Overview

The number of connections could very well be a problem, depending on
how fast you generated them.

The reason behind this problem is how TCP connections operate. I'll
roughly summarize RFC 793.

1. A connection is made using a three way handshake
2. Data is sent
3. A connection is closed using a full duplex handshake

This last step is important. The side initiating the closing (your
Derby server) ends up in a final TCP_WAIT state. This is done at the
OS / Networking layer to make sure all of the data has been reliably
delivered.

By default, the TCP_WAIT state lasts for 240 seconds (2 times the
maximum segment life). While the socket is in a TCP_WAIT state, it
cannot be reused by the OS (obviously).

If you make and break lots of connections in a short amount of time,
you can run out of socket resources.

To check that, you can download TCPView for Windows at:

http://technet.microsoft.com/en-us/sysinternals/bb897437

If you see a lot of connections in TIME_WAIT when running your program
(or shortly after), you have a problem.

There are several solutions to this problem, depending on your use
case. I'll outline 4 common ones below.

Use Cases

1. Use Case 1 - Web application with lots of users

This is probably the most common use case. Your program is part of a
web application, and lots of people make lots of database requests.

Fortunately, there is a relatively standard solution to this
problem. It's called database connection pooling.

Application servers (such as Glassfish) and servlet containers (such
as Tomcat), provide a way to create pools of database
connections. The server controls the number of connections, can grow
the pool if needed, shrink the pool if needed, and makes sure that the
connections are valid.

To the database, the client (your application running in a server) has
a small number of more or less permanent connections. To your
application's users, it looks like you have a virtually unlimited
number of connections.

Configuration is server-specific, but there is good documentation on
how to do this for all major servers.

Your application usually (at least on Glassfish and Tomcat) accesses
the connection pool via JNDI. To your application, it looks just
exactly like a standard JDBC connection. You should close and clean up
the connection in exactly the same manner.

Here's one example on how to do this:

http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/

Here's a connection method that can be placed inside a base class:

[Bob McConnell]

From: mdeggers