R
R
Railchik2015-05-19 16:35:27
PHP
Railchik, 2015-05-19 16:35:27

How and where to remove the timeout 30-40sec of php freetds mssql query execution?

Actually the problem is this. There is a site on debian (php mssql - freetds) and mssql database on MS server 2008 R2. Everything works and works great. BUT: if the request is heavy, then after ~ 40 seconds the script crashes. I run via the console php index.php
index.php

$link = mssql_connect('192.168.*****', 'user', 'pass');

if (!$link) {
    die('Something went wrong while connecting to MSSQL');
}
echo "Begin";

$time_start = microtime(true);

$res = mssql_query("select top 10 * from log where action like '%my_offer%' ");
//$res= mssql_query("select 1 ");

$time_end = microtime(true);

$time = $time_end - $time_start;
echo $time;
......


Begin
PHP Warning: mssql_query(): Query failed in /home/www/100g/report/index.php on line 18
40.11705493927

Through the console, tsql returns

[email protected]:/var/deploy/local/test2# tsql -S mssql -U web
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Password:
1> select top 3 * from log where action like '%tender_offer%';
2> go
Msg 20004, Level 9, State -1, Server OpenClient, Line -1
Read from the server failed

Sinned on ms server.
We created an application in C++ (compiled under Windows with connection via ADODB::_ConnectionPtr ").
Throws an exception after 30 seconds
Request timed out

If you specify a timeout for the connection something like this
ADODB::_ConnectionPtr m_conn = 0;
ADODB::_RecordsetPtr m_rs = 0;
HRESULT hr;
      try 
      {			
        hr = m_conn.CreateInstance(__uuidof(ADODB::Connection));
        <b>m_conn->CommandTimeout =200;</b>
        if FAILED(hr) {
          throw _com_error(hr);
        }
...........................

Returns a result.
Changed all possible settings in php, freetds.
The same request in MSSQL Manager is executed with a bang.
Tell me at least where to dig, what to try where to look.
Thanks in advance.
UPD 1:
a simple select * from log request hangs for an unlimited amount of time. In activity monitor MSSQL Server Manager studio
128 1 web ***** SUSPENDED SELECT PHP 5 117 ASYNC_NETWORK_IO External ExternalResource=ASYNC_NETWORK_IO 16 test RealTimeGroup
If mssql.timeout is set in php.ini less than 40 seconds, such timeout is applied, more - no.
# server specific section
[global]
        # TDS protocol version
        tds version = 8.0
        port = 1433

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
        dump file = /tmp/free1tds.log
        debug flags = 0xffff

        # Command and connection timeouts
        timeout = 300
        connect timeout = 300

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        text size = 6451200

# A typical Sybase server
[egServer50]
        host = symachine.domain.com
        port = 5000
        tds version = 5.0

# A typical Microsoft server
[egServer70]
        host = ntmachine.domain.com
        port = 1433
        tds version = 7.0

[mssql]
        host = 192.168.*.*
        port = 1433
        tds version = 8.0
        client charset = UTF-8
        date format = %F %T
        dump file = /tmp/free2tds.log

Here timeout is not applied at all. No way. Those. I set 10 or 20, it still works out 40. This config is used, because the log file is taken from here.
Our solution:
I don't know why, but by changing the KeepAlive tcp/ip parameter in the sql server settings, everything worked. Put for the server and for the client.
c7b0c2e55c7f467ba43b08266c02ba33.jpg
It is also possible that in the registry you need to insert the line KeepAliveTime HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Parameters

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question