How to use retryHandler

[EtienneBruines]

Sometimes, connections to MySQL servers aren't the way you want it to be:

• MySQL server has gone away
• SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

Since this can happen almost anywhere, I thought I'd see what Yii2 allows me to configure. Stumbled upon the setRetryHandler method, which allows me to indicate whether to retry the pdo command. Great!

My initial thoughts were this:

return [
    'components' => [
        'db' => [
            'class' => \common\components\RetryingDBConnection::class,
            'dsn'   => 'mysql:host=mysql;port=3306;dbname=mydb',
            'username'   => 'root',
            'password'   => 'super-secure-password',
            'charset'    => 'utf8',
            'commandMap' => [
                'mysql' => [
                    'class' => \yii\db\Command::class,
                    'retryHandler' => static function (\yii\db\Exception $e, $attempt) {
                        if (str_contains($e->getMessage(), 'MySQL server has gone away')) {
                            Yii::$app->getDb()->close();
                            Yii::$app->getDb()->open();

                            return true; // retry
                        }

                        if (str_contains($e->getMessage(), 'try restarting transaction')) {
                            return true; // retry
                        }

                        return false; // do not retry, but throw error
                    }
                ]
            ],
        ],
    ],
];

And even though this configures the $config variable within Yii::$app->db->createCommand, it does not set the _retryHandler for the resulting $command = Yii::createObject($config)

How does one use this retryHandler? I've seen no documentation and #15924 (comment) doesn't seem practical to use, since I'd have to do this for every use of createCommand.

[WinterSilence]

Deadlock is not server "gone away", is "busy", PDO tells you what to do: try restarting transaction. Also, you can enable autocommit to minimize deadlocks.

[EtienneBruines]

Thank you for your reply 😄

Deadlock is not server "gone away", is "busy"

Correct. But when figuring out how how to setup the retryHandler, one might as well include those "gone away" errors since they can occur with PDO::ATTR_PERSISTENT and restarting MySQL servers.

PDO tells you what to do: try restarting transaction.

I agree. I was hoping to do so using this retryHandler Yii has.

Also, you can enable autocommit to minimize deadlocks.

In MariaDB Galera cluster setups (multi-master), commits can happen on different servers in parallel and therefore cause deadlocks - even with autocommit enabled. Even a simple "atomic" SQL command like UPDATE mytable SET v = v+1 WHERE id = ? can already deadlock if run on multiple nodes in parallel.

[WinterSilence]

nope, I'm mean reconnect at lock-down is not "right way", you must sleep/wait, $attempt counter using to limit cycles, explain

yii2/framework/db/Command.php

Line 1288 in 4c1cae3

protected function internalExecute($rawSql)

By analogy: you go to toilet, but toilet room is busy, you wait when room is free, you can "reconnect": go outside from building and and come back, but it's not guarantees what's toilet is free now.

do you tried optimistic locks?

[EtienneBruines]

When two master-MySQL-nodes have a transaction at the same time accessing the same data (e.g. cell), only one of them will commit successfully, while the other will ROLLBACK with this error: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

This happens because both master-nodes will only do a local check to see if it's possible - and it always is, but then once they mitigate/synchronize to other nodes, the transaction itself will be rolled back when it conflicts with other nodes. Restarting the transaction, as PDO suggests, is indeed a useful thing to do. That will just attempt to commit again at the master-node, and will then hopefully not ROLLBACK again when it attempts to synchronize to other nodes - especially with a single command instead of a big transaction in PHP.

do you tried optimistic locks?

In the link it states:

it would be desirable to have a way to prevent him from saving the article and show him some hint message.

That is exactly what the Galera multi-master errors do - whenever there is two processes accessing/modifying the same data, one of them is being rejected / with ROLLBACK. I now need to simply "retry" that, because I'm working on (atomic) counters. Multiple files being uploaded in parallel (each with their own PHP-FPM process), and a database-column keeping track of how many files are left.

go outside from building and and come back, but it's not guarantees what's toilet is free now.

True, there is no guarantee. But as long as PDO suggests I try again, I can keep trying again.

The full-on reconnecting should only happen on MySQL server has gone away which is entirely different (e.g. MySQL connection dropped) - the try restarting transaction only retries the command and does not attempt to reconnect.

[WinterSilence]

But as long as PDO suggests I try again, I can keep trying again.

I mean, you can wait or reconnect, but reconnection use more RAM

I never used this before, but I think it's must be something like this:

static function (\yii\db\Exception $e, int $attempt): bool {
    if ($attempt < 10) { // to break infinity cycle
       \usleep(100); // waiting before check unlock, 1s is 1000000ms
       return true;
    }
    return false;
}

[EtienneBruines]

I ended up creating my own Command class extending yii\db\Command:

<?php

namespace common\components;

use yii\db\Command;

/**
 * Retries commands if they fail with MySQL gone away or retrying transaction.
 */
class RetryingCommand extends Command
{
    public function init()
    {
        parent::init();
        $this->setRetryHandler(static function (\yii\db\Exception $e, $attempt) {
            if (str_contains($e->getMessage(), 'MySQL server has gone away')) {
                // The MySQL connection got dropped / disconnected, so let's reconnect 
                \Yii::$app->getDb()->close();
                \Yii::$app->getDb()->open();

                return true; // retry the SQL command
            }

            if (str_contains($e->getMessage(), 'try restarting transaction')) {
                // Deadlock issues with Galera cluster - two commands updating the same data in parallel
                return true; // retry the SQL command
            }

            return false; // do not retry, but throw error
        });
    }
}

And then specifying it in the config:

<?php 

return [
    'components' => [
        'db' => [
            'class' => \yii\db\Connection::class,
            'dsn'   => 'mysql:host=mysql;port=3306;dbname=db1',
            'username'   => 'root',
            'password'   => 'super-duper-secure-password',
            'charset'    => 'utf8',
            'attributes' => [
                PDO::ATTR_PERSISTENT => true,
            ],
            'commandClass' => \common\components\RetryingCommand::class,
        ],
   ] ,
];

[ghost]

This is almost right but doesn't quite work because the prepared query already has the database connection inside of it (so it will fail forever because the old connection is gone), so after the DB reconnects, we actually have to re-create the query using the new database connection.

Here's a working version I used to stop our scripts from crashing when the VPN disconnects:

/* hack str_contains into the current environment if it doesn't exist */
if (!function_exists('str_contains')) {
    function str_contains (string $haystack, string $needle)
    {
        return empty($needle) || strpos($haystack, $needle) !== false;
    }
}

assert(function_exists('str_contains'));

/* Adapted from: https://github.com/yiisoft/yii2/discussions/19429#discussioncomment-2980120 */
class RetryingCommand extends yii\db\Command
/**
 * Retries commands instead of crashing scripts when DB server connections drop.
 */
{
    public function init()
    {
        parent::init();

        /* need to capture the class external to retry handler so we can use it inside the retry handler since
         * retry handler doesn't have a "$this" in itself... */
        $parent = $this;

        $this->setRetryHandler(function (\yii\db\Exception $e, $attempt) use ($parent) {
            echo "Retrying (", Yii::$app->db->dsn, ") due to mysql error: ", $e->getMessage(), " :: ", $attempt, "\n";
            if (str_contains($e->getMessage(), 'MySQL server has gone away')) {
                // The MySQL connection dropped / disconnected, so try to reconnect and fix the stalled query

                /* alert arbitrary number alert */
                if ($attempt > 20) {
                    // too many retries for the same statement, so just crash the operation.
                    return false;
                }

                while (true) {
                    try {
                        Yii::$app->db->close();
                        Yii::$app->db->open();
                        echo "Connected!\n";

                        /* pause a little in case there's a looping error here too */
                        usleep(3000);

                        // Now re-prepare the query because the retry is against the previously prepared
                        // query which already has the (now closed) connection inside of it, so re-prepare
                        // the query with our newly reconnected DB.
                        $parent->cancel();

                        /* awful hack to reset the query against NEW DB CONNECTION because the regular
                         * behavior keeps queries tied to the orginal connection (which is gone now), so
                         * we have to generate a fake prepare() logic */
                        /* Adapted from logic here, except we can't use built-in parameter rebinding because
                         * the FIRST TIME parameters are bound, the parameters are deleted, so we must extract
                         * the already created SQL and inject it into the new DB connection prepared query
                         * statement thing:
                         * https://github.com/yiisoft/yii2/blob/4628b91e739c93b8bbda0ba107e3a2a1fc474d85/framework/db/Command.php#L251-L275 */
                        $pdo = $parent->db->getMasterPdo();
                        $parent->pdoStatement = $pdo->prepare($parent->getRawSql());
                        echo "Query re-prepared!\n";

                        return true;
                    } catch (\Exception $opener) {
                        echo "Failed to connect: ", $opener->getMessage(), "\n";
                        sleep(1);
                    }
                }
            }

            if (str_contains($e->getMessage(), 'try restarting transaction')) {
                // auto-retry against deadlock issues - two commands updating the same data in parallel
                return true; // retry the SQL command
            }

            return false; // do not retry, but throw error
        });
    }
}

[WinterSilence]

@mattpeople echos and sleep(1); looks strange, $parent = $this; - non-static closure use parent scope

[ghost]

Yes, this was just a quick hack to fix our manual maintenance scripts from failing continuously (because if the DB connection drops at any point, then an entire 6 hour script fails). Probably not a great idea to use this in a real application since it could cause duplicate writes with no feedback.