FreeTDS Development Group

["David Barnwell" <david.barnwell AT well.ox.ac.uk>]

Hi Bill,

I ran a trace from the SQL Profiler as you suggested. I couldn't find a way
to save the trace to a text file, but looking at the output on the screen,
there's a clear difference in the error case. An extra SQL statement is
executed just before my statement:

SELECT statman([assay description],@PSTATMAN) FROM (SELECT TOP 100 PERCENT
[assay description] FROM [dbo].[assay]
WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [assay
description]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)

This statement and the data error both go away if I first update statistics
on the table.

I can reproduce this error with just two rows of dummy data:

drop table assay
create table assay (
 assay varchar(50) not null ,
 [assay description] varchar (150) ,
)
insert into assay values ('X123', 'yyyyy')
insert into assay values ('X456', 'yyyyy')
grant all on assay to xxxxx

-- Adding this line clears the error:
create statistics assay_stats on dbo.assay([assay description])

I assume that the reason for the intermittent failures on our web site was
that SQL Server updated the statistics on this table from time to time and
caused the extra return. Fortunately, the error case is very consistent: I
receive one row with one column containing text "0". As a workround I've
added some Perl code to detect this case and retry the SQL. This appears to
work fine.

Thanks for your help!

David.

PS. I also looked at the Perl prepare .. execute .. fetchrow_array
statements. A single call to $dbh->selectall_arrayref has just the same
effect.


----- Original Message ----- 
From: "Thompson, Bill D (London)" <bill_d_thompson AT ml.com>
To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
Sent: Monday, June 28, 2004 2:22 PM
Subject: RE: [freetds] intermittent empty results


Hi David,

some other thoughts on this...

The RETURNSTATUS token, as you may know, is reporting the return status of a
stored procedure.
On the face of it, getting this back from the server is counter-intuitive as
you haven't exec'd a stored procedure...

A couple of possibilities arise.
The first is that FreeTDS is issuing a stored procedure call to the server.
We do this sometimes, as much functionality on SQL server is accessible only
through some (undocumented) stored procedures.
"prepare" functionality - SQL with placeholders and parameters - is one
occasion when we do this, hence my previous reply.

The second possibility is that the Server is internally executing a stored
procedure as it processes your statement, and sending the return status to
you.
We have seen this behaviour before, and it might well upset an application
that was not expecting to receive this.

One way of finding out for sure what is going on would be to capture a SQL
server profiler trace for your test scenario.
That would tell us from the servers point of view everything that's
happening with your session.

Are you familiar with running SQL server profiler traces ?

Bill

> -----Original Message-----
> From: David Barnwell [SMTP:david.barnwell AT well.ox.ac.uk]
> Sent: 25 June 2004 20:08
> To: freetds AT lists.ibiblio.org
> Subject: [freetds] intermittent empty results
>
> Hi,
>
> We have been using freetds successfully for four months on a website that
> displays medical research data. The database server runs SQL Server 2000,
> and the webserver (and database client) is Linux.
>
> However, the website has an intermittent problem: occasionally, it
> incorrectly returns an empty data set. It usually returns the correct data
> if the user clicks again.
>
> After a lot of investigation, I have managed to reproduce this problem
> consistently with a simple perl script that prints one column from a table
> using the following SQL:
>
>     SELECT assay
>     FROM xxxxxx.xxxxx.assay
>     where [assay description] is not null
>     order by 1
>
> The script seems to be upset by certain server activity. If I drop and
> recreate my table on the server, then the first time I run the script on
> the
> client I just get one line containing "0". Thereafter, it's OK.
>
> The problem occurs whether I call the script from CGI or from the command
> line. It occurs with TDSVER = 4.2, 7.0 and 8.0. It still occurs if I
> reduce
> the select to a single line (using SELECT top 1).
>
> This is what the TDS log shows. In the error case, the server always
> returns
> an extra 5 bytes (hex 79 00 00 00 00) at the start of the data stream. The
> log then shows:
>
>     processing result tokens.  marker is  79(RETURNSTATUS)
>     ct_results() process_result_tokens returned 1 (type 4043)
>     generating return status row. type = 56(int), varint_size 0
>
> Shortly after that, a ct_cancel is sent and we get repeated calls of
> "tds_process_default_tokens" until the incoming data is exhausted, rather
> than repeated "processing row tokens" that we see in the in the normal
> case.
>
> In the error case, the log ends with:
>
>   tds_process_end: more_results = 0
>     was_cancelled = 1
>     error = 0
>     done_count_valid = 0
>   tds_process_end() state set to TDS_IDLE
>   ct_close()
>   ct_con_drop()
>   ct_cmd_drop()
>
> Any ideas or help would be gratefully received!
>
> Many thanks,
> David Barnwell
>
> Database Developer/Systems Administrator
> Wellcome Trust Centre for Human Genetics
> University of Oxford, Roosevelt Drive, Oxford OX3 7BN, UK
>
> --------------------------------------------------------------------------
> -
>
> (1) Server software:
> Windows 2000 5.00.2195 (Service Pack 4)
> Microsoft SQL Server  2000 - 8.00.818 (Intel X86)
>
> (2) Client software:
> Red Hat Enterprise Linux 3
> perl 5.8.0
> Apache 2.0.46
> Apache-DBI-0.94
> DBD-Sybase-1.02
> freetds-0.62.1 (loaded from freetds-0.62.1-1.i586.rpm)
>
> (3) The server SQL that triggers the problem:
> drop table assay
> select * into assay from copy_assay
> grant all on assay to xxxxxx
>
> (4) The perl script that shows the problem:
> #!/usr/bin/perl -w
> use strict;
> use DBI;
>
> $ENV{'TDSVER'} = '4.2';
> $ENV{'TDSDUMP'} = "/tmp/dbtest.log";
> $ENV{'TDSDUMPCONFIG'} = "/tmp/dbtest.config";
>
> my $HOST     = 'xxxxxx.xxx.xxx';
> my $USER     = 'xxxxxx';
> my $PASSWORD = 'xxxxxxxxxxx';
>
> my $dsn = "DBI:Sybase:server=$HOST" ;
>
> my $sql = "
>   SELECT top 1 assay
>   FROM xxxxxx.xxxxx.assay
>   where [assay description] is not null
>   order by 1
> ";
>
> my $dbh = DBI->connect($dsn, $USER, $PASSWORD) or die 'connect';
> my $sth = $dbh->prepare($sql);
> $sth->execute;
>
> while (my @row = $sth->fetchrow_array) {
>   print join "  ", @row;
>   print "\n";
> }
>
> $sth->finish;
> $dbh->disconnect;
>
> --------------------------------------------------------------------------
> -
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

============================================================================
==

If you are not an intended recipient of this e-mail, please notify
the sender, delete it and do not read, act upon, print, disclose,
copy, retain or redistribute it.

Click here for important additional terms relating to this e-mail.
     <http://www.ml.com/email_terms/>

============================================================================
==

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