[freetds] Everything working fine... and then not!
Alan Smith
alan at d-sol.co.uk
Fri Oct 27 12:28:34 EDT 2006
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
More information about the FreeTDS
mailing list