Intermittent crashes when connecting to SQL server

[MCF]

I am seeing intermittent crashes when connecting to SQL server. There are no PHP errors thrown, the sqlsrv driver seems to be crashing. Here is the information from the Windows event logs:

Faulting application name: httpd.exe, version: 2.4.16.0, time stamp: 0x55a22a64
Faulting module name: php_pdo_sqlsrv_56_ts.dll, version: 3.2.0.0, time stamp: 0x54f4b646
Exception code: 0xc0000005
Fault offset: 0x00014926
Faulting process id: 0x854
Faulting application start time: 0x01d0fc91fac8f4fc
Faulting application path: C:\Apache24\bin\httpd.exe
Faulting module path: C:\PHP\ext\php_pdo_sqlsrv_56_ts.dll
Report Id: 3f966aa0-6885-11e5-be5e-180373b9ebbe

I am using:

Windows 7
PHP 5.6.13
Apache 2.4.16
CakePHP 2.7.3
Microsoft's sqlsrv pdo driver version 3.2

The crashes happen when making a connection to the database. More precisely At this line:

https://github.com/cakephp/cakephp/blob/2.7.3/lib/Cake/Model/Datasource/Database/Sqlserver.php#L135

As mentioned previously the problem is intermittent and highly variable. It is not easy to reproduce as a result.

The crash information does seem similar to this older issue: #2003

As suggested in that older issue I've set the database connection to persistent => false in an attempt to solve the problem. So far it seems to have fixed it, although I've only spent an hour testing the legacy application I am upgrading to 2.7.3. I will continue to test and report back in a few days.

I have not looked in detail at what the persistent flag is affecting with respect to the sqlsrv connection. Any comments on what this is doing (or if it is actually doing anything) are welcome.

[markstory]

If the sqlsrv driver code ia failing, there is not much we can really do.

[ravage84]

May be it has to do with the fact that the sqlsrv driver relies on connection pooling:

While the mssql API offers the mssql_connect and mssql_pconnect functions for establishing connections and “persistent” connections respectively , the sqlsrv API relies on ODBC connection pooling to make sure the overhead of creating a new connection is kept to a minimum. Connection pooling is on by default. For more information, see Connection Pooling.

http://blogs.msdn.com/b/brian_swan/archive/2010/03/10/mssql-vs-sqlsrv-what-s-the-difference-part-2.aspx

I've ran into the same problem before and it seems to be quite common:

I had a problem with the PDO_SQLSRV driver myself. This parameter
PDO:ATTR_PERSISTENT => true
(set when you create your PDO object) appears to work. However, it caused my PHP-CGI and PHP-CLI processes to crash each time. Removing this parameter - well specifically all parameters - from the new PDO statement causes PDO to work great.
This has been tested on PHP v5.3 and v5.4 setups.
I found this bug when I tried to recycle the $dbh variable (with the same server and such), and the PDO_SQLSRV driver told me, "You can't do that, it's unsupported."

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/005125f9-55d0-45a1-8dd9-02bf434a285b/problems-with-windows-2008r2-iis75-php-54-using-pdosqlsrv-and-sql-server-2008?forum=sqldriverforphp

http://stackoverflow.com/questions/23917085/pdo-attr-persistent-true-with-sqlsrv-causing-delays-fails

May be we should add a note that people should not use persistent connections with sqlsrv?

[MCF]

I've tested all day with persistent set to false. There have been no crashes.

@markstory I agree that if there is a bug in the core Microsoft drivers that we are unable to work around then there is not much to do. I'm not convinced yet that the CakePHP code has not got a problem in how it is interfacing with the driver. The driver does seem touchy in how it is used as @ravage84 has pointed out. As an additional data point I was seeing the same behaviour using much older versions of the driver and PHP (version 2 of the driver and PHP 5.3). So whatever is happening is hitting conditions/bugs in the sqlsrv driver that have been around for a long time.

If it is determined that setting persistent to true for the SQLServer datasource is what is causing the problem then perhaps that setting should be ignored by the datasource?

I do have a reduced test case (essentially Microsoft's sqlsrv DBO example) that does not exhibit this problem even with the PDO:ATTR_PERSISTENT set to true. I'll have another look at that test case next week now that I have a little more perspective on the problem. I'd like to know if turning persistent on or off has any performance impact as well.

[MCF]

FWIW, the issue is still present with CakePHP 2.7.5.

[markstory]

@MCF There were no changes in 2.7.5 intended to address this, as it sounds like the issue is in the driver extension.

[MCF]

I updated the large legacy project I am working on from 2.7.3 to 2.7.5. I thought it was worth mentioning that the problem still exists.

There was one change in 2.7.4 (8fae10a) that now closes connections in the way that Microsoft recommends - by setting the PDO instance to null (see https://msdn.microsoft.com/en-us/library/ff628159.aspx).

I thought it was unlikely that it would have an effect but tested anyway to be sure.

[MCF]

I've spent a bit more time looking at this. I have opened a related issue against the microsoft driver:

microsoft/msphpsql#65

Perhaps that will shed a little more light on the ATTR_PERSISTENT attribute.

[MCF]

I've also done some very basic testing with a reduced test case. My initial observation is that setting persistence to on seems to introduce significant delays in connecting to the server.

With persistence off the connection and subsequent small query is almost instantaneous - even to a server that is not on localhost. With persistence turned on it takes 0.5-1.0 second to establish the connection and make the query to the same server.

[MCF]

I'd be inclined to ignore the persistent config attribute when connecting to SQL Server on windows and to not set the ATTR_PERSISTENT attribute when creating the PDO connection.

Not sure how this impacts connections to SQL Server on other platforms. Although, according to the documentation, the Linux PDO driver used Microsoft's ODBC driver. Perhaps it is also using ODBC connection pooling?

At the very least I would agree with @ravage84 that the documentation should tell people to never set persistent => true for the sql server datasource on Windows.

[lorenzo]

@MCF Would you like to submit a pull request for the docs change?

[markstory]

Documentation updated in cakephp/docs#3511