Propel in PHP Interactive with Database Timeout

Question

How to make the MySQL Database always available during use the PHP Interactive Shell?

I have the problem that I connect to DB at the beginning and it timeout in shortly. System Admin set the timeout of MySQL database to 10 seconds (idle) and I am not be able to change that value. However this value is not long enough to interactive with Propel object with the shell in time. I look for the way without change the configuration at MySQL server.

For example

// Assume that User is the Propel class
$con = User::create();
$first = $con->findPk(2); // Still alive
sleep(11);
$second = $con->findPk(1); // Dead -- Return NULL

Details of MySQL variable (note that I am not allow to change those variable)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    |
| delayed_insert_timeout     | 300   |
| innodb_lock_wait_timeout   | 360   |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout        | 28800 |
| net_read_timeout           | 30    |
| net_write_timeout          | 60    |
| slave_net_timeout          | 3600  |
| table_lock_wait_timeout    | 50    |
| wait_timeout               | 28800 |
+----------------------------+-------+

Please add the output of `show variables like "%timeout%";` to your question. Also remove the `sleep(11)` from the code, but you've done that probably only to demonstrate your issue ;) — hakre, Nov 09 '12 at 08:42
Have you tried to increase the [`connect_timeout`](http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_connect_timeout) by [setting the global variable](http://dev.mysql.com/doc/refman/5.0/en/using-system-variables.html)? — hakre, Nov 09 '12 at 08:48
Sorry, as question. I am not allow to change those variable. — scalopus, Nov 09 '12 at 09:57
What you describe in your question looks more like a `wait_timeout` to me (not saying it is, but:). Which exact error(s) are you getting? — hakre, Nov 09 '12 at 09:59

nikita2206 · Answer 1 · 2012-11-09T11:23:21.670

If you really can't solve this on database side, then there is workaround. Sadly I don't know how to make it with Propel2 (I did something similar with Doctrine2), but maybe someone will suggest it.

The idea is to create connection class that Propel will use instead of default one. In this class you will have to make sure that connection is still alive on every query and if it isn't - create new connection (all this should be made silently).

That will look something like this:

class Connection
{
    // ...
    public function query($stmt)
    {
        $result = $this->pdo->query($stmt);
        if (!$result) {
            $this->reconnect();
            $result = $this->pdo->query($stmt);
        }
        return $result;
    }
    // ...
}

PS Again - you shouldn't do it if you can solve it on database side

Propel in PHP Interactive with Database Timeout

1 Answers1