FreeTDS Development Group

["Lowden, James K" <james.k.lowden AT alliancebernstein.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.  

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.


--jkl

-----------------------------------------
The information contained in this transmission may be privileged and
confidential and is intended only for the use of the person(s) named
above. If you are not the intended recipient, or an employee or agent 
responsible
for delivering this message to the intended recipient, any review, 
dissemination,
distribution or duplication of this communication is strictly prohibited. If 
you are
not the intended recipient, please contact the sender immediately by reply 
e-mail
and destroy all copies of the original message. Please note that we do not 
accept
account orders and/or instructions by e-mail, and therefore will not be 
responsible
for carrying out such orders and/or instructions.  If you, as the intended 
recipient
of this message, the purpose of which is to inform and update our clients, 
prospects
and consultants of developments relating to our services and products, would 
not
like to receive further e-mail correspondence from the sender, please "reply" 
to the
sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New 
York,
NY 10105.