PDO connection timed out in API, php-fpm restart solves it

Question

I keep getting the following error from my code:

Database connect failed: PDO::__construct(): send of 12 bytes failed with errno=110 Connection timed out

This error persistently happens on an api. If that api keeps getting called a lot during the day this does not happen. Only when the api is not used for some time.

I can solve this by doing a php-fpm restart/reload, but this shouldn't be the solution.

Does anybody have any idea how to solve this?

--EDIT--

This is the code for connection to the database:

public function connectDatabase($allow_persistent = true)
{
    $this->db = null;

    $this->readINI();

    $pdo_attr = [
        PDO::ATTR_PERSISTENT => $allow_persistent,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8;",
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true,
    ];

    $this->db = new PDO("mysql:host=" . $this->db_data_dbhost . ";dbname=" . $this->db_data_dbname . ";charset=utf8", $this->db_data_username, $this->db_data_password, $pdo_attr);

    $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}

To itterate some more, this is used on two API's at the moment.

One is consistently used by customers and never encounters this problem
The second is not used by a lot of customers yet seeing as this is still in development, it's only with a few customers for testing purposes. We do try to push an app to the appstore but it gets shot down by this problem. This API will eventually run into the error as depicted above and will not recover from this state without restarting/reloading the php-fpm service.

@Federkun I added the code for the connection to the database, so yes if you follow the code — Wouter, Jun 28 '17 at 08:28
Just don't, http://php.net/manual/en/features.persistent-connections.php — Federkun, Jun 28 '17 at 12:36
I can't not use persistent connections. this is for an API and otherwise those API calls will take forever to collect the data. — Wouter, Jul 04 '17 at 09:05
See https://stackoverflow.com/questions/9736188/mysql-persistent-connection-vs-connection-pooling. — smcjones, Jul 24 '17 at 15:25
@smcjones doesn't really answer my question, I don't ask about multithreading. — Wouter, Jul 24 '17 at 15:31
Fair enough! Also check http://php.net/manual/en/pdo.connections.php, specifically the warning about ODBC. Also make sure `mysql.max_persistent` is consistent between your dev and prod servers. — smcjones, Jul 24 '17 at 15:43
@smcjones thx but of course I read it, but thanks for the options and at least trying to help :) — Wouter, Jul 24 '17 at 15:48

score 2 · Accepted Answer · answered Jul 18 '17 at 13:33

Using persistant connections is not that good (see why), but still, to solve this you may want to increase MySQL connections timeout.

To achieve this, see wait_timeout parameter for my.ini.

p.s. Also, you can catch this error and just reconnect to the database.

score 1 · Answer 2 · answered Oct 16 '18 at 04:23

After much testing and research we found this bug was causing the issue:

https://bugs.php.net/bug.php?id=64549

Our solution was to use the error control operator '@' on the PDO connect:

$connection = @new PDO(
    $connection_type.":dbname=".$dbname.";host=".$host,
    $username, 
    $password, 
    [
        PDO::ATTR_PERSISTENT => TRUE,
        PDO::ATTR_TIMEOUT => 15,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode='POSTGRESQL'"
    ]
);

If you are using your own error handler set_error_handler() you will need to ignore if error_reporting() level === 0.

// This handles the mysqlnd persistent connections bug when the @ symbol is used it and the error is suppressed
if ( 0 !== error_reporting() ) {
    // Do your regular customer error handling here
}else{
    // ignore this since it is throwing a false positive from the persistent connection bug
}

I hope this saves someone hours troubleshooting. I hope the people behind PHP figure out a solution for this bug. It has annoyed many people for years.

PDO connection timed out in API, php-fpm restart solves it

2 Answers2