socket write error (not a login problem)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

socket write error (not a login problem)

Author

Topic

lebedev
Posting Yak Master

126 Posts

Posted - 2006-06-20 : 14:05:39

I am using SQL Server 2000 on Windows 2003 server and I get sporadic "java.sql.SQLException: I/O Error: Connection reset by peer: socket write error" messages when trying to connect to the server through JDBC.

Now, I don't think that this is a problem with the JDBC driver because I am getting these errors with two different JDBC drivers: jTDS and Microsoft JDBC driver.

Moreover, this error occurs only when more than 5 clients are trying to connect to the server at the same time!

I know that SQL Server MSDE version has the limit on the "Maximum concurrent user connections" set to 8. However, I am using the normal SQL Server installation and set this limit to 0 (unlimited connections).

I am affraid that there is a hidden place in my SQL Server installation where the number of concurrent connections is capped. Can anyone think of where this can be?

I would appreciate any feedback since I already wasted the whole day fighting this issue.

Please see full stack trace below.

Thanks.

Caused by: java.sql.SQLException: I/O Error: Connection reset by peer: socket write error
java.sql.SQLException: I/O Error: Connection reset by peer: socket write error
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:611)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:331)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:178)
at net.sourceforge.jtds.jdbcx.JtdsDataSource.getConnection(JtdsDataSource.java:281)
at net.sourceforge.jtds.jdbcx.JtdsDataSource.getConnection(JtdsDataSource.java:133)
at com.nextpage.server.domain.persistence.jdbc.JDBCConnectionPool.getConnection(JDBCConnectionPool.java:50)
at com.nextpage.server.domain.persistence.jdbc.JDBCPersistenceService.allocateThreadLocalConnection(JDBCPersistenceService.java:205)
at com.nextpage.server.domain.persistence.jdbc.JDBCPersistenceService.beginTransaction(JDBCPersistenceService.java:89)
at com.nextpage.server.domain.persistence.jdbc.SQLQueryExecutor.execute(SQLQueryExecutor.java:98)
at com.nextpage.server.domain.persistence.jdbc.JDBCRepository.find(JDBCRepository.java:420)
at com.nextpage.server.domain.persistence.jdbc.JDBCRepository.find(JDBCRepository.java:113)
at com.nextpage.common.domain.dto.DTORepositoryImpl.find(DTORepositoryImpl.java:264)
at com.nextpage.common.domain.entity.EntityRepositoryImpl.find(EntityRepositoryImpl.java:135)
at com.nextpage.server.domain.impl.ServerSyncHostServiceImpl.findLocalSyncHostNullIfNotThere(ServerSyncHostServiceImpl.java:51)
at com.nextpage.server.domain.impl.ServerSyncHostServiceImpl.findLocalSyncHost(ServerSyncHostServiceImpl.java:36)
at com.nextpage.server.application.impl.AppServerSyncHostServiceImpl.findLocalSyncHost(AppServerSyncHostServiceImpl.java:25)
at com.nextpage.tester.domain.retention.server.TServerEntityFactory.create(TServerEntityFactory.java:119)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.nextpage.tester.domain.ATEntityFactory.invokeMethod(ATEntityFactory.java:294)
at com.nextpage.tester.domain.ATEntityFactory.create(ATEntityFactory.java:180)
at com.nextpage.tester.application.actions.TActionCreate.register(TActionCreate.java:194)
at com.nextpage.tester.application.actions.TActionCreate.createDomain(TActionCreate.java:122)
at com.nextpage.tester.application.actions.TActionCreate.simulate(TActionCreate.java:66)
at com.nextpage.tester.application.actions.ATAction.call(ATAction.java:111)
at com.nextpage.tester.application.actions.TActionSequence.simulate(TActionSequence.java:37)
at com.nextpage.tester.application.actions.ATAction.call(ATAction.java:104)
at com.nextpage.tester.application.actions.ATAction.call(ATAction.java:1)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.net.SocketException: Connection reset by peer: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.DataOutputStream.write(DataOutputStream.java:90)
at net.sourceforge.jtds.jdbc.SharedSocket.sendNetPacket(SharedSocket.java:656)
at net.sourceforge.jtds.jdbc.RequestStream.putPacket(RequestStream.java:560)
at net.sourceforge.jtds.jdbc.RequestStream.flush(RequestStream.java:508)
at net.sourceforge.jtds.jdbc.TdsCore.sendMSLoginPkt(TdsCore.java:2067)
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:584)
... 35 more

lebedev
Posting Yak Master

126 Posts

Posted - 2006-06-21 : 13:56:04

After looking into this problem some more I found that I get "socket write error" if I try to open more than 6-7 SQL Server connections at the SAME time (plus or minus one millisecond). Here is the summary:

1. If I begin open 6 connections extactly the same time (plus or minus one millisecond), then SQL server throws "socket write error".

2. As I add more connections, more of them throw this error.

3. If I open each connection after waiting for a random number (< 1000) of milliseconds, then this error does not show up untill I reach more than 35 clients.

All this research makes me think that there is a problem with the number of connections SQL Server can OPEN at the same time.

Has anybody else encountered this problem?

Is there a way to configure SQL Server not to deny connections and instead block the connection request until it can be granted?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-21 : 14:04:10

SQL Server can handles thousands of simultaneous connections. We typically have around 500 users connected per SQL instance.

I'd suggest running SQL Profiler on the database server to determine if your connection is even making it to the SQL Server. If it isn't, then the problem isn't within SQL Server. SQL Profiler will show the connection being made if it makes it there.

Tara Kizer
aka tduggan

lebedev
Posting Yak Master

126 Posts

Posted - 2006-06-21 : 14:19:10

tkizer, it is not the number of active concurrent connections that causes the problem. It's the number of connections being opened at the SAME time. I can open tons and tons of connections as long as I don't open them at the same time.

lebedev
Posting Yak Master

126 Posts

Posted - 2006-06-21 : 14:20:44

I just found this bug fix on msdn: http://support.microsoft.com/kb/295029/en-us
However, I have all service packs applied and I am still having this problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-21 : 14:26:10

We've still never had that problem and we've got them connecting at the same time.

Could you post your connection string (minus password if it's in there)? Also, is your server listed here:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

If not, adding an alias might help. It's possible you aren't using TCP/IP to connect. We can force that in the connection string or alias. I prefer to force it in the alias since an alias typically helps with connecting problems anyway.

Tara Kizer
aka tduggan

lebedev
Posting Yak Master

126 Posts

Posted - 2006-06-21 : 15:03:58

My JDBC URL is: jdbc:jtds:sqlserver://sql-devserv2:1433/test_db;user=sa;
The server is not listed in that registry key.
I also want to make clear that I can establish connections to the server, just not when I do this concurrently from more than 6 clients.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-21 : 15:14:38

I won't comment on the sa userid being used as I'm sure you already know the issue there.

Add the following string value to the key I mentioned:

Name: sql-devserv2
Data: DBMSSOCN,sql-devserv2,1433

That will force your connection to use TCP/IP. I went down this route as I read something about SPX getting that error. If it doesn't work, go ahead and delete it. Although I'd recommend keeping it there.

So you've got service pack 4 for SQL Server 2000 installed on sql-devserv2?

Tara Kizer
aka tduggan

lebedev
Posting Yak Master

126 Posts

Posted - 2006-06-21 : 15:31:13

I am not sure what known issues you are referring to with regards to the sa username, but I know we gotta change it :)
Adding the key did not fix the problem.
I talked to my IT guy and he said that all requests to the server go through the firewall which only lets TCP/IP connections through.
I have SQl Server SP4 installed on that server.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-21 : 15:41:40

I'm outta ideas. I'd suggest contacting MS for assistance with this problem if no one else is able to help you.

Here's some information that I found while googling your problem:

http://forum.java.sun.com/thread.jspa?threadID=294609&tstart=0
This basically means that a network error occurred while the client was receiving data from the server. But what is really happening is that the server actually accepts the connection, processes the request, and sends a reply to the client. However, when the server closes the socket, the client believes that the connection has been terminated abnormally because the socket implementation sends a TCP reset segment telling the client to throw away the data and report an error.

Sometimes, this problem is caused by not properly closing the input/output streams and the socket connection. Make sure you close the input/output streams and socket connection properly. If everything is closed properly, however, and the problem persists, you can work around it by adding Thread.sleep(1000) before closing the streams and the socket. This technique, however, is not reliable and may not work on all systems.

.....

http://www.websina.com/bugzero/errors/http-connection-reset.html
This exception is normally harmless. It does not seem possible to trap this exception with J2SE.

.....

http://archives.postgresql.org/pgsql-jdbc/2004-12/msg00007.php
Check for firewalls between the appserver and the database server. The error is a TCP-level connection error that means that the peer (the database server) unexpectedly disappeared on us, and we didn't see an orderly shutdown of the connection. If it always happens after a certain connection length, or after the connection has been idle for a certain period, it's likely that there is a stateful firewall in the way that is dropping the connection after that period.

Tara Kizer
aka tduggan

lebedev
Posting Yak Master

126 Posts

Posted - 2006-06-21 : 15:48:46

After reading through that bug fix I posted earlier I decided to install SQL Server 2000 or Windows Server 2000 (not 2003) box. I also put this machine inside the firewall.

Running against this 2000/2000 inside firewall configuration did not produce the old "socket write error"! I can successfully open up to 35 or so connections at the same time. However, with a larger number of connections I get a different error:

Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect
java.sql.SQLException: Network error IOException: Connection refused: connect
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:372)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:178)
at net.sourceforge.jtds.jdbcx.JtdsDataSource.getConnection(JtdsDataSource.java:281)
at net.sourceforge.jtds.jdbcx.JtdsDataSource.getConnection(JtdsDataSource.java:133)
at com.nextpage.server.domain.persistence.jdbc.JDBCConnectionPool.getConnection(JDBCConnectionPool.java:50)
at com.nextpage.server.domain.persistence.jdbc.JDBCPersistenceService.allocateThreadLocalConnection(JDBCPersistenceService.java:205)
at com.nextpage.server.domain.persistence.jdbc.JDBCPersistenceService.beginTransaction(JDBCPersistenceService.java:89)
at com.nextpage.tester.application.TMain$TPooler$1.call(TMain.java:461)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
at java.net.Socket.connect(Socket.java:507)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:289)
at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:250)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:297)
... 13 more

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-21 : 15:54:23

I don't think that's a SQL Server issue. The error java.net.ConnectException: Connection refused: connect is not a SQL Server error. Java is potentially hiding the error and barfing with this one though. But I did some googling on your new error, and it looks like it's a network issue.

If we had the specific SQL Server error (if in fact SQL Server was causing it), then it'd be easier for us to assist you.

We've got a few Java applications here as well. They are the hardest to troubleshoot when the developers are pointing to SQL Server as the culprit. The reason being is that they can't give us the SQL Server error that they are getting as Java isn't giving that information to them.

Tara Kizer
aka tduggan

lebedev
Posting Yak Master

126 Posts

Posted - 2006-06-21 : 16:24:27

Is there a way I can capture this error on the SQL Server side? How should I configure SQL Server logging to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-21 : 16:26:44

If you are reaching SQL Server, then you will see the connection inside SQL Profiler. SQL Profiler is a tool that you'll get if you install the SQL Client Tools. If you have Query Analyzer or Enterprise Manager, then you have this tool. I'd suggest running the tool on a client maching pointing at your database server to minimize the impact that this tool does.

Tara Kizer
aka tduggan

computabloke
Starting Member

1 Post

Posted - 2007-03-06 : 22:18:12

The easiest way to avoid these problems is to use a PooledConnection and a DataSource implementation that supports a TestOnBorrow concept (eg. Apache Tomcat's DBCP BasicDataSource).

On the DataSource use:
setTestOnBorrow(true);
setValidationQuery("SELECT 1");

This will avoid the "socket write error" such as commonly occurs with those long-term/idle connections that were abnormally closed for one reason or another, by ensuring that every connection borrowed from the pool is connectable before running any queries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources