CODEWITHSUNDEEP
phppdo
jjwdesign
jjwdesign

Reputation: 3322

FreeTDS: Properly close a PHP PDO connection, not working correctly

I have an old CodeIgniter instance that I'm trying to use with PHP PDO. This is based on the "cickes" modifications (see links below). It works well under small request loads, but the connections seem to hang for a long while under heavy request loads. Eventually I run out of available connections and the DB returns and error: SQLSTATE[] (null) (severity 0).

https://github.com/cickes/PDOinCodeigniter2

I'm using a MS SQL Server 2008 with PHP PDO. The PHP driver for MSSQL is the FreeTDS driver which can be seen with phpinfo() output. FreeTDS PHPINFO shown below:

mssql
MSSQL Support   enabled
Active Persistent Links 1
Active Links    1
Library version FreeTDS
Directive   Local Value Master Value
mssql.allow_persistent  On  On
mssql.batchsize 0   0
mssql.charset   no value    no value
mssql.compatability_mode    Off Off
mssql.connect_timeout   5   5
mssql.datetimeconvert   On  On
mssql.max_links Unlimited   Unlimited
mssql.max_persistent    Unlimited   Unlimited
mssql.max_procs Unlimited   Unlimited
mssql.min_error_severity    10  10
mssql.min_message_severity  10  10
mssql.secure_connection Off Off
mssql.textlimit Server default  Server default
mssql.textsize  Server default  Server default
mssql.timeout   60  60

I've set the DB handler to null as well as unset() the DB handler. Both changes do not help. Eventually I run out of connections.

I thought that I could try to pass in an attribute options during connection, as suggested on the PHP Web site. Unfortunately, the FreeTDS driver does not support attributes. The first $DB defined is working, but I believe it uses persistent connections.

$DB = new PDO($params['dbdriver'].':host='.$params['hostname'].'; dbname='.$params['database'], $params['username'], $params['password']);

Note: the attribute setting example below is not supported by driver.

$DB = new PDO($params['dbdriver'].':host='.$params['hostname'].'; dbname='.$params['database'], $params['username'], $params['password'], array(
    PDO::ATTR_PERSISTENT => false
));

Message: PDO::__construct(): SQLSTATE[IM001]: Driver does not support this function: driver does not support setting attributes

$DB = new PDO($params['dbdriver'].':host='.$params['hostname'].'; dbname='.$params['database'], $params['username'], $params['password']);

PDO::__construct(): SQLSTATE[IM001]: Driver does not support this function: driver does not support setting attributes.

Perhaps persistent links are causing the problem? I think there are non-persistent mssql_connect() functions also being used on this server. Perhaps someone can suggest a better configuration for the FreeTDS driver?

Laravel Testing (Added 08/14/2014):

I decided to test Laravel on the "same server" with the same database connection settings. It does not produce the same problem. I can hit it multiple times in a row without "hanging" or DB errors. So, this bring up the question: How is Laravel doing PHP PDO differently?

Upvotes: 0

Views: 1418

Answers (2)

jjwdesign
jjwdesign

Reputation: 3322

The Cickles PDO changes for CodeIgniter includes a bug that I found. The DB function is called multiple times, so the database connection was being called multiple times (9). In order to prevent this, I first check a reference to a global DB variable to make sure I'm not creating another connection to the database. I hope this answer helps someone else who might experience the same issue.

See Github Project:

https://github.com/cickes/PDOinCodeigniter2

File:

https://github.com/cickes/PDOinCodeigniter2/blob/master/system/database/DB.php

if (!empty($GLOBALS['DB'])) $DB = $GLOBALS['DB'];
//var_dump($DB);
//echo "DB Function Called."."<br />";

if (empty($DB)) {

// Using PDO: connect to a database using PDO
try {

    echo "Creating new PDO Connection!";
    $DB = new PDO($params['dbdriver'].':host='.$params['hostname'].'; dbname='.$params['database'], $params['username'], $params['password']);

    if (ENVIRONMENT == 'development') {
        $DB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } elseif (ENVIRONMENT == 'production') {
        $DB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
    }
    $DB->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
    $DB->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL);
    $DB->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
    //$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    $GLOBALS['DB'] = $DB;

} catch(PDOException $e) {

    //print_r($params);
    echo $e->getMessage();
}

}

Upvotes: 1

meda
meda

Reputation: 45490

Try to reuse the connection if possible, don't close them yourself (PDO is smart enough to do that).

Check if the connection is null before creating a new one

if($DB == null){
    $DB = new PDO($params['dbdriver'].':host='.$params['hostname'].'; dbname='.$params['database'], $params['username'], $params['password']);
    $DB->setAttribute(PDO::ATTR_PERSISTENT, false);    
}

Otherwise reuse that same object.

Upvotes: 0

Related Questions