FreeTDS Development Group

["David Horman" <david.horman AT odf-limited.com>]

Thanks for your help - it seems that the WAITFOR command was exactly
what I needed to finally get to the bottom of this mystery. It turned
out that while I had set mssql.timeout to 600 in php.ini, I had
forgotten to uncomment the line! After doing so, and writing a quick PHP
script to query the server and WAITFOR 12 minutes, the script gave the
"Query failed" error - after exactly 10 minutes. Changing mssql.timeout
to 240s caused "Query failed" after exactly 4 minutes.

So it seems this was all down to PHP (well, and having such an old
server that it takes forever to run queries) after all.

Thanks again for your help.

David

-----Original Message-----
From: FreeTDS [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of
Frediano Ziglio
Sent: 20 March 2015 10:31
To: FreeTDS Development Group
Subject: Re: [freetds] Occasional "Query failed" with LAMP to MSSQL 2000
server on intensive queries

2015-03-19 15:47 GMT+00:00 David Horman <david.horman AT odf-limited.com>:
> Hi,
>
>
>
> Apologies in advance if this is a bit vague - I'm not expecting anyone

> to be able to figure out what's going on from the following 
> description, I'm really looking more for advice on how to investigate
further.
>
>
>
> For the last few years I've been happily using FreeTDS to query an 
> ageing SQL Server 2000 server using PHP scripts running on a Centos 6 
> server. Recently, though, the amount of data I've been working with, 
> and the number of complex queries, has increased to the point where I 
> sometimes see long-running, intensive queries failing after 
> approximately (but this is not a firm measurement) a minute with the 
> following output:
>
>
>
> PHP Warning:  mssql_query(): Query failed in [script_name] on line 131
>
> Warning: mssql_query(): Query failed in [script_name] on line 131
>
>
>
> Then, it seems, any further queries attempted shortly after in the 
> same script fail with, for example:
>
>
>
> Warning: mssql_query(): Unable to set query in [script_name] on line 
> 162
>
> PHP Warning:  mssql_query(): Unable to set query in [script_name] on 
> line 162
>
>
>
> This seems to occur more often if the server is particularly busy, 
> such that I have tried to carefully time my cronjobs not to overlap at
night.
> I've never seen a large query fail when I run it directly in SQL 
> Server Management Studio Express.
>
>
>
> In some cases I can get around the issue, or at least alleviate its 
> frequency, by breaking large queries into smaller ones covering 
> different ranges of data, but this isn't ideal. I've tried changing 
> FreeTDS's timeout and connection timeout values, but to no avail. I 
> also recently increased mssql.timeout to 600 seconds in php.ini, but 
> this made no difference.
>
>
>
> freetds.conf (minus comments) looks like this:
>
>
>
> [global]
>
>   timeout = 240
>
>   connect timeout = 30
>
>   text size = 64512
>
>
>
> [server_name]
>
>   host = 192.x.x.x
>
>   port = 1433
>
>   tds version = 7.1
>
>   client charset = UTF-8
>
>
>
> I forget the reason for setting client charset to UTF-8 but I think it

> was a good one...
>
>
>
> Could anyone advise me on what I can do to get more information on 
> what's going wrong? I couldn't find anything unusual logged on the SQL

> server itself, but again I'm somewhat in the dark as to where to look.
>
>
>
> Thanks in advance,
>
>
>
> David
>

Which FreeTDS version are you using?

Configuration looks fine. Just to try if there is any timeout problem
(could be due even to some firewall) try the WAITFOR statement, see
https://msdn.microsoft.com/en-us/library/ms187331.aspx. This statement
just wait so beside the connection does not occupy the server/network.

Another problem could be lock contention. If there are too much locking
server timeout automatically to prevent deadlock. I don't remember but
there should be a setting for this timeout (on the database server).

What mssql_get_last_message say? As database server is already busy I'd
put some extra logging on the web server to see which kind of errors
happen.

Regards,
  Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds