Restore timed out connection

Question

Restore timed out connection

tobispace Jan '18

Created Jan '18	Last Reply Jan '18	Replies 3	Views 955	Votes 0

tobispace 12.9k

Jan '18

Hello,

I have some long-running scripts which may take 5 minutes to many hours to complete. Those scripts work like that:

Perform a query using a Model (instantiate it with $model = new Model() and then ->save() it)
Launch the execution of an external long running script (which does nothing on the application and does not use any Model or database connection)
Perform a query on that same Model (for example $model->setParam( 1 )->save();)

The Database server has a wait_timeout for connection with a value of 600 seconds (10 minutes).

Before executing instruction 3 I perform this:

$this->db->connect( $config->Database->toArray() );

in order to restore the connection (ref. https://olddocs.phalcon.io/en/latest/api/Phalcon_Db_Adapter_Pdo.html#methods )

I even tried that:

$db = $model->getWriteConnection();
$db->connect( $config->Database->toArray() );

But I always get this error when executing connect():

Phalcon\Db\Adapter\Pdo::connect(): send of 5 bytes failed with errno=32 Broken pipe

I could raise the value of wait_timeout (for example to 8 hours) but the point is that I can not arbitrarily set a value that can always be valid.

I want my wait_timeout parameter to be "some value" not depending on long running scripts.

I just need to be able to reconnect the application to the database without returning that error.

How can I do?

Thank you

Lajos Bencz
77.7k

Accepted
answer

edited Jan '18
Jan '18

I've created a ping method for the default Mysql adapter:

class MysqlAdapter extends \Phalcon\Db\Adapter\Pdo\Mysql {
    public function ping() {
        try {
            $this->fetchAll('SELECT 1');
        }
        catch (\Exception $e) {
            $this->connect();
        }
        return $this;
    }
}

Make sure to instantiate this new class in your services, then before each long running query, issue a $this->db->ping()

tobispace · Answer 1 · 2018-01-02T10:03:13-07:00

I've created a ping method for the default Mysql adapter:
class MysqlAdapter extends \Phalcon\Db\Adapter\Pdo\Mysql {
   public function ping() {
       try {
           $this->fetchAll('SELECT 1');
       }
       catch (\Exception $e) {
           $this->connect();
       }
      return $this;
   }
}
Make sure to instantiate this new class in your services, then before each long running query, issue a $this->db->ping()

Hello,

thank you for your answer. Anyway I can't understand how this can help me. My script is like that:

query with model
long running command (it is an exec() on the server) which has nothing to do with my application, models, database, etc. (imagine that I run a sleep( 30000 ) in this step)
another query with the same model instance used in step 1

Should I run your ping() before or after my step 2? If yes, it's useless because the connection is still alive before 2. If I run ping() after step 2, it's simething I already tried to do (acutally I do ->connect() after 2 at the moment) and doing ->connect() after 2 causes the error I reported here, both passing the connection parameters or not.

I mean: it is the instruction $this->db->connect() (or $this->db->connect( $parameters )) that causes the Broken pipe error...

Lajos Bencz · Answer 2 · 2018-01-02T10:55:02-07:00

The crucial part is catching the first Exception (broken pipe), then rebuilding the connection. Issuing a simple connect would give you a broken pipe error the same.

This ping method only hides the first broken pipe error, then connects again. And yes, you should run this after step 2 (the long running process).

EDIT: Oh, and you also have to set up PDO with the PDO::ERRMODE_EXCEPTION attribute

Jonathan Aaron Steel · Answer 3 · 2018-01-03T00:59:05-07:00

The crucial part is catching the first Exception (broken pipe), then rebuilding the connection. Issuing a simple connect would give you a broken pipe error the same.

That's not quite correct. I have had same issue in the past and exactly I was able to tackle it with simple connect() call when RDBMS goes over the bridge.

tobispace · Answer 4 · 2018-01-03T00:59:54-07:00

The crucial part is catching the first Exception (broken pipe), then rebuilding the connection. Issuing a simple connect would give you a broken pipe error the same.

Yes, you're right. Any new query, including a call to connect(), causes the broken pipe exception to be thrown. After that, executing connect() "once more" made the connection to be restored. Thank you!