Spring With JDBC

[rajeevpariyadathu]

Hi,
I am using Spring with JDBC connection in my project. DB is SQLSERVER, and while on load testing after some time , we got the below exception
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.BindException: Address already in use: connect

Context.xml in Tomcat server , we are giving the below database details ,

<Resource name="jdbc/hbsidb" auth="Container"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLS erverDriver"
url="jdbc:sqlserver://192.168.32.42:1433;databaseName=DB_XML"
username="root1" password="root1" maxActive="200" minIdle="10" maxWait="20000" initialSize="50"
/>

Thanks in advance and waiting for the solution, pls help to solve this problem.

regards,
Rajeev Pariyadathu

[luis.espinal]

What do you get on the database side? This looks more like a database server problem (perhaps a listener trying to bind multiple times to the same IP and port.)

Your code, as client to SQL Server (or any DB server) should have no problems opening multiple, simultaneous sockets to the same DB server IP and port (in your specific case 192.168.32.42:1433).

The error you are getting is usually when something tries to bind multiple times to the same port and IP. This should not be the case for your client code (the one residing on tomcat.) I'm mostly experienced with Oracle, and I haven't touch SQL Server in ages, so I might be wrong.

However, based on my understanding of the java.net.BindException, which is the nested exception you are getting with the com.microsoft.sqlserver.jdbc.SQLServerException, it is most likely that something on the db server side is trying to bind to the same port and IP, causing the exception.

Another thing. I suggest you get into the habit of including the full stack trace of the exception you are getting. A short message like that might not cut it to diagnose the problem.

Can you verify that there are no errors being registered on the server side when you encounter the exception? This is very important.

Can you, more or less with accuracy, describe what load (# of requests) get you to get that exception? Can you determine a load pattern?
Can you post the entire

[luis.espinal]

However, based on my understanding of the java.net.BindException, which is the nested exception you are getting with the com.microsoft.sqlserver.jdbc.SQLServerException, it is most likely that something on the db server side is trying to bind to the same port and IP, causing the exception.

Just to expound on this, and why it will be great if you can discern some load pattern that causes the error, it could very well be that the stress test/load parameters are causing the DB server to fail.

When doing performance/load tests, it is imperative that all components in the system are taking into account. After all, you are not only banging the living guts out of your web application. You are also hitting the tomcat instance that contains your application, the client box (OS and hardware), the network (which usually it's not an issue), and the DB server (both the DB server software, the OS and the hardware.)

So, in addition to checking if any errors occur on the DB side, you need to get some stats at the OS level (using netstat or something). Since it's SQLServer runs on a NT machine, you might want to check if it's not the case that under severe load, the specific NT machine you are using as a DB server is getting flooded with connections in the TIME_WAIT state. That will cause it to run out of available ports designated for incoming db connections.

I ran a google on the exception string you were getting (com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.BindException: Address already in use: connect) and I got this:

http://n2.nabble.com/OpenJPA-opening...-td210000.html

Although not related to Spring, but it discusses a similar type of exception with a probable cause on the DB server side. You might be running on the same issue.

Amit@nabble.com[/email]]It looks like you are not using a connection pool. By not using a connection pool, each time you need a connection, your app will need to spend the overhead of pening the socket connection to the db server.

Your netstat -a shows that all your connections are in TIME_WAIT status, which means the connection has been closed and is waiting for the OS to release the resources. If you are running on Windows, the default ephemeral port rang is between 1024 and 5000 and the default time it takes windows to release the resource from TIME_WAIT status is 4 minutes. So if your application used more then 3976 connections in less then 4 minutes, you will get the exception below.

Some suggestions to fix this:

1) Add a connection pool (check out Apache dbcp). This will reduce the number of connections to the db and speed up your application.

2) Change your windows settings to allow for more ephemeral ports and a quicker close time. This can be done by modifying the following registry settings:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\Tcpip\Parameters:

MaxUserPort = dword:00004e20 (20,000 decimal)
TcpTimedWaitDelay = dword:0000001e (30 decimal)

One last thing to consider is the TCP/IP set ups on the client box where Tomcat is running. I'm not sure about Linux, but on Solaris, you might want to tune those (namely the /dev/tcp tcp_time_wait_interval parameter). Obviously that will be a secondary course of action since you still need to determine that the DB server is performing well.

All in all, this seems more a database server issue than a Spring/Tomcat one.

[justin.nc]

<Resource name="jdbc/hbsidb" auth="Container"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLS erverDriver"
url="jdbc:sqlserver://192.168.32.42:1433;databaseName=DB_XML"
username="root1" password="root1" maxActive="200" minIdle="10" maxWait="20000" initialSize="50"
/>

Can you verify that there are no errors being registered on the server side when you encounter the exception? This is very important.



______________________
Dịch vụ thiết kế nội thất văn phòng luôn mang lại cho bạn một không gian nội thất văn phòng đẹp và sang trọng