Re: Problem with a query against MS SQL 2005

2015-08-31 15:15 GMT+01:00 Rob Morin <[email protected]>:
> All,
>
> First, please let me apologize if this already went to the group. I think
> I tried to send it before I was officially a member, so I don't know if it
> actually went or not.
>
> I'm upgrading a server from PHP 4.3 to PHP 5.6 and doing the necessary
> code remediation.  I've got most things working, but I'm having a problem
> on certain queries and I'm to the point that I believe it is the driver
> (PHP 4.3 used a mssql driver, PHP 5.6 uses FreeTDS) that's causing the
> problem.
>
> The Environment
> CentOS 7.1 with PHP 5.6.8 and php56w-mssql from webtatic
> This comes with FreeTDS 0.91 from epel
>
> I've set the FreeTDS global protocol version to 7.2 in freetds.conf. When
> it starts, I see an error in the log : "TDS version downgraded to 7.1!"

This is expected, newer version use 7.2 even for dblib.

> The wait time is also set to 180.
>

Which make sense with the 3 minutes wait (180 seconds for cancel, 180 for
error).

> The log of the downgrade:
>         util.c:331:tdserror(0x7fd16ec064c0, 0x7fd16ec9edd0, 100, 0)
>         dblib.c:7929:dbperror(0x7fd16ec9e360, 100, 0)
>         dblib.c:7981:100: "TDS version downgraded to 7.1!"
>         dblib.c:8002:"TDS version downgraded to 7.1!", client returns 2
> (INT_CANCEL)
>         util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
>         util.c:384:tdserror: returning TDS_INT_CANCEL(2)
>         iconv.c:330:tds_iconv_open(0x7fd16ec9edd0, UTF-8)
>         iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
>         iconv.c:187:local name for UTF-8 is UTF-8
>         iconv.c:187:local name for UCS-2LE is UCS-2LE
>         iconv.c:187:local name for UCS-2BE is UCS-2BE
>         iconv.c:349:setting up conversions for client charset "UTF-8"
>         iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
>         iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE"
> conversion
>         iconv.c:394:tds_iconv_open: done
>         net.c:205:Connecting to 10.100.5.201 port 1433 (TDS version 7.1)
>         net.c:270:tds_open_socket: connect(2) returned "Operation now in
> progress"
>         net.c:310:tds_open_socket() succeeded
>         util.c:156:Changed query state from DEAD to IDLE
>
> I've established that the server is able to connect to the SQL server and
> run 'normal' queries. Apparently this one is 'special', but I'm not sure
> at what level.
>

Can you enable timestamps on logs?

> Here's the query:
>         IF OBJECT_ID('tempdb..#EarlyRenewals') IS NULL
>         SELECT * INTO #EarlyRenewals FROM
> [atl-sportal01].sportal.dbo.EarlyRenewalsBasedOnDates)
>
> It appears to be timing out while waiting on the SQL server (SQL 2005). It
> takes the server ~8-10 secs to respond to the query. The php script is
> 'failing' after waiting ~3 minutes.
>

Weird... would like to understand the type of columns. Could be that
for some reasons
the query is much slower with different connection settings.

> Here's the log after the query is sent:
>         dblib.c:4639:dbsqlok(0x7fd16ecc1540)
>         dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
>         token.c:540:tds_process_tokens(0x7fd16ecc2890, 0x7fff3872e248,
> 0x7fff3872e24c, 0x6914)
>         util.c:156:Changed query state from PENDING to READING
>         util.c:331:tdserror(0x7fd16ec064c0, 0x7fd16ecc2890, 20003, 0)
>         dblib.c:7929:dbperror(0x7fd16ecc1540, 20003, 0)
>         dblib.c:7981:20003: "Adaptive Server connection timed out"
>         dblib.c:8002:"Adaptive Server connection timed out", client
> returns 2 (INT_CANCEL)
>         util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
>         util.c:384:tdserror: returning TDS_INT_CANCEL(2)
>         util.c:156:Changed query state from READING to DEAD
>         token.c:555:processing result tokens.  marker is  0()
>         token.c:122:tds_process_default_tokens() marker is 0()
>         token.c:125:leaving tds_process_default_tokens() connection dead
>         util.c:104:logic error: cannot change query state from DEAD to
> PENDING
>         dblib.c:3196:dbcancel(0x7fd16ecc1540)
>         dblib.c:7929:dbperror(0x7fd16ecc1540, 20047, 0)
>         dblib.c:7981:20047: "DBPROCESS is dead or not enabled"
>         dblib.c:8002:"DBPROCESS is dead or not enabled", client returns 2
> (INT_CANCEL)
>
> Any help or guidance you can provide is greatly appreciated. Please let me
> know if there's any additional information I can provide.
>
> Thank you,
> Rob
>
>
>
>
> Rob Morin
>
>
> Systems Integration Architect
> Silverpop, an IBM Company
>

Frediano