FreeTDS Development Group

[Peter Giorgilli <pgiorgilli AT theage.fairfax.com.au>]

On Friday 09 January 2004 02:33, Kent Scott wrote:
> Was wondering if someone could give me some direction in looking into how
> the timeout on queries work.  Quick background info - running slackware
> 9.1, freetds 0.61.2, and php 4.3.3.
>
> My biggest problem stems from the fact that if a connection is made and
> then lost while trying to run queries (in php) I seem to hang indefinitely
> until the connection comes back. It seems that the function in tds that
> ultimately gets called is goodread(). It looks at tds->timeout to go in to
> some code to be able to return. tds->timeout seems to be set in tds_connect
> by looking at the connect_timeout variable. If connect_timeout is set then
> it assigns connect_info->query_timeout. query_timeout is set in
> tds_set_timeouts().  This function does not seem to be called anywhere
> internally. Here is where I am a little confused, there seems to be a
> connect_info->timeout variable that is set by parsing the config file
> (timeout = 5). What is supposed to be the difference between query_timeout
> and timeout in connect_info? Thanks in advance!
>

My experience with 0.61.2 was that the code does not time-out queries; 
connection time-outs, however, are handled properly.

It's not difficult to exercise the time-out code: connect to a database using 
your favourite command line shell, execute a query to make sure that you 
really are connected, then either pull the network-cable out of the computer 
or -- if you're running Linux with a 2.4 kernel -- add firewall rules like 
the two below to "drop" packets bound for your database server. For example:

1. Log packets bound for your database server:

#iptables --table filter --append OUTPUT --protocol tcp --source YOUR.IP 
--destination SERVER.IP --jump LOG --out-interface YOUR.INTERFACE 
--destination-port SERVER.PORT

2. Drop those same packets (bound for your database server):

#iptables --table filter --append OUTPUT --protocol tcp --source YOUR.IP 
--destination SERVER.IP --jump DROP --out-interface YOUR.INTERFACE 
--destination-port SERVER.PORT

Now, go back to your command line shell, and recall your previous query and 
execute it anew. What you'll find (under 0.61.2) is that the shell just 
hangs. In other words, the library does not time-out the query.

To delete the firewall rules:

1. #iptables --table filter --delete OUTPUT --protocol tcp --source YOUR.IP 
--destination SERVER.IP --jump LOG --out-interface YOUR.INTERFACE 
--destination-port SERVER.PORT

2. #iptables --table filter --delete OUTPUT --protocol tcp --source YOUR.IP 
--destination SERVER.IP --jump DROP --out-interface YOUR.INTERFACE 
--destination-port SERVER.PORT

What I ended up doing to resolve the time-out problem was to alarm certain 
calls to the database such that a SIGALRM is generated when an 
application-defined time-out is reached, which in turn causes the library 
function to fail. In my environment, this was relatively easy to do by 
sub-classing the Perl DBI and overriding certain methods with my own.

I haven't had a chance to test this with the latest release candidate; maybe 
someone else can?

Regards

Peter



*********************************************************************************
The information contained in this e-mail message and any accompanying files 
is or may be confidential.  If you are not the intended recipient, any use, 
dissemination, reliance, forwarding, printing or copying of this e-mail or 
any attached files is unauthorised. This e-mail is subject to copyright. No 
part of it should be reproduced, adapted or communicated without the written 
consent of the copyright owner. If you have received this e-mail in error, 
please advise the sender immediately by return e-mail, or telephone and 
delete all copies. Fairfax does not guarantee the accuracy or completeness of 
any information contained in this e-mail or attached files. Internet 
communications are not secure, therefore Fairfax does not accept legal 
responsibility for the contents of this message or attached files.
*********************************************************************************