SQLSRV_ATTR_QUERY_TIMEOUT issue

[muratbostan]

+## PHP Driver version or file name
+Lastest version
+## SQL Server version

• 2008(SP2)
  +## Client operating system
• Centos 7.6
  +## PHP version
• Php 7.3 and codeigniter 3.1
  +## Microsoft ODBC Driver version
  +Lastest version

I connect to remote a mssql databas . But sometimes mssql connect take long time or gives errors.
I want to set a query time out or mssql connect login time out.
I thinks connect timeout works well,query time out doesn't work as I wanted.

Sometimes gives time out as i wanted but sometimes waiting 19-40 second and gives tcp error
İ don't want to wait too long for response.
my request to mssql db;

     Name    Status  Type    Size        Time       Respose
      test    200      xhr    10.5kb     400ms       working well
      test    200      xhr    10.5kb     400ms       working well
      test    200      xhr    608kb      1.16ms      login time out
      test    200      xhr    10.5kb     400ms       working well
      test    200      xhr    10.5kb     400ms       working well
      test    200      xhr    618kb      39.45s      gives TCP error
      test    200      xhr    10.5kb     400ms       working well
      test    200      xhr    10.5kb     400ms       working well

My Connect Configuration

try{
  $dsn ="sqlsrv:Server=IP,Port;Database=DB;";
  $db  = new PDO($dsn, "username", "password");

 //$db->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 3);
 //$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION );

  $query=('select * from Accounting');

  $cmd = $db->prepare($query,array(PDO::SQLSRV_ATTR_QUERY_TIMEOUT => 1 ));

  if(!$cmd->execute()){
    throw new PDOException ($cmd->errorInfo());
  }
  return $cmd->fetchAll(PDO::FETCH_ASSOC);
}catch(PDOException $e) {
  die("Error connecting to SQL Server: " . $e->getMessage());
}

[yitam]

hi @cosmic-horizon , note that we no longer support SQL Server 2008

That being said, we have merged a pull request to fix query timeout issues (#1037) , which we will roll out in our upcoming preview release.

In the meantime, please modify your script to utilize the exception mechanism for query timeouts. When it's timed out, an exception will be thrown from within the driver, so try not to comment out the following line.

$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

See if this makes a difference.

[muratbostan]

hi @yitam ,thanks for answer, but it seems doesn't work

[yitam]

@cosmic-horizon but that's another issue. The message indicated a connection error. What's your unixODBC version? Can you also check your sql server settings, like if you have set maximum number of remote connections and remote login timeout, etc.

Please also try with another instance of SQL Server that we officially support

[muratbostan]

• thanks for information @yitam
• --unixODBC 2.3.7 installed ;i'll check those settings,
• I think the problem is that the query cannot be run after the connection
• Is there any solution for as terminate the connection if there is no response.
• and Sorry for wasting your time.

[yitam]

Are you using connection pooling, @cosmic-horizon ?

A race condition exists such that the connection is valid initially but later becomes invalid when sending the query. The fix to detect dead connections in the pool was merged into unixODBC 2.3.7.

Make sure you don't have multiple unixODBCs in your env.

Also, connection resiliency in the latest drivers might be able to help, like this:

$connectionInfo = "ConnectRetryCount = 2; ConnectRetryInterval = 10;";
$conn = new PDO( "sqlsrv:server = $server; Database = $db; $connectionInfo;", $uid, $pwd);

[muratbostan]

I'm not use connection pooling,
here installed unixODBCs

unixODBC.x86_64             2.3.7-1.rh          @packages-microsoft-com-prod
unixODBC-devel.x86_64       2.3.7-1.rh          @packages-microsoft-com-prod

and connection resiliency seems support only sql 2014 and higher

[yitam]

Hi @cosmic-horizon

Perhaps you can enable odbc tracing. After modifying your odbcinst.ini file please also modify your connection string to enable "TraceOn":

$dsn ="sqlsrv:Server=IP,Port;Database=DB;TraceOn=true;";
$db  = new PDO($dsn, "username", "password");

Then send us your odbc trace log.

[muratbostan]

here to trace logs:

//this take 400ms work without an error

 
> [ODBC][8859][1574251779.522158][__handles.c][460]
>     		Exit:[SQL_SUCCESS]
>     			Environment = 0x55d61b19e630
>     [ODBC][8859][1574251779.522229][SQLGetEnvAttr.c][157]
>     		Entry:
>     			Environment = 0x55d61b19e630
>     			Attribute = 65002
>     			Value = 0x7ffd3d1f4780
>     			Buffer Len = 128
>     			StrLen = 0x7ffd3d1f471c
>     [ODBC][8859][1574251779.522256][SQLGetEnvAttr.c][273]
>     		Exit:[SQL_SUCCESS]
>     [ODBC][8859][1574251779.522288][SQLFreeHandle.c][220]
>     		Entry:
>     			Handle Type = 1
>     			Input Handle = 0x55d61b19e630

//this take 20 seconds and response is tcp provider error

 [ODBC][62440][1574282013.537773][__handles.c][460]
		Exit:[SQL_SUCCESS]
			Environment = 0x55d61b19d3d0
[ODBC][62440][1574282013.537835][SQLGetEnvAttr.c][157]
		Entry:
			Environment = 0x55d61b19d3d0
			Attribute = 65002
			Value = 0x7ffd3d1f4780
			Buffer Len = 128
			StrLen = 0x7ffd3d1f471c
[ODBC][62440][1574282013.537884][SQLGetEnvAttr.c][273]
		Exit:[SQL_SUCCESS]
[ODBC][62440][1574282013.537926][SQLFreeHandle.c][220]
		Entry:
			Handle Type = 1
			Input Handle = 0x55d61b19d3d0

[yitam]

@cosmic-horizon please provide the entire trace log or at least up to the point when it starts failing.

[muratbostan]

@yitam I got tcp error sometimes but didn't find any error records in the 1,500,000 line log records.Maybe that error is caused by sql server settings

[yitam]

@cosmic-horizon is the tcp error the same message all the time? How often do you get the error(s)?

RE your screenshot above, it helps if you show the entire error message, like a var_dump, for example:

catch(PDOException $e) {
  echo "Error connecting to SQL Server:\n"; 
  var_dump($e);
}

[yitam]

Any update @cosmic-horizon ?