BindException (Address already in use) when connecting to MSSQL

T

Tage V. Madsen

I'm experiencing a problem with JDBC and MSSQL 2000, now and again
DriverManager.getConnection() throws a java.net.BindException, with
this message:

TCP/IP connection failed to host:java.net.BindException: Address
already in use: connect
url:jdbc:JSQLConnect://localhost:1433/database=DBx/

I've boiled the problem down to an example - listed below, after
connecting approx. 3960 times the exception is thrown - on my 3 Ghz
PC, on slower ones it works, so it must be timing-related...

To verify that MSSQL is working, I've created a similar app in
VC++/ODBC, and there it works fine - I can connect 100000 times
without problems.

I'm using JNetDirect's JDBC driver, but I've also tried the MS driver,
same behaviour. The use of DriverManager instead of a (pooled)
DataSource is merely for simplicity, the behaviour is, again, the
same, either way.

Any help will be greatly appreciated.

Code:

import java.sql.*;

public class Tester {
public static void main(String[] args) {
try
{
Class.forName("com.jnetdirect.jsql.JSQLDriver");
}
catch(ClassNotFoundException e)
{
}
for (int i=0;i<100000;i++)
{
justconn();
System.out.println("Did: "+i);
}
}

private static Connection getconn()
{
try
{
String url="jdbc:JSQLConnect://localhost:1433/database=DBx/";
return DriverManager.getConnection(url,"sa","sa");
}
catch(SQLException es)
{
System.out.println("too bad "+es.getMessage()); // Exception!!!
}
return null;
}

private static void justconn()
{
try{
Connection con = getconn();
con.close();
}
catch(SQLException e)
{
System.out.println("bad");
}
}
}
 
J

John C. Bollinger

Tage said:
I'm experiencing a problem with JDBC and MSSQL 2000, now and again
DriverManager.getConnection() throws a java.net.BindException, with
this message:

TCP/IP connection failed to host:java.net.BindException: Address
already in use: connect
url:jdbc:JSQLConnect://localhost:1433/database=DBx/

I've boiled the problem down to an example - listed below, after
connecting approx. 3960 times the exception is thrown - on my 3 Ghz
PC, on slower ones it works, so it must be timing-related...

If the diagnostic message is to be accepted as a reasonably accurate
description of the failure then it would seem to indicate a problem in
the driver, Java class library, or in Windows network stack. However,
it may well be that it is a problem that is not triggered in reasonable
use scenarios. Your code creates many connections to the same local
address and closes them again, all as fast as it can. This does not
model any reasonable use scenario; indeed if it persisted long enough
this test would constitute a DOS attack.

My best guess is that you are running into a system limit on the number
of open file descriptors. Invoking close() on your Connections will
cause their sockets to eventually be closed, but it may not force them
to close immediately. This would be a performance feature. However, if
you keep opening new sockets without giving the existing, no longer
needed ones a chance to close then you will eventually run into a file
descriptor limit.
To verify that MSSQL is working, I've created a similar app in
VC++/ODBC, and there it works fine - I can connect 100000 times
without problems.

That's nice, but not very relevant.
I'm using JNetDirect's JDBC driver, but I've also tried the MS driver,
same behaviour. The use of DriverManager instead of a (pooled)
DataSource is merely for simplicity, the behaviour is, again, the
same, either way.

I don't believe that you will see this behavior with a DataSource that
actually uses a connection pool if you ensure that the pool has a
reasonable maximum size. Indeed, even a size cap oughtn't to be
necessary in the single-threaded test scenario you present. Beware,
however, of DataSource implementations that may just wrap DriverManager
without providing pooling. Don't assume that a DataSource automatically
uses a pool.


John Bollinger
(e-mail address removed)
 
T

Tage V. Madsen

Thank you very much for your input..

John C. Bollinger said:
address and closes them again, all as fast as it can. This does not
model any reasonable use scenario; indeed if it persisted long enough
this test would constitute a DOS attack.

Actually I think you've got a point, about the DOS - I've tried doing
a telnet localhost 1433 afterwards, and it refuses the connection for
some time after the "attack" - but only when trying from the same PC!
I can telnet from another PC just fine. Maybe this is a MSSQL
self-protection mechanism?
That's nice, but not very relevant.

I think it is interesting that ODBC can connect that many times, at
the same pace, without being banned?
I don't believe that you will see this behavior with a DataSource that
actually uses a connection pool if you ensure that the pool has a
reasonable maximum size.

One would think so, yes, my understanding of a pooled datasource is
the same, and I simply can't figure out why this happens. I've changed
the previously posted example to use a pooled connection, see below,
but still exactly the same thing happens.

private static JSQLConnectionPoolDataSource m_ds1;

private static void setupdb()
{
m_ds1 = new JSQLConnectionPoolDataSource();
m_ds1.setMinPoolSize(100000);
m_ds1.setMaxPoolSize(1000000);
m_ds1.setServerName("localhost");
m_ds1.setDatabaseName("DBx");
m_ds1.setUser("sa");
m_ds1.setPassword("sa");
}
....
private static Connection getconn()
{
try
{
return m_ds1.getConnection();
}
catch(SQLException esq)
{
System.out.println("too bad "+esq.getMessage());
}
return null;
}
 
T

Tage V. Madsen

Figured it out!! You we're quite right, connection pooling works! The
thing is that the JNetDirect JSQLConnectionPoolDataSource class isn't
actually doing any connection pooling (duh!), instead the descendant
JSQLPoolingDataSource is to be used...which of course doesn't exist in
my ancient version of the driver, but that's another issue...

Thanks for pointing me in the right direction.

-Tage
 
J

John C. Bollinger

Tage said:
Thank you very much for your input..




Actually I think you've got a point, about the DOS - I've tried doing
a telnet localhost 1433 afterwards, and it refuses the connection for
some time after the "attack" - but only when trying from the same PC!
I can telnet from another PC just fine. Maybe this is a MSSQL
self-protection mechanism?

I couldn't say, but it's possible. I would guess, though, that it's a
lower-level effect of the network stack catching up with closing all the
connections.
I think it is interesting that ODBC can connect that many times, at
the same pace, without being banned?

Who says ODBC is the determining factor? When did you before claim that
both programs achieved the same pace? And who said anything about
banning? All you can really say is that the two programs are evidently
not behaving the same. One is a native Windows executable, and the
other is a Java program, so that's hardly a surprise. You would have to
do a much deeper analysis to determine exactly which details of their
differing behavior cause the specific issue that you asked about.
Furthermore, I have seen absolutely no evidence of what the VC++ program
is actually doing or how it's performing. I continue to maintain that
its behavior is of little relevance.

At this point it seems to be moot, as well.
One would think so, yes, my understanding of a pooled datasource is
the same, and I simply can't figure out why this happens. I've changed
the previously posted example to use a pooled connection, see below,
but still exactly the same thing happens.
private static JSQLConnectionPoolDataSource m_ds1;

private static void setupdb()
{
m_ds1 = new JSQLConnectionPoolDataSource();
m_ds1.setMinPoolSize(100000);
m_ds1.setMaxPoolSize(1000000);

I see from your other message that you resolved your problem. Do note,
however, the qualification in my previous statement: "if you ensure that
the pool has a reasonable maximum size." We evidently have very
different ideas of "reasonable". There is absolutely no reason
whatsoever that a connection pool would need to accommodate a million
connections. Even the 100000 _minimum_ you specified is much higher
than you ought to even contemplate for a maximum. The maximum _must_ be
significantly less than the number of open connections that your network
stack can accommodate, and if you are connecting back to the local
machine then you may need to cut the maximum in half. I can't imagine
how you would be able to make effective use of a pool of more than about
100-200 connections, and chances are you can't keep even that many busy.

John Bollinger
(e-mail address removed)
 
T

Tage V. Madsen

different ideas of "reasonable". There is absolutely no reason
whatsoever that a connection pool would need to accommodate a million
connections.

No, of course you're right, that's just some panicky test code, I
wanted to be sure that there was enough :)

Thanks again.

-Tage
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,905
Latest member
Kristy_Poole

Latest Threads

Top