Skip to Content.
Sympa Menu

freetds - [freetds] intermittent empty results

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "David Barnwell" <david.barnwell AT well.ox.ac.uk>
  • To: <freetds AT lists.ibiblio.org>
  • Subject: [freetds] intermittent empty results
  • Date: Fri, 25 Jun 2004 19:07:35 -0000

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;

---------------------------------------------------------------------------





Archive powered by MHonArc 2.6.24.

Top of Page