FreeTDS Development Group

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

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;

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