PDO Connection Timeouts and Aura.Sql

For today's PHP developer there is only one way to code a connection to a database: PDO, an access abstraction layer that is a common extension to the core. It works across different databases (MySQL, MSSQL, PostgreSQL, etc), is class-based, and is also fairly lightweight. If a developer wants an easy way to connect to their database, PDO is the way to go. Unless they're using a library that completely wraps and abstracts out the actually connection, like I did with my pushup bot.

Being a big fan of the Aura project I decided to use Aura.Sql for my bot's data connection. Among other cool ideas this library extends PDO (instead of wrapping it up), giving you all of the familiar methods plus a whole bunch more. I was particularly enjoying the different fetch*() methods as an easier way to pull a single row, value, or column of entries. However, I started to notice a weird error showing up in my logs for a daemon script, an error about the MySQL host not being available. Something weird was going on that was causing my bot to malfunction.

My first thought was connection timeouts. After all, this is a daemon that runs continuously in the background gobbling up a user stream. But that didn't make any sense, as Aura.Sql was calling a central connect() method before performing any queries. This was one of the nice things about extended version - in vanilla PDO, calling the new object created the connection right there. Aura.Sql's ExtendedPDO chooses instead to store the credentials during construct and wait to connect until you actually need to perform an operation.

The key problem was in how PDO itself handles a connection. When you create a new object, a connection to the defined database is made. If the connection times out, things will start to fail. It won't try to reconnect or invalidate the object, things will just start to fail. The only way around this is to catch the failures and reconnect or be proactive and disconnect manually… By setting the reference to null.

So, Aura.Sql was creating a PDO instance with ExtendedPdo::connect(), passing the instance around, and when it inevitably timed out during the daemon run then ExtendedPdo would start to throw errors. All those central connect() methods were not helping me out, as the object was still valid for each call and wouldn't trigger a reconnect. So, I needed to find a way to kill the connection (set it to null) after I was done with it so than the the connect() method could rebuild it. But ExtendedPdo didn't give me direct access to the internal connection. It was pull request time.

As of Aura.Sql 2.3.0 you can now call disconnect() to nullify the internal PDO instance. And my pushup bot is able to connect, do stuff, and disconnect for each event thrown at the daemon. In the end this little bug gave me a chance to learn a little bit more about long-lived database connections and made my bot a little bit more robust!