Connection Timeout, Driver Crash on Windows

[elton182]

PHP Driver version or file name

php_pdo_sqlsrv-5.8.1-7.2-nts-vc15-x86

SQL Server version

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Client operating system

Windows server 2019 datacenter

PHP version

7.2.20

Microsoft ODBC Driver version

17.5.2.1

Problem description

It is like issue 678 or 762

The diference is tha I got a totally windows environment where after a lot of queries in a single request. After 30 or so find/insert/updates in sequence the conection closes at the driver level and it doesn't reconnect.

SQLSTATE[IMC06]: [Microsoft][ODBC Driver 17 for SQL Server]A conexão foi desfeita e a recuperação não é possível. A conexão está marcada pelo driver do cliente como não recuperável. Não houve nenhuma tentativa de restaurar a conexão. {"exception":"[object] (PDOException(code: IMC06): SQLSTATE[IMC06]: [Microsoft][ODBC Driver 17 for SQL Server]A conexão foi desfeita e a recuperação não é possível. A conexão está marcada pelo driver do cliente como não recuperável. Não houve nenhuma tentativa de restaurar a conexão.

[yitam]

Hi @elton182 in Windows, connection pooling is enabled by default. Did you change the default connection resiliency settings?

Also, please check your network trace and compare to those in this support article

[elton182]

No I didn't changed the connection resiliency, I didn't even know it existed until the problem arise.

I get to write a simple code that generates de error:

 $n =0 ; while($n <=1000) { Transportadora::limit(1)->get(); echo $n . "\n" ; $n++; }

With this code the error shows on the 393 count always, and if a do a simple reconnect, it works again.

This is the error the above scripts throws.

Illuminate/Database/QueryException with message 'SQLSTATE[08S01]: [Microsoft][ODBC Driver 17 for SQL Server]Provedor TCP: An existing connection was forcibly closed by the remote host.
 (SQL: select top 1 * from [Transportadora])'

[yitam]

Hi @elton182 in Windows, connection pooling is enabled by default. Did you change the default connection resiliency settings?

Also, please check your network trace and compare to those in this support article

Have you checked the support article in my first reply, @elton182 ?

[elton182]

Hi @yitam I've checked with the system admin and he told me that both servers are Windows 2019 that already has the update that fix the issue.

[yitam]

Hi @elton182 , please check SQL Server Error log and Windows event log as explained in this article.

Do you set LoginTimeout by any chance?

You might also try using connection resiliency by changing the default values for ConnectRetryCount and ConnectRetryInterval

For example, something like this ConnectRetryCount = 2; ConnectRetryInterval = 10;

[yitam]

Any update @elton182 ?

The diference is tha I got a totally windows environment where after a lot of queries in a single request. After 30 or so find/insert/updates in sequence the conection closes at the driver level and it doesn't reconnect.

Can you provide some examples of your queries? Do you connect and disconnect for each select, insert or update requests?

$n =0 ; while($n <=1000) { Transportadora::limit(1)->get(); echo $n . "\n" ; $n++; }

Does this disconnect before connecting again?

[elton182]

@yitam I've used an script direct with PHP doing queries and the error doesnt come out.
I'm starting to think that is something on laravel's end but I ll need to look more on this.

[yitam]

Ok thanks @elton182. Please keep us posted.

[elton182]

@yitam I noticed that laravel uses PDO to connect to MSSQL, and on my tests with php direct I've used sqlsrv_connect.

I think it may be something releated to PDO.

[yitam]

@elton182 can you try the same with our pdo_sqlsrv directly?
Also, you might want to reach out to Laravel support too.

[elton182]

@yitam I confirmed my hypotheses.

With the script below, which connects directly to the database with PHP throught PDO, it throws the error after 393 queries. It is basically the same script that I am using in the laravel example.

<?php  

$serverName = "server,port";  
  
/* Connect using Windows Authentication. */  
try  
{  
$conn = new PDO( "sqlsrv:server=$serverName ; Database=Vision", "database", "passwor");  
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
}  
catch(Exception $e)  
{   
die( print_r( $e->getMessage() ) );   
}  

try  
{  

    for($n = 0;$n<=1500; $n++){
        
        $tsql = "SELECT top 1 * FROM Transportadora";  
          
        $getProducts = $conn->prepare($tsql);  
        $getProducts->execute();  
        $products = $getProducts->fetchAll(PDO::FETCH_ASSOC);  
        echo $n . '- ' . $products[0]['Nome'] . "\n";
    }

}catch(Exception $e)  
{   
die( print_r( $e->getMessage() ) );   
}  

Error:

SQLSTATE[08S01]: [Microsoft][ODBC Driver 17 for SQL Server]Provedor TCP: An existing connection was forcibly closed by the remote host.

[elton182]

@yitam can I do anything else to test this?

[yitam]

Hi @elton182 the repro script helps and let me do some investigation first. I'll get back to you on this.

[yitam]

Hi @elton182

I can't reproduce this on my side. I've tried SQL Servers on premise and in the local network. I tested up to 3000 times. I've also tested connecting to Azure SQL Server, as shown below.

Have you tried doing something similar using Management Studio?

For example:

DECLARE @CTR AS INTEGER
SET @CTR = 0
WHILE ( @CTR) < 1500
BEGIN  
	SELECT TOP 1 * FROM A_TABLE
	SET @CTR = @CTR + 1
END  

As I mentioned above, please provide SQL Server Error log, Windows event log, SQL Profiler trace and/or ODBC trace if possible.

FYI, I copied and pasted one of the answers from this page:

This problem can be caused by a lot of network issues including but not limited to:

• Workstations set to Stand By, Sleep or Hibernate when not used for a period of time.
• Timeout settings on SQL Server, disconnecting idle sessions.
• Faulty network infrastructure causing the connection to the server to be dropped. This includes bad Wireless quality.
• Not installing critical updates, causing a faulty network driver to drop the connection.
• The Database Property under Options "Auto Close" was set to True allowing the server to close "inactive" connections. This must be set to False.

[elton182]

@yitam I've tried on a sqlserver express on my localhost and it doesn't happens.
I think it may be something related especificaly on this customer environment.

I'll test direct on a sqlserver client like Managment Studio and others and get the logs you asked.

[yitam]

Any update, @elton182 ?

[elton182]

Hi @yitam.

I've tried on the Managment Studio and it works normal.
About the logs I could't get then yet, I'm waiting for the IT.

Today I've noticed a new error:

local.ERROR: SQLSTATE[08S01]: [Microsoft][ODBC Driver 17 for SQL Server]Falha de vínculo de comunicação {"exception":"[object] (PDOException(code: 08S01): SQLSTATE[08S01]: [Microsoft][ODBC Driver 17 for SQL Server]Falha de vínculo de comunicação 

I'm still thinking this is related to the PDO driver somehow.

[yitam]

Hi @elton182 please do check the logs as this is more like an issue with the server or network rather than the driver, as explained in this issue discussion.

That said, please also check this forum and see if you find anything useful.

[yitam]

Closing this due to inactivity, @elton182 . Please feel free to reopen this issue when you have logs and/or traces for us to investigate the problem.