Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Restore timed out connection

Hello,

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

  1. Perform a query using a Model (instantiate it with $model = new Model() and then ->save() it)
  2. Launch the execution of an external long running script (which does nothing on the application and does not use any Model or database connection)
  3. 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.phalconphp.com/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



43.4k
Accepted
answer
edited 13d ago

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()



4.7k
edited 13d ago

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:

  1. query with model
  2. 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)
  3. 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...

edited 13d ago

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

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.



4.7k

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!