FreeTDS Development Group

[Alan Smith <alan AT d-sol.co.uk>]

Hi,

I am running some perl cgi scripts that query a MySQL database. A few of the 
scripts also query a remote MSSQL 2000 server that is connected to the apache 
server via VPN.

The setup is as follows:

unixODBC-2.2.11
freetds-0.63
DBD-SyBase-1.04
DBI-1.47
DBD-ODBC-1.13
perl-5.8.7 (unthreaded)
Apache-2.0.53
Slackware 10.1

Yesterday I was writing a new script which which should have fetched from the 
MSSQL database 671 records with 8 fields from a table with 6300 odd records 
and 30 odd fields in total. Normally this would take a couple of seconds but 
it timed out. I ran another script that fetches data from the same MSSQL 
database that up until now has been working fine and it just hung.

The database server was pingable, but I had the same result with the unixODBC 
test utility and isql. I don't recall making any system changes to the web 
server so I'm thinking that perhaps the config on the MSSQL server has 
changed. However, booting my laptop into Windows and running the same SQL 
query as a passthrough query in MS Access and it worked fine, the laptop is 
on the same LAN as the webserver.

The queries to the MYSQL database (the MySQL database runs on the web server) 
work fine.

The SQL query is:

SELECT 
customer,analysis_codes5,name,address1,address2,address3,address4,despatch_held
 FROM scheme.slcustm
WHERE invoice_customer='xxxx' AND customer<>''xxxx' AND despatch_held='N'

Using $sth->fetchrow_array() here is the output from 
DBI->trace(4,'/tmp/dbi.log'):

    DBI 1.47-nothread default trace level set to 0x0/4 (pid 1065)
    -> prepare for DBD::ODBC::db (DBI::db=HASH(0x8415620)~0x844dec8 'SELECT 
customer,analysis_codes5,name,address1,address2,address3,address4,despatch_held
 FROM scheme.slcustm
    WHERE invoice_customer='xxxx' AND customer<>'xxxx' AND despatch_held='N'')
    New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x844dec8), id=)
    dbih_setup_handle(DBI::st=HASH(0x844dfac)=>DBI::st=HASH(0x8415464), 
DBD::ODBC::st, 844dfb8, Null!)
    dbih_make_com(DBI::db=HASH(0x844dec8), 84512f0, DBD::ODBC::st, 212, 0) 
thr#0
    SQLPrepare returned 0

    dbd_st_prepare'd sql f138846960, ExecDirect=0
 SELECT 
customer,analysis_codes5,name,address1,address2,address3,address4,despatch_held
 FROM scheme.slcustm
    WHERE invoice_customer='xxxx' AND customer<>'xxxx' AND despatch_held='N'
    <- prepare= DBI::st=HASH(0x844dfac) at b2b_co_admin.pl line 172 via 
/usr/local/lib/perl5/site_perl/5.8.7/i686-linux/ModPerl/RegistryCooker.pm 
line 203
    -> execute for DBD::ODBC::st (DBI::st=HASH(0x844dfac)~0x8415464)
    dbd_st_execute (outparams = 0)...
    dbd_st_execute (for hstmt 138846960 before)...
dbd_error: err_rc=0 rc=0 s/d/e: 138846960/138734672/138507800
dbd_error: err_rc=0 rc=0 s/d/e: 0/138734672/138507800
dbd_error: err_rc=0 rc=0 s/d/e: 0/0/138507800
    dbd_describe sql 138846960: num_fields=8
      col  1: CHAR     (1) len=  8 disp=  9, prec=  8 scale=0
      col  2: CHAR     (1) len= 10 disp= 11, prec= 10 scale=0
      col  3: CHAR     (1) len= 32 disp= 33, prec= 32 scale=0
      col  4: CHAR     (1) len= 32 disp= 33, prec= 32 scale=0
      col  5: CHAR     (1) len= 32 disp= 33, prec= 32 scale=0
      col  6: CHAR     (1) len= 32 disp= 33, prec= 32 scale=0
      col  7: CHAR     (1) len= 32 disp= 33, prec= 32 scale=0
      col  8: CHAR     (1) len=  1 disp=  2, prec=  1 scale=0
      col  1: 'customer' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=9, (dp = 0, 
cp = 0)
      col  2: 'analysis_codes5' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=11, 
(dp = 12, cp = 9)
      col  3: 'name' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=33, (dp = 24, cp 
= 25)
      col  4: 'address1' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=33, (dp = 60, 
cp = 30)
      col  5: 'address2' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=33, (dp = 96, 
cp = 39)
      col  6: 'address3' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=33, (dp = 
132, cp = 48)
      col  7: 'address4' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=33, (dp = 
168, cp = 57)
      col  8: 'despatch_held' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=2, (dp = 
204, cp = 66)
dbd_describe failed, dbd_st_execute #2...!
    <- execute= -1 at b2b_co_admin.pl line 174 via 
/usr/local/lib/perl5/site_perl/5.8.7/i686-linux/ModPerl/RegistryCooker.pm 
line 203
    -> fetchrow_array for DBD::ODBC::st (DBI::st=HASH(0x844dfac)~0x8415464)
       SQLFetch rc 0
    dbih_setup_fbav for 8 fields => 0x8415380
fetch num_fields=8
fetch col#0 customer datalen=8 displ=9
fetch col#1 analysis_codes5 datalen=10 displ=11
fetch col#2 name datalen=32 displ=33
fetch col#3 address1 datalen=32 displ=33
fetch col#4 address2 datalen=32 displ=33
fetch col#5 address3 datalen=32 displ=33
fetch col#6 address4 datalen=32 displ=33
fetch col#7 despatch_held datalen=1 displ=2
    <- fetchrow_array= ( 'xxxx' 'None' 'xxx xxxx xxxx' 'xxxx WIMBORNE ROAD' 
'KINSON,  BOURNEMOUTH' 'DORSET' 'xxx xxx' 'N' ) [8 items] row1 at 
b2b_co_admin.pl line 176 via 
/usr/local/lib/perl5/site_perl/5.8.7/i686-linux/ModPerl/RegistryCooker.pm 
line 203

It takes about 20 seconds to get this far, then it's real slow, the rest of 
the records come down slowly but the web page times out. If I reduce the 
number of fields fetched to 1 it fetches the records more quickly but it is 
still very slow.

Have you encountered this before? The only thing that stands out to me is the 
line:

dbd_describe failed, dbd_st_execute #2...!

This setup has been working fine since April and I can't think what I may 
have changed on the web server, there have been no network config changes 
either. I've rebooted the servers involved and the vpn routers.

Regards,

Alan Smith