PDO: How to check if the connection is really active, for real?

Question

There were already two questions about this, but no one really answered the question.

I know that PDO will throw an exception if the connection does not work (if you enable PDO::ERRMODE_EXCEPTION ), but I want to check if the connection continues, possibly after a few hours.

I have a long script, and after a while it expires. Theoretically, I can increase this amount of time using PDO::ATTR_TIMEOUT , but basically I want to write a function that returns me an active connection - either an existing connection, if it is already established, or it has not been disconnected or reconnected if it is.

Should I just do SELECT 1 , catch the exception and reconnect? Or is there a better way?

Answer 1

The MySQL protocol supports the special COM_PING command for this purpose, and for the C API, there is a mysql_ping () call. This checks if the connection is active.

If the connection was created using MYSQL_OPT_RECONNECT, it is automatically connected ( https://dev.mysql.com/doc/refman/5.6/en/auto-reconnect.html ).

Unfortunately, none of these features are supported if you are using the current version of PDO. You can send SQL query strings, not special commands. And PDO now uses the mysqlnd driver, which has its advantages, but does not support the reconnect option. So the problem is anyway.

I don’t know a more elegant solution than trying to issue a “dummy” request, such as SELECT 1 , catch an exception, and if you get an error code 2006 (the server is gone), then reconnect.

You can create a singleton class to store your db connection and test a live connection every time the application code calls getConnection (). Here is an example that I tested:

 class DB { protected static $pdo = null; public static function getConnection() { // initialize $pdo on first call if (self::$pdo == null) { self::init(); } // now we should have a $pdo, whether it was initialized on this call or a previous one // but it could have experienced a disconnection try { echo "Testing connection...\n"; $old_errlevel = error_reporting(0); self::$pdo->query("SELECT 1"); } catch (PDOException $e) { echo "Connection failed, reinitializing...\n"; self::init(); } error_reporting($old_errlevel); return self::$pdo; } protected static function init() { try { echo "Opening new connection...\n"; self::$pdo = new PDO('mysql:host=huey;dbname=test', 'root', 'root'); self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { die($e->getMessage()); } } } 

Use it as follows:

 echo "Query for 2:\n"; $pdo = DB::getConnection(); echo $pdo->query("SELECT 2")->fetchColumn() . "\n"; echo "\nSleeping 10 seconds...\n"; sleep(10); /* meanwhile I use another window to KILL the connection */ echo "\n"; echo "Query for 3:\n"; $pdo = DB::getConnection(); echo $pdo->query("SELECT 3")->fetchColumn() . "\n"; 

Output:

 Query for 2: Opening new connection... Testing connection... 2 Sleeping 10 seconds... Query for 3: Testing connection... Connection failed, reinitializing... Opening new connection... 3 

You can see that it detects that the connection failed and is reinitialized.