PDO Connection Test

Question

I am writing an installer for one of my apps and I would like to be able to test some default database settings.

Is this possible using PDO to test valid and invalid database connections?

I have the following code:

try{
            $dbh = new pdo('mysql:host=127.0.0.1:3308;dbname=axpdb','admin','1234');
            die(json_encode(array('outcome' => true)));
        }catch(PDOException $ex){
            die(json_encode(array(
                'outcome' => false,
                'message' => 'Unable to connect'
            )));
        }

The problem I am having is that the script trys to connect until the script execution time of 60 seconds runs out instead of saying it cannot connect to the db.

Thanks

Lower case pdo works the same as PDO, if I put correct details in the script works as expected but i'm trying to detect invalid settings — Lee, Jun 07 '11 at 10:01
did you try to add the option `PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION` ? — Sascha Galley, Jun 07 '11 at 10:03
If i add it here: `$dbh = new PDO('mysql:host=127.0.0.1;port=3308;dbname=axpdb','admin','1234', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));` it makes no difference and as it is that line that errors i cannot do `$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );` below — Lee, Jun 07 '11 at 10:30

score 64 · Accepted Answer · answered Jun 07 '11 at 10:25

64

you need to set the error mode when connection to the database:

try{
    $dbh = new pdo( 'mysql:host=127.0.0.1:3308;dbname=axpdb',
                    'admin',
                    '1234',
                    array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    die(json_encode(array('outcome' => true)));
}
catch(PDOException $ex){
    die(json_encode(array('outcome' => false, 'message' => 'Unable to connect')));
}

for more infos see the following links:

Using MySQL with PDO

Errors and error handling

answered Jun 07 '11 at 10:25

Sascha Galley

15,711
5
37
51

1

That combined with `ini_set('display_errors', 'off');` works fine. Thanks – Lee Jun 07 '11 at 10:38
5

@Themodem: Why would you need to turn display errors off? You've caught the exception -- it's handled. – mpen Apr 06 '13 at 17:20
1

@Mark, because the implementation of `PDO::ERRMODE_EXCEPTION` is broken (PHP5.3.28), and has no effect in some cases (e.g. "DB server not running" on my host). So, this solution then, unfortunately, is not enough, as Themodem noticed it in his workaround. *(Hey, BTW, just put `@new pdo()`, don't brute-force `display_errors` to `off` just for this!)* – Sz. Apr 25 '14 at 08:22
For example: `Warning: PDO::__construct() [pdo.--construct]: [2002] No connection could be made because the target machine actively refused it. (trying to connect via tcp://localhost:3306) in ...`, and the code is: `$this->pdo = new PDO($cfg['DB'], $cfg['DB_USER'], $cfg['DB_PASS'], array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_LOCAL_INFILE => true));` (Note: the exception *is* thrown, and cought, I put some log messages in the catch clause.) – Sz. Apr 25 '14 at 08:26

Crozin · Answer 2 · 2011-06-07T10:45:48.297

As @Sascha Galley already mentioned you should set error mode to exception mode. However, you should also set up PDO::ATTR_TIMEOUT attribute to prevent a long time waiting for response in some cases.

Although documentation says that behavior of this attribute is driver-dependent in case of MySQL it's a connection timeout. You won't find anything about it documentation but here's a short snippet from driver's source code:

long connect_timeout = pdo_attr_lval(driver_options, PDO_ATTR_TIMEOUT, 30 TSRMLS_CC);

score 2 · Answer 3 · edited Mar 09 '18 at 10:52

2

As seen e.g. in the comments at this answer (but hardly anywhere else, so I made it more visible here), the "classic" PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION solution does not always work.

The implementation of PDO::ERRMODE_EXCEPTION is broken, so it seems to be "leaking" in some cases.

For example:

Warning: PDO::__construct() [pdo.--construct]: [2002] No connection could be made because the target machine actively refused it. (trying to connect via tcp://localhost:3306) in [...] db.php on line 34

The code there:

try {
    $this->pdo = new PDO($cfg['DB'], $cfg['DB_USER'], $cfg['DB_PASS'],
        array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
} catch {
    echo("Can't open the database.");
}

The exception is thrown (and cought: I can see my message).

So, as a necessary workaround, you need to also put a @ (let's call it a "diaper operator" in this case) before new pdo(...) to actually keep it clean.

edited Mar 09 '18 at 10:52

PiTheNumber

22,828
17
107
180

answered Apr 25 '14 at 08:48

Sz.

3,342
1
30
43

It is not advisable to suppress errors as that might cause the program to behave unexpectedly without clearly notifying you of the issue. Check out `https://www.sitepoint.com/why-suppressing-notices-is-wrong/`for more information – Kalema Edgar Feb 15 '22 at 20:30
@KalemaEdgar, in general, of course. But you seem to have missed the _"The implementation of `PDO::ERRMODE_EXCEPTION` [is broken](https://bugs.php.net/bug.php?id=63812)"_ part. That's a special case here. (The bug report still seems to be open, but I haven't played with PDO in recent years, maybe it's no longer failing.) – Sz. Mar 11 '22 at 18:35

score 1 · Answer 4 · answered Jan 05 '16 at 18:30

1

There's a missing closing parenthese at the end of PDO::ERRMODE_EXCEPTION.

Should be:

$this->pdo = new PDO($cfg['DB'], $cfg['DB_USER'], $cfg['DB_PASS'],
    array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

answered Jan 05 '16 at 18:30

Denis St-Michel

79
6

PDO Connection Test

4 Answers4

Linked

Related