Is PDO::ATTR_PERSISTENT supported by the PDO driver?

[MCF]

This is either a bug report or a question regarding the PDO::ATTR_PERSISTENT attribute - depending on what the facts are. My initial question is: is the option supported? The reason I ask is because it appears that you can set the option when you create a new PDO connection, and you can read the option back using getAttribute(PDO::ATTR_PERSISTENT) - seeming to imply that it is supported. But only if you are careful with the order of the options in the call to the PDO constructor.

This, for example, will work and set the option:

$conn = new PDO(
    "sqlsrv:server=$serverName;Database=$database",
    $uid,
    $pwd,
    array(
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    )
);

But changing the order of the options will throw an exception. This call:

$conn = new PDO(
    "sqlsrv:server=$serverName;Database=$database",
    $uid,
    $pwd,
    array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_PERSISTENT => true
    )
);

will result in this exception message and a failure to connect:

SQLSTATE[IMSSP]: An unsupported attribute was designated on the PDO object.

This blog entry from Microsoft when these new drivers were first released noted that ODBC connection pooling is used instead of the persistent connection offered by other older, non-microsoft drivers.

So should one ever use ATTR_PERSISTENT? If yes to that question, why does the second code example throw an exception? If no why is it even exposed by the driver - or at the very least why is it not simply ignored (as I mentioned above, the first code example does succeed in the setting the option)?

FWIW the following versions were used for the testing:

PDO::ATTR_CLIENT_VERSION value:
array (
  'DriverDllName' => 'msodbcsql11.dll',
  'DriverODBCVer' => '03.80',
  'DriverVer' => '12.00.4100',
  'ExtensionVer' => '3.2.0.0',
)

Windows 7 Professional, Service Pack 1
PHP 5.6.13
Apache 2.4.16

[MCF]

As a follow on to this question. It appears that setting the PDO::ATTR_PERSISTENT attribute to true with this driver will cause intermittent crashes and timeouts when attempting to connect to the database. The crashes tend to be silent failures as well (no exceptions thrown, etc.). Hard to diagnose as a result.

I would strongly recommend never setting that attribute with this driver.

A note to the driver maintainers - if that attribute cannot be supported and/or is not needed (which I suspect is the case) perhaps the attribute should be ignored, or throw an error when it is set in all cases?

[yukiwongky]

Hi @MCF ,

As of release 4.1.8-preview, when the user specify PDO::ATTR_PERSISTENT in the connection attribute, pdo::__construct will throw an exception stating ATTR_PERSISTENT is not support in our driver (even when ATTR_ERRMODE is SILENT or WARNING). The intermittent crash and memory leak from persistent connection should also be fixed in the latest release.

Please double check if the issue still exists and update us.

[silva96]

throws An unsupported attribute was designated on the PDO object

[yukiwongky]

@silva96 , PDO::ATTR_PERSISTENT is not supported in the PDO_SQLSRV driver. Previously, a persistent DBH is made in the PDO side but the PDO_SQLSRV driver didn't clean this up properly thus resulting in intermittent crash. As of release 4.1.8-preview, An unsupported attribute was designated on the PDO object is thrown when user tries to connect with this PDO::ATTR_PERSISTENT. After the exception is thrown, the PDO_SQLSRV driver cleans up the persistent DBH to stop memory leaks.