Setting a connect timeout with PDO

Question 1

I'm using PDO to get data off a MySQL server. What I noticed is this: if the MySQL server is unavailable, it takes really (relatively) long for this code to return an exception:

try {
  $handle = new PDO($db_type . ':host='.$db_host.';dbname='.$db_name,$db_user,$db_pass);
  // Tried using PDO::setAttribute and PDO::ATTR_TIMEOUT here
} catch(PDOException $e) {
  echo $e->getMessage;
}

In case of MySQL it takes just over 2 minutes for the exception to occur (SQLSTATE[HY000] [2003] Can't connect to MySQL server on...) and 30 seconds on PostgreSQL (SQLSTATE[08006] [7] timeout expired).

I tried using PDO::setAttribute and PDO::ATTR_TIMEOUT but it's not working. Which I guess makes sense, since the problem occurs before this statement.

Is there a way to set a timeout for connecting to the DB? 2 minutes/30 seconds seems really long to me for PDO to realize there is nothing there.

I think I saw this being done somewhere, but can't find it again for the life of me.

Question 2

$DBH = new PDO(
    "mysql:host=$host;dbname=$dbname", 
    $username, 
    $password,
    array(
        PDO::ATTR_TIMEOUT => 5, // in seconds
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    )
);

Question 3

There's a conflict between the PHP that is used by Apache and the PHP that is linked to the command line. (It happens more often that it should to be honest).

What is typically done is:

which php

This tells you which php will be expecuted when running in the command line. e.g. /usr/bin/php

mv /usr/bin/php /usr/bin/php.old

Then link or copy the correct PHP version to an executable path:

ln -s /path/to/php/bin/php /usr/bin/php

or the following should also work.

cp /path/to/php/bin/php /usr/bin/php

Also suggested if you want to be able to manually install mods:

ln -s /path/to/php/bin/phpize /usr/bin/phpize
ln -s /path/to/php/bin/php-config /usr/bin/php-config

This way your CLI will match your webserver.

Update:

If as noted in this answer if you are using Ubuntu with multiple alternative installations of PHP you can do:

sudo update-alternatives --set php /usr/bin/php<version>
sudo update-alternatives --set phar /usr/bin/phar<version>
sudo update-alternatives --set phar.phar /usr/bin/phar.phar<version> 
sudo update-alternatives --set phpize /usr/bin/phpize<version> 
sudo update-alternatives --set php-config /usr/bin/php-config<version>

Question 4

You need to paramaterize the imageid value and bind the parameter to PDO::PARAM_LOB:

$sql = "SELECT image FROM image WHERE imageid=:id";
$query = $db_conn->prepare($sql);
$query->execute(array(':id' => $image_id));

$query->bindColumn(1, $image, PDO::PARAM_LOB);
$query->fetch(PDO::FETCH_BOUND);
header("Content-Type: image");
echo $image;

Of course, you'll also want to specify the complete, correct content type (e.g., image/png).

Question 5

To set the default schema, you'll have to execute a query like:

$conn = Foo::Conecction();
$conn->exec('SET search_path TO yourschema');

or, if you want to go about it in a more user-specific way:

$conn->exec('ALTER USER user SET search_path TO yourschema');

As a side-note: Please don't create your PDO instances like that (as the return value of a static method). PDO offers a clean API right out of the box. You're not allowing the caller to determine what DB to connect to, instead, you're hard coding the credentials. This is considered bad practice by any standard. Consider passing the connection to wherever you need it, or -if you insist- create a method that at least requires the caller to pass DB credentials themselves.

I'd also recommend you include the collation in your DSN string, and possibly set some attributes to make life easier when debugging:

$pdo = new PDO(
    "pgsql:dbname=$dbname;host=$host;port=5432;charset=utf8",
    $user,
    $pass,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,//set PDO to throw exceptions on error
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,//NULL values are returned as PHP null's
    ]
);

Check out other attributes and use accordingly.

Last thing: Some time ago PDO could occasionally run into trouble resolving the localhost host-name. I suspect this bug has been fixed already, but just in case it crops up (or even: hasn't been fixed), I'd recommend you use ip addresses whenever you can. If you know the IP already, there's no real point in having to bother the DNS server with resolving a string that you know will resolve to 127.0.0.1 anyway.

Question 6

Try this:

oci:dbname=//host:port/SID/INSTANCE_NAME

And see http://www.php.net/manual/en/ref.pdo-oci.connection.php for more info. The username is passed separately.

Setting a connect timeout with PDO

Answers

php,mysql,postgresql,pdo