Skip to Content.
Sympa Menu

freetds - [freetds] Everything working fine... and then not!

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Alan Smith <alan AT d-sol.co.uk>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] Everything working fine... and then not!
  • Date: Fri, 27 Oct 2006 17:28:34 +0100

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



  • [freetds] Everything working fine... and then not!, Alan Smith, 10/27/2006

Archive powered by MHonArc 2.6.24.

Top of Page