Is there a possibility to configure a fallback database write host to laravel instead of randomly ch-CodePudding

We have a Galera DB cluster running 3 nodes and a load balancer acting as a intermediary between the nodes. Our application running multiple instances and has a lot of read/writes to the database so we ran into problems with deadlocks when using the loadbalancer for writes. We workaround the problem by selecting one of the 3 nodes specific for writing and still using the loadbalancer for reading.

Now I'm looking for a solution to setup a fallback connection host when there is a problem connecting to the write node so we have no downtime. I did a lot of research and i found out about this method in Illuminate\Database\Connectors\ConnectionFactory:


/**
 * Create a new Closure that resolves to a PDO instance with a specific host or an array of hosts.
 *
 * @param  array  $config
 * @return \Closure
 */
protected function createPdoResolverWithHosts(array $config)
{
    return function () use ($config) {
        foreach (Arr::shuffle($hosts = $this->parseHosts($config)) as $key => $host) {
            $config['host'] = $host;

            try {
                return $this->createConnector($config)->connect($config);
            } catch (PDOException $e) {
                continue;
            }
        }

        throw $e;
    };
}

I can provide multiple hosts to the write configuration but Laravel will randomly decide which one to use.

'write'     => [
    'host' => [ "node1", "node2" ],
],

I just want to always use 1 node for writes and if there is a problem with the connection i want to fallback to another one (second one). Does somebody know how i could achieve this? It seems I can't override the behavior of createPdoResolverWithHosts as it's not a service provider, but coded in the core of Laravel.

CodePudding user response：

So looking at the Illuminate\Database\Connectors\ConnectionFactory class, your question includes the createPdoResolverWithHosts() method; you can see this is called by createPdoResolver() when a hosts key exists in the config. But if that array key is missing, createPdoResolverWithoutHosts() is called instead, which directly calls the createConnector() method. That method returns an instance of the appropriate database connector class. Before it does that, however, it checks for any bound connectors:

if ($this->container->bound($key = "db.connector.{$config['driver']}")) {
    return $this->container->make($key);
}

So (with the caveat that this is completely untested) I would suggest the following:

Create a custom database connector, extending the default one. Put some custom logic into the connect() method, which gets the config array as its sole argument:

<?php

namespace App\Database;

use Illuminate\Database\Connectors\MySqlConnector as BaseConnector;

class MySqlConnector extends BaseConnector
{
    public function connect(array $config)
    {
        $primary = $config["primary_host"];
        $secondary = $config["secondary_host"];
        try {
            $config["host"] = $primary;
            return parent::connect($config);
        } catch (\Throwable $e) {
            $config["host"] = $secondary;
            return parent::connect($config);
        }
    }
}

Create a service provider to bind your custom DB connector:

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;

class CustomMysqlConnector extends ServiceProvider
{
    public function register()
    {
        $this->app->bind('db.connector.mysql', \App\Database\MySqlConnector::class);
    }
}

<?php

return [
    ...
    'providers' => [
        ...
        App\Providers\CustomMysqlConnector::class,
    ],
];

Unset the host key in your configuration array. Instead, use primary_host and secondary_host as used in the custom connector.