FreeTDS Development Group

[Jeff.Goodwin AT fairchildsemi.com]

freetds-bounces AT lists.ibiblio.org wrote on 03/15/2009 06:13:21 PM:

> Jeff.Goodwin AT fairchildsemi.com wrote:
> > 
> > It appears that the change did not have the desired effect:
> > 
> > DB error = 20004,  Severity code = 9
> > DB error msg Read from the server failed
> > OS error # 54
> > OS error msg connection reset by peer
> 
> 
http://h71000.www7.hp.com/doc/82final/6529/6529pro_030.html#app_error_codes
> 
> 54 ECONNRESET    SS$_CONNECFAIL    Connection reset by peer

James,

Thanks for the detailed analysis.  I first must confess to what a dope I 
am.  When the program failed again today with a EINPROGRESS, I felt 
something was amiss.  I realized that although I had rebuilt all my 
executables, I neglected to rebuild my intermediate object file that links 
with main ADA program to provide the Sybase calls.  The ECONNRESET was not 
the result of the changes that Freddy had suggested as I was still running 
the same code.  I really have rebuilt it all and have verified I am 
running the newest changes as the freetds.log now reports updated OpenVMS 
versions of the source code.

This mean that the ECONNRESET is an additional issue that we have only 
seen this one time.  I've listed all the failures of this particular 
program below.

> 
> Hi Jeff, 
> 
> I think the problem originates on the server, or seems to from the
> client's perspective. 
> 
> I was hoping Craig would weigh in, because he's done the most to make
> FreeTDS work under VMS.  You would certainly be one of only a small 
number
> of people using 0.82 with VMS. 
> 
> When I read your post on Friday, I wanted to suggest looking at the 
server
> and the network.  Freddy suggested that select(2) may say the write 
socket
> was ready when it wasn't.  He's right, but as you found there's no fix 
if
> the server's really gone. 
> 
> select(2) and poll(2) can return positive -- meaning a socket is "ready"
> -- for four reasons: 
> 
> 1.  data can be read/written
> 2.  connection is closed
> 3.  accept/connect completed
> 4.  socket error pending
> 
> We know #3 doesn't apply.  #1 is the good case: the i/o succeeds as
> exepected and life goes on.  #2 and #4 are handled when the next i/o
> operation returns an error and sets errno appropriately.  If the 
operation
> returns 0, we emit TDSESEOF, else that the read/write failed.  In your
> first post, you were getting SYBESEOF, which is  #2 of read(2). 
> 
> I think it's a read (not write) failure for two reasons. 
> 
> 1.  tdserror was called during sqlok(), not sqlsend(). 
> 
> 2.  Once you masked it with Frediano's suggested changes, you got a hard
> read error, much as you would from reading from a file after hitting 
EOF. 
> 
> Case #2 results from the peer -- here, the server -- closing the
> connection.  The reader is waiting for the sender to send something, and
> instead is told the other end has disconnnected. 
> 
> For the sake of anyone trying to follow this, let's review.  select(2) 
(or
> poll(2)) returns a count of ready sockets.  In tds_select(), that count
> will only ever be 1 at most.  select(2) says the socket is ready, but by
> "ready" means it was closed by the server.  When recv(2) or read(2)
> returns 0, we know we have an eof condition and say so. 
> 
> This is an exceptional condition.  It's not a network failure: By
> definition "closed by peer" means we received the server's FIN packet. 
> But why?  Normally only clients disconnect. 
> 
> Remember the server isn't supposed to disconnect abruptly and, when it
> does, nothing is going to restore the connection.  It is not just 
resting.
>  The connection is dead, expired, pushing up daisies, an ex-connexion! 
> 
> It's not due to a TDS protocol violation; we know that from the packet 
you
> sent.  I would check the server logs, looking for resource exhaustion or
> other reasons it would close the connection.  Also, the 24-hour thing is
> suspicious.  Maybe some backup script or cleanup utility is closing
> "stale" connections or otherwise resetting the network interface.  Or
> maybe a firewall shuts down connections after a period of inactivity. 
> Keep in mind it might be half-closed. 

I should have supplied more information in my original post.  We had 
examined the server logs and found nothing unusual.  I too thought the 24 
hour thing was suspect.  As you can see below, the stop times and elapsed 
times are all over the place:

OS error # 36 job terminated at 16-MAR-2009 11:13:36.99 Elapsed time:  1 
00:02:23.50 (1411 records)
OS error # 54 job terminated at 15-MAR-2009 02:54:58.96 Elapsed time:  1 
00:23:09.64 (9036 records)
OS error # 36 job terminated at 13-MAR-2009 11:46:47.78 Elapsed time:  0 
04:22:57.17 (5834 records)
OS error # 36 job terminated at 11-MAR-2009 16:16:12.36 Elapsed time:  1 
00:08:52.41 (22292 records)
OS error # 36 job terminated at  4-MAR-2009 17:09:46.15 Elapsed time:  1 
00:57:54.58 (996 records)
OS error # 36 job terminated at  3-MAR-2009 08:40:02.18 Elapsed time:  1 
00:12:44.24 (26091 records
OS error # 36 job terminated at 24-FEB-2009 09:25:44.82 Elapsed time:  1 
00:25:51.75 (5254 records)
OS error # 36 job terminated at 21-FEB-2009 15:24:24.21 Elapsed time:  1 
00:31:41.19 (2876 records)

The last column is the number of successful inserts/updates performed 
before failure.  I also have monitored the process quotas and it doesn't 
seem to be a resource exhaustion issue on the client process side.

This program is being ported from the OpenVMS VAX architecture using the 
commercial Sybase client to the OpenVMS Itanium architecture using 
FreeTDS.  The program is still in test mode.  That is why there is not a 
failure every day.

The client and server are on the same LAN and there are no firewalls 
between them.  I had entertained the idea of putting the server on the 
exact same physical network card to eliminate any 'in between' network 
hardware issues.  After your comments, I'll think we'll proceed with that 
plan.  The systems are on the other side of the world and it will take me 
a couple of days to get the ports moved.

We had also considered there was something on the server that might be 
shutting the processes down.  We hadn't found anything, but after looking 
at the above data again, it still seems to be a probable cause.

We've taken another look at the server logs after the ECONNRESET and there 
is still nothing going on anywhere near the timeframes where we are 
experiencing the failure.

> 
> In looking into this, I noticed a subtle mistake in tds_goodwrite(): it
> should never return TDSESEOF.  For a closed connection, read(2) returns 
0,
> but write(2) returns -1.  No special meaning attaches to write(2)
> returning zero. 
> 
> When write(2) returns zero, tds_goodwrite() returns TDSESEOF.  It should
> simply retry and let the timeout take care of things.  It'll take a 
while,
> sending the data zero bytes at a time, but that's no reason to claim it
> can't be done. 
> 
> The attached patch fixes this.  It won't apply cleanly to your modified
> code, but I hope you'l be able to use it as a guide.  By preventing
> tds_goodwrite() from emitting TDSESEOF, we'll eliminate some ambiguity. 
> 
> One by-the-way suggestion: in your error handler, call strerror(3) 
instead
> of printing out the raw error code.  According to the documentation
> Frediano found, 54 is ECONNRESET; if it actually means something else,
> we're on a wild goose chase. 

I've implemented the changes from the diff file you supplied as well as 
the strerror() change you suggested.

I'll followup this post when something noteworthy occurs.  (Like another 
error or the process making it more than a day+).

> 
> In sum:
> 
> 1.  It looks like the server is disconnecting for reasons unknown. 
> Removing TDSESEOF from tds_goodwrite() will help clarify matters. 
> 
> 2.  It does not look like a network problem. 
> 
> 3.  Server disconnections are abnormal, particularly when SQL Server
> itself doesn't crash. 
> 
> 4.  The culprit would seem to be neither the client, nor the network, 
nor
> SQL Server.  Other server processes and/or a firewall seems likely. 
> 
> HTH. 
> 
> --jkl