Skip to Content.
Sympa Menu

freetds - [freetds] Problem with long queries - on one MSSQL server

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Daryl Jones <daryl AT tcomeng.com>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] Problem with long queries - on one MSSQL server
  • Date: Fri, 21 Feb 2014 20:08:49 -0800

I have a PHP app that connects to one of several MSSQL servers
depending on the user's choice. Short queries of less than about 600
bytes work fine on all of the SQL servers. Long queries (more than
about 600 bytes) fail when submitted to one of the MSSQL servers from
the production PHP server. The same query works ok when submitted
from my dev machine which is a Mac using XAMPP. The production PHP
server has CentOS 6.5 with FreeTDS 0.91 built from source. I've also
tried FreeTDS-dev-0.92.377 on the production server with the same
result.

FreeTDS returns 20003: "Adaptive Server connection timed out" error
when the long query is submitted from the CentOS box to the problmatic
MSSQL server.

Do you think this is a FreeTDS issue? If not, can anyone offer any
suggestions on where to look for the problem?

Here's an excerpt from the log file.

19:44:12.359425 11900 (token.c:1924):tds_process_end() state set to TDS_IDLE
19:44:12.359432 11900 (util.c:159):Changed query state from READING to IDLE
19:44:12.359440 11900 (util.c:99):logic error: cannot change query
state from IDLE to PENDING
19:44:12.359448 11900 (util.c:159):Changed query state from IDLE to PENDING
19:44:12.359457 11900 (dblib.c:4681):dbsqlok() end status is SUCCEED
19:44:12.359465 11900 (dblib.c:4692):dbsqlok() end status was success
19:44:12.359472 11900 (dblib.c:1663):dbresults(0x7f2f9c163800)
19:44:12.359480 11900 (dblib.c:1668):dbresults: dbresults_state is 5
(_DB_RES_SUCCEED)
19:44:12.359488 11900 (dblib.c:1653):dbresults returning 1 (SUCCEED)
19:44:12.359496 11900 (dblib.c:5810):dbcanquery(0x7f2f9c163800)
19:44:12.359504 11900 (token.c:525):tds_process_tokens(0x7f2f9c1642a0,
0x7f2fc0df75cc, (nil), 0x108)
19:44:12.359514 11900 (token.c:528):tds_process_tokens() state is COMPLETED
19:44:12.359575 11900 (dblib.c:3981):dbsettime(60)
19:44:12.359600 11900 (dblib.c:1311):dbcmd(0x7f2f9c163800, SELECT TOP
(2000) RIMS.dbo.VINFO.CASEN as CASEN,RIMS.dbo.VINFO.ITEXT as
ITEXT,RIMS.dbo.VINFO.LICENSE as LICENSE,CONVERT(VARCHAR,
RIMS.dbo.VINFO.DATEREC, 101) AS DATEREC,RIMS.dbo.VINFO.MAKE as
MAKE,RIMS.dbo.VINFO.MODEL as MODEL FROM RIMS.dbo.VINFO INNER JOIN
CASES ON RIMS.dbo.VINFO.CASEN = RIMS.dbo.CASES.CASEN WHERE
(RIMS.dbo.VINFO.AGCODE = 'BPD') AND (RIMS.dbo.VINFO.ITEXT LIKE
'%10851%') AND (RIMS.dbo.VINFO.IDATE BETWEEN DATEADD(day, -90,
GETDATE()) AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) OR
(RIMS.dbo.VINFO.AGCODE = 'BPD') AND (RIMS.dbo.VINFO.ITEXT LIKE '%Rec
Veh%') AND (RIMS.dbo.VINFO.IDATE BETWEEN DATEADD(day, -90, GETDATE())
AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) ORDER BY
RIMS.dbo.VINFO.CASEN DESC)
19:44:12.359612 11900 (dblib.c:1317):dbcmd() bufsz = 11
19:44:12.359621 11900 (dblib.c:5837):dbfreebuf(0x7f2f9c163800)
19:44:12.359631 11900 (dblib.c:1367):dbsqlexec(0x7f2f9c163800)
19:44:12.359639 11900 (dblib.c:6809):dbsqlsend(0x7f2f9c163800)
19:44:12.359648 11900 (mem.c:598):tds_free_all_results()
19:44:12.359656 11900 (util.c:159):Changed query state from IDLE to QUERYING
19:44:12.359664 11900 (write.c:137):tds_put_string converting 722
bytes of "SELECT TOP (2000) RIMS.dbo.VINFO.CASEN as
CASEN,RIMS.dbo.VINFO.ITEXT as ITEXT,RIMS.dbo.VINFO.LICENSE as
LICENSE,CONVERT(VARCHAR, RIMS.dbo.VINFO.DATEREC, 101) AS
DATEREC,RIMS.dbo.VINFO.MAKE as MAKE,RIMS.dbo.VINFO.MODEL as MODEL FROM
RIMS.dbo.VINFO INNER JOIN CASES ON RIMS.dbo.VINFO.CASEN =
RIMS.dbo.CASES.CASEN WHERE (RIMS.dbo.VINFO.AGCODE = 'BPD') AND
(RIMS.dbo.VINFO.ITEXT LIKE '%10851%') AND (RIMS.dbo.VINFO.IDATE
BETWEEN DATEADD(day, -90, GETDATE()) AND GETDATE()) AND
(RIMS.dbo.VINFO.CASEN > 0) OR (RIMS.dbo.VINFO.AGCODE = 'BPD') AND
(RIMS.dbo.VINFO.ITEXT LIKE '%Rec Veh%') AND (RIMS.dbo.VINFO.IDATE
BETWEEN DATEADD(day, -90, GETDATE()) AND GETDATE()) AND
(RIMS.dbo.VINFO.CASEN > 0) ORDER BY RIMS.dbo.VINFO.CASEN DESC"
19:44:12.359694 11900 (write.c:137):tds_put_string converting 594
bytes of " RIMS.dbo.VINFO.DATEREC, 101) AS DATEREC,RIMS.dbo.VINFO.MAKE
as MAKE,RIMS.dbo.VINFO.MODEL as MODEL FROM RIMS.dbo.VINFO INNER JOIN
CASES ON RIMS.dbo.VINFO.CASEN = RIMS.dbo.CASES.CASEN WHERE
(RIMS.dbo.VINFO.AGCODE = 'BPD') AND (RIMS.dbo.VINFO.ITEXT LIKE
'%10851%') AND (RIMS.dbo.VINFO.IDATE BETWEEN DATEADD(day, -90,
GETDATE()) AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) OR
(RIMS.dbo.VINFO.AGCODE = 'BPD') AND (RIMS.dbo.VINFO.ITEXT LIKE '%Rec
Veh%') AND (RIMS.dbo.VINFO.IDATE BETWEEN DATEADD(day, -90, GETDATE())
AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) ORDER BY
RIMS.dbo.VINFO.CASEN DESC"
19:44:12.359710 11900 (write.c:137):tds_put_string converting 466
bytes of "N CASES ON RIMS.dbo.VINFO.CASEN = RIMS.dbo.CASES.CASEN WHERE
(RIMS.dbo.VINFO.AGCODE = 'BPD') AND (RIMS.dbo.VINFO.ITEXT LIKE
'%10851%') AND (RIMS.dbo.VINFO.IDATE BETWEEN DATEADD(day, -90,
GETDATE()) AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) OR
(RIMS.dbo.VINFO.AGCODE = 'BPD') AND (RIMS.dbo.VINFO.ITEXT LIKE '%Rec
Veh%') AND (RIMS.dbo.VINFO.IDATE BETWEEN DATEADD(day, -90, GETDATE())
AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) ORDER BY
RIMS.dbo.VINFO.CASEN DESC"
19:44:12.359723 11900 (write.c:137):tds_put_string converting 338
bytes of "851%') AND (RIMS.dbo.VINFO.IDATE BETWEEN DATEADD(day, -90,
GETDATE()) AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) OR
(RIMS.dbo.VINFO.AGCODE = 'BPD') AND (RIMS.dbo.VINFO.ITEXT LIKE '%Rec
Veh%') AND (RIMS.dbo.VINFO.IDATE BETWEEN DATEADD(day, -90, GETDATE())
AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) ORDER BY
RIMS.dbo.VINFO.CASEN DESC"
19:44:12.359735 11900 (write.c:137):tds_put_string converting 210
bytes of ".VINFO.AGCODE = 'BPD') AND (RIMS.dbo.VINFO.ITEXT LIKE '%Rec
Veh%') AND (RIMS.dbo.VINFO.IDATE BETWEEN DATEADD(day, -90, GETDATE())
AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) ORDER BY
RIMS.dbo.VINFO.CASEN DESC"
19:44:12.359746 11900 (write.c:137):tds_put_string converting 82 bytes
of ") AND GETDATE()) AND (RIMS.dbo.VINFO.CASEN > 0) ORDER BY
RIMS.dbo.VINFO.CASEN DESC"
19:44:12.359757 11900 (write.c:165):tds_put_string wrote 1444 bytes
19:44:12.359766 11900 (util.c:159):Changed query state from QUERYING to
PENDING
19:44:12.359774 11900 (packet.c:133):Sending packet
0000 01 01 05 ac 00 00 01 00-53 00 45 00 4c 00 45 00 |........ S.E.L.E.|
0010 43 00 54 00 20 00 54 00-4f 00 50 00 20 00 28 00 |C.T. .T. O.P. .(.|
0020 32 00 30 00 30 00 30 00-29 00 20 00 52 00 49 00 |2.0.0.0. ). .R.I.|
0030 4d 00 53 00 2e 00 64 00-62 00 6f 00 2e 00 56 00 |M.S...d. b.o...V.|
0040 49 00 4e 00 46 00 4f 00-2e 00 43 00 41 00 53 00 |I.N.F.O. ..C.A.S.|
0050 45 00 4e 00 20 00 61 00-73 00 20 00 43 00 41 00 |E.N. .a. s. .C.A.|
0060 53 00 45 00 4e 00 2c 00-52 00 49 00 4d 00 53 00 |S.E.N.,. R.I.M.S.|
0070 2e 00 64 00 62 00 6f 00-2e 00 56 00 49 00 4e 00 |..d.b.o. ..V.I.N.|
0080 46 00 4f 00 2e 00 49 00-54 00 45 00 58 00 54 00 |F.O...I. T.E.X.T.|
0090 20 00 61 00 73 00 20 00-49 00 54 00 45 00 58 00 | .a.s. . I.T.E.X.|
00a0 54 00 2c 00 52 00 49 00-4d 00 53 00 2e 00 64 00 |T.,.R.I. M.S...d.|
00b0 62 00 6f 00 2e 00 56 00-49 00 4e 00 46 00 4f 00 |b.o...V. I.N.F.O.|
00c0 2e 00 4c 00 49 00 43 00-45 00 4e 00 53 00 45 00 |..L.I.C. E.N.S.E.|
00d0 20 00 61 00 73 00 20 00-4c 00 49 00 43 00 45 00 | .a.s. . L.I.C.E.|
00e0 4e 00 53 00 45 00 2c 00-43 00 4f 00 4e 00 56 00 |N.S.E.,. C.O.N.V.|
00f0 45 00 52 00 54 00 28 00-56 00 41 00 52 00 43 00 |E.R.T.(. V.A.R.C.|
0100 48 00 41 00 52 00 2c 00-20 00 52 00 49 00 4d 00 |H.A.R.,. .R.I.M.|
0110 53 00 2e 00 64 00 62 00-6f 00 2e 00 56 00 49 00 |S...d.b. o...V.I.|
0120 4e 00 46 00 4f 00 2e 00-44 00 41 00 54 00 45 00 |N.F.O... D.A.T.E.|
0130 52 00 45 00 43 00 2c 00-20 00 31 00 30 00 31 00 |R.E.C.,. .1.0.1.|
0140 29 00 20 00 41 00 53 00-20 00 44 00 41 00 54 00 |). .A.S. .D.A.T.|
0150 45 00 52 00 45 00 43 00-2c 00 52 00 49 00 4d 00 |E.R.E.C. ,.R.I.M.|
0160 53 00 2e 00 64 00 62 00-6f 00 2e 00 56 00 49 00 |S...d.b. o...V.I.|
0170 4e 00 46 00 4f 00 2e 00-4d 00 41 00 4b 00 45 00 |N.F.O... M.A.K.E.|
0180 20 00 61 00 73 00 20 00-4d 00 41 00 4b 00 45 00 | .a.s. . M.A.K.E.|
0190 2c 00 52 00 49 00 4d 00-53 00 2e 00 64 00 62 00 |,.R.I.M. S...d.b.|
01a0 6f 00 2e 00 56 00 49 00-4e 00 46 00 4f 00 2e 00 |o...V.I. N.F.O...|
01b0 4d 00 4f 00 44 00 45 00-4c 00 20 00 61 00 73 00 |M.O.D.E. L. .a.s.|
01c0 20 00 4d 00 4f 00 44 00-45 00 4c 00 20 00 46 00 | .M.O.D. E.L. .F.|
01d0 52 00 4f 00 4d 00 20 00-52 00 49 00 4d 00 53 00 |R.O.M. . R.I.M.S.|
01e0 2e 00 64 00 62 00 6f 00-2e 00 56 00 49 00 4e 00 |..d.b.o. ..V.I.N.|
01f0 46 00 4f 00 20 00 49 00-4e 00 4e 00 45 00 52 00 |F.O. .I. N.N.E.R.|
0200 20 00 4a 00 4f 00 49 00-4e 00 20 00 43 00 41 00 | .J.O.I. N. .C.A.|
0210 53 00 45 00 53 00 20 00-4f 00 4e 00 20 00 52 00 |S.E.S. . O.N. .R.|
0220 49 00 4d 00 53 00 2e 00-64 00 62 00 6f 00 2e 00 |I.M.S... d.b.o...|
0230 56 00 49 00 4e 00 46 00-4f 00 2e 00 43 00 41 00 |V.I.N.F. O...C.A.|
0240 53 00 45 00 4e 00 20 00-3d 00 20 00 52 00 49 00 |S.E.N. . =. .R.I.|
0250 4d 00 53 00 2e 00 64 00-62 00 6f 00 2e 00 43 00 |M.S...d. b.o...C.|
0260 41 00 53 00 45 00 53 00-2e 00 43 00 41 00 53 00 |A.S.E.S. ..C.A.S.|
0270 45 00 4e 00 20 00 57 00-48 00 45 00 52 00 45 00 |E.N. .W. H.E.R.E.|
0280 20 00 28 00 52 00 49 00-4d 00 53 00 2e 00 64 00 | .(.R.I. M.S...d.|
0290 62 00 6f 00 2e 00 56 00-49 00 4e 00 46 00 4f 00 |b.o...V. I.N.F.O.|
02a0 2e 00 41 00 47 00 43 00-4f 00 44 00 45 00 20 00 |..A.G.C. O.D.E. .|
02b0 3d 00 20 00 27 00 42 00-50 00 44 00 27 00 29 00 |=. .'.B. P.D.'.).|
02c0 20 00 41 00 4e 00 44 00-20 00 28 00 52 00 49 00 | .A.N.D. .(.R.I.|
02d0 4d 00 53 00 2e 00 64 00-62 00 6f 00 2e 00 56 00 |M.S...d. b.o...V.|
02e0 49 00 4e 00 46 00 4f 00-2e 00 49 00 54 00 45 00 |I.N.F.O. ..I.T.E.|
02f0 58 00 54 00 20 00 4c 00-49 00 4b 00 45 00 20 00 |X.T. .L. I.K.E. .|
0300 27 00 25 00 31 00 30 00-38 00 35 00 31 00 25 00 |'.%.1.0. 8.5.1.%.|
0310 27 00 29 00 20 00 41 00-4e 00 44 00 20 00 28 00 |'.). .A. N.D. .(.|
0320 52 00 49 00 4d 00 53 00-2e 00 64 00 62 00 6f 00 |R.I.M.S. ..d.b.o.|
0330 2e 00 56 00 49 00 4e 00-46 00 4f 00 2e 00 49 00 |..V.I.N. F.O...I.|
0340 44 00 41 00 54 00 45 00-20 00 42 00 45 00 54 00 |D.A.T.E. .B.E.T.|
0350 57 00 45 00 45 00 4e 00-20 00 44 00 41 00 54 00 |W.E.E.N. .D.A.T.|
0360 45 00 41 00 44 00 44 00-28 00 64 00 61 00 79 00 |E.A.D.D. (.d.a.y.|
0370 2c 00 20 00 2d 00 39 00-30 00 2c 00 20 00 47 00 |,. .-.9. 0.,. .G.|
0380 45 00 54 00 44 00 41 00-54 00 45 00 28 00 29 00 |E.T.D.A. T.E.(.).|
0390 29 00 20 00 41 00 4e 00-44 00 20 00 47 00 45 00 |). .A.N. D. .G.E.|
03a0 54 00 44 00 41 00 54 00-45 00 28 00 29 00 29 00 |T.D.A.T. E.(.).).|
03b0 20 00 41 00 4e 00 44 00-20 00 28 00 52 00 49 00 | .A.N.D. .(.R.I.|
03c0 4d 00 53 00 2e 00 64 00-62 00 6f 00 2e 00 56 00 |M.S...d. b.o...V.|
03d0 49 00 4e 00 46 00 4f 00-2e 00 43 00 41 00 53 00 |I.N.F.O. ..C.A.S.|
03e0 45 00 4e 00 20 00 3e 00-20 00 30 00 29 00 20 00 |E.N. .>. .0.). .|
03f0 4f 00 52 00 20 00 28 00-52 00 49 00 4d 00 53 00 |O.R. .(. R.I.M.S.|
0400 2e 00 64 00 62 00 6f 00-2e 00 56 00 49 00 4e 00 |..d.b.o. ..V.I.N.|
0410 46 00 4f 00 2e 00 41 00-47 00 43 00 4f 00 44 00 |F.O...A. G.C.O.D.|
0420 45 00 20 00 3d 00 20 00-27 00 42 00 50 00 44 00 |E. .=. . '.B.P.D.|
0430 27 00 29 00 20 00 41 00-4e 00 44 00 20 00 28 00 |'.). .A. N.D. .(.|
0440 52 00 49 00 4d 00 53 00-2e 00 64 00 62 00 6f 00 |R.I.M.S. ..d.b.o.|
0450 2e 00 56 00 49 00 4e 00-46 00 4f 00 2e 00 49 00 |..V.I.N. F.O...I.|
0460 54 00 45 00 58 00 54 00-20 00 4c 00 49 00 4b 00 |T.E.X.T. .L.I.K.|
0470 45 00 20 00 27 00 25 00-52 00 65 00 63 00 20 00 |E. .'.%. R.e.c. .|
0480 56 00 65 00 68 00 25 00-27 00 29 00 20 00 41 00 |V.e.h.%. '.). .A.|
0490 4e 00 44 00 20 00 28 00-52 00 49 00 4d 00 53 00 |N.D. .(. R.I.M.S.|
04a0 2e 00 64 00 62 00 6f 00-2e 00 56 00 49 00 4e 00 |..d.b.o. ..V.I.N.|
04b0 46 00 4f 00 2e 00 49 00-44 00 41 00 54 00 45 00 |F.O...I. D.A.T.E.|
04c0 20 00 42 00 45 00 54 00-57 00 45 00 45 00 4e 00 | .B.E.T. W.E.E.N.|
04d0 20 00 44 00 41 00 54 00-45 00 41 00 44 00 44 00 | .D.A.T. E.A.D.D.|
04e0 28 00 64 00 61 00 79 00-2c 00 20 00 2d 00 39 00 |(.d.a.y. ,. .-.9.|
04f0 30 00 2c 00 20 00 47 00-45 00 54 00 44 00 41 00 |0.,. .G. E.T.D.A.|
0500 54 00 45 00 28 00 29 00-29 00 20 00 41 00 4e 00 |T.E.(.). ). .A.N.|
0510 44 00 20 00 47 00 45 00-54 00 44 00 41 00 54 00 |D. .G.E. T.D.A.T.|
0520 45 00 28 00 29 00 29 00-20 00 41 00 4e 00 44 00 |E.(.).). .A.N.D.|
0530 20 00 28 00 52 00 49 00-4d 00 53 00 2e 00 64 00 | .(.R.I. M.S...d.|
0540 62 00 6f 00 2e 00 56 00-49 00 4e 00 46 00 4f 00 |b.o...V. I.N.F.O.|
0550 2e 00 43 00 41 00 53 00-45 00 4e 00 20 00 3e 00 |..C.A.S. E.N. .>.|
0560 20 00 30 00 29 00 20 00-4f 00 52 00 44 00 45 00 | .0.). . O.R.D.E.|
0570 52 00 20 00 42 00 59 00-20 00 52 00 49 00 4d 00 |R. .B.Y. .R.I.M.|
0580 53 00 2e 00 64 00 62 00-6f 00 2e 00 56 00 49 00 |S...d.b. o...V.I.|
0590 4e 00 46 00 4f 00 2e 00-43 00 41 00 53 00 45 00 |N.F.O... C.A.S.E.|
05a0 4e 00 20 00 44 00 45 00-53 00 43 00 |N. .D.E. S.C.|

19:44:12.360516 11900 (dblib.c:4619):dbsqlok(0x7f2f9c163800)
19:44:12.360530 11900 (dblib.c:4648):dbsqlok() not done, calling
tds_process_tokens()
19:44:12.360539 11900 (token.c:525):tds_process_tokens(0x7f2f9c1642a0,
0x7f2fc0df75ac, 0x7f2fc0df75a8, 0x6914)
19:44:12.360549 11900 (util.c:159):Changed query state from PENDING to READING
19:45:12.425125 11900 (util.c:315):tdserror(0x1a21930, 0x7f2f9c1642a0, 20003,
0)
19:45:12.425307 11900 (dblib.c:7874):dbperror(0x7f2f9c163800, 20003, 0)
19:45:12.425339 11900 (dblib.c:7927):20003: "Adaptive Server
connection timed out"
19:45:12.425356 11900 (dblib.c:7948):"Adaptive Server connection timed
out", client returns 2 (INT_CANCEL)
19:45:12.425367 11900 (util.c:345):tdserror: client library returned
TDS_INT_CANCEL(2)
19:45:12.425378 11900 (util.c:368):tdserror: returning TDS_INT_CANCEL(2)
19:45:12.425483 11900 (util.c:159):Changed query state from READING to DEAD
19:45:12.425504 11900 (token.c:540):processing result tokens. marker is 0()
19:45:12.425527 11900 (token.c:107):tds_process_default_tokens() marker is 0()
19:45:12.425538 11900 (token.c:110):leaving
tds_process_default_tokens() connection dead
19:45:12.425548 11900 (util.c:99):logic error: cannot change query
state from DEAD to PENDING
19:45:12.425558 11900 (util.c:159):Changed query state from DEAD to PENDING
19:45:12.425679 11900 (dblib.c:3186):dbcancel(0x7f2f9c163800)
19:45:12.425698 11900 (dblib.c:7874):dbperror((nil), 20047, 0)
19:45:12.425713 11900 (dblib.c:7927):20047: "DBPROCESS is dead or not enabled"
19:45:12.425723 11900 (dblib.c:7948):"DBPROCESS is dead or not
enabled", client returns 2 (INT_CANCEL)
19:45:12.426775 11900 (dblib.c:1439):dbclose(0x7f2f9c163800)
19:45:12.426807 11900
(dblib.c:253):dblib_del_connection(0x7f2fd0ece200, 0x7f2f9c1642a0)
19:45:12.426820 11900 (mem.c:598):tds_free_all_results()
19:45:12.426981 11900 (dblib.c:300):dblib_release_tds_ctx(1)
19:45:12.427022 11900 (dblib.c:5837):dbfreebuf(0x7f2f9c163800)
19:45:12.427037 11900 (dblib.c:735):dbloginfree(0x7f2f9c002e70)

--




Archive powered by MHonArc 2.6.24.

Top of Page