0

I am using PDO to connect to a Postgresql DB, using PDO::ATTR_PERSISTENT attribute which does speed up the application noticeably.

However I encountered a problem, after debugging it, found that the connections to DB die about 2 hours 11 minutes after the last SQL query executed! The fault is at the Firewall between the servers, but the networking guy refuses to have the limit higher than 24 hours (saying it would slow down the firewall) while I need it to be disabled.

I cant refresh the pooled connections by simple usage, because I cant control or ensure that ALL the pooled connections are served on a rotating base, ensuring no connection would die (causing hard to debug issues later when there is load on the server, and some connections are dead, while others are alive).

So, does anyone have any suggestion on the problem? how to keep the pooled connections alive, other than forcing the Networking guy to disable this silly timeout rule for those 2 specific servers?

Thanks!

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Carmageddon
  • 2,627
  • 4
  • 36
  • 56
  • I don't understand the problem at all. What's wrong with dead connection? Is it online script you are talking about, or some sort of demon, connected to database permanently? – Your Common Sense Oct 08 '13 at 14:44
  • @YourCommonSense Read about persistent connections http://php.net/manual/en/pdo.connections.php – Marek Oct 09 '13 at 07:14
  • @Marek ok, I read. What's next? Can you answer questions I asked the OP? – Your Common Sense Oct 09 '13 at 07:34
  • @YourCommonSense OP explained it well. There's a connection pool (TCP/IP connections) to Postgresql. If a connection is not used for over 2 hours, it's removed from the firewall's tables, is dead. This is not realized by the client until the connection is used, hence it's the clients responsibility to handle this situation and reconnect. – Marek Oct 09 '13 at 07:38

1 Answers1

-2

Use

SELECT 1

as the first query, if that fails, reconnect.

Marek
  • 7,337
  • 1
  • 22
  • 33
  • I cant reconnect, because the connection is given to the script by the resource pool. The pool is simply not aware that the connection is dead, even after attempting any SQL statement - next execution would still yield errors. I have found no other way to solve this except restart apache. – Carmageddon Oct 09 '13 at 07:45
  • 1
    If $con is the connection you have, after catching an error, assign to $con new PDO object. That should reopen the connection. I verified it now, but only for regularly killed connections. You can also try $con = null before making new PDO. – Marek Oct 09 '13 at 08:12