FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

> 
> > From: Godefroid Chapelle
> > Sent: Wednesday, December 06, 2006 4:22 AM
> > 
> > if ... the network connection to the database server gets lost,
> > the following queries do hang forever without complaining.
> > 
> > I have looked both at FreeTDS and pymssql code to find out if there
> > was any way to setup the chain so that we could be warned by FreeTDS
> > that a network error happened (instead of hanging).  
> 
> I have studied this question carefully and come to a simple 
> conclusion:
> Not yet.  
> 
> A db-lib program (and I don't know if pymssql is one) sets its query
> timeout with dbsettime().  It sets its error handler with 
> dberrhandle().
> 
> 
> Whenever db-lib begins to wait for a response from the server, it sets
> that timeout.  When it expires, it calls  the error handler with
> SYBETIME.  The handler can choose to return INT_CONTINUE, 
> telling db-lib
> to wait for another timeout period.  Or the handler can 
> choose to return
> INT_CANCEL, telling db-lib to give up.  Both vendors say that 
> INT_CANCEL
> causes the connection to be broken.  
> 
> What src/tds/net.c::tds_goodread() should do on receipt of INT_CANCEL
> from the error handler:
> 
> 1.  Close the socket.
> 2.  Mark the connection DEAD.
> 3.  Return failure.  
> 

IMO this is not the purpose of INT_CANCEL. INT_CANCEL is meant to cancel
query, not to close connection. The problem is that tcp stack cannot
safely detect some network problem.... assuming that a given connection
just stop (all packets lost but no errors) how to detect it? There are
some possibility:
- send some keep alive packet. For tcp connections this can be
represented by an empty data, data which is aknowledged. If no
aknowledge this means some problems in connection. However how to send
this packet? You cannot send(fd, "", 0, 0) (which is ignored) you should
enable SO_KEEPALIVE but there is no portable way to set a given
keep-alive timeout. We could also use socket timeouts (SO_RCVTIMEO) but
still not so portable...
- add a "cancel timeout", we sent cancel and server should reply ASAP.
This do not correct detect case where server is too busy and cannot
reply but is portable.

> What src/tds/net.c::tds_goodread() does on receipt of INT_CANCEL:
> 
> 1.  Defeat the timeout, in src/tds/query.c::tds_send_cancel()
> 2.  Send a cancel packet. 
> 3.  Wait for an acknowledgement from the server until tcp/ip 
> times out.
> 
> 
> Sometimes the OS may return an error when the cancel packet 
> is sent.  If
> not, though, #3 may take a long time, as in hours, while 
> tcp/ip retries
> the packets.  If the network is restored in the meanwhile, the
> connection will be OK when control is returned to the 
> application.  But
> probably the user gave up and killed it first.   
> 
> Besides the error handler, the application is supposed to have another
> function it can use for timeout purposes: dbdataready().  
> Unfortunately,
> it's not implemented in FreeTDS.  
> 
> Now that I understand the problem (I believe), it's possible 
> to fix it.
> Please don't hold your breath, though.  It will take me 
> awhile to do it
> unless someone sends me a patch.  And then the Python folks 
> have to make
> sure their code takes advantage of our improvement.  And they 
> have to be
> using db-lib, because I'm not sure how ct-lib and ODBC will 
> be affected.
> 

ODBC do not have a setting for this stuff.. only connection and query
timeout.

Frediano