Db connection failover

[jakubriedl]

It would be great to have failover option for db connections in Laravel. So when connection to primary db fail it tries secondary connection. This will make creating HA services much easier. And maybe if possible the same thing in queue drivers.

[ipalaus]

Yeah definitely a nice addition to DB now that we have write/read connections.

Sent from my iPhone

On 26/11/2013, at 22:01, Jakub Riedl notifications@github.com wrote:

It would be great to have failover option for db connections in Laravel. So when connection to primary db fail it tries secondary connection. This will make creating HA services much easier. And maybe if possible the same thing in queue drivers.

—
Reply to this email directly or view it on GitHub.

[taylorotwell]

Give me some proposals for what it would look like, etc.

[ipalaus]

Something like @philsturgeon pointed on #5 (comment)

It doesn't even have to do something with the read/write thing, but it's useful to "detect" dead nodes on that scenario. Basically if the connection to the database fails, before throwing an error that the connection couldn't be stablished try for, let's say, 3 times. In the read/write scenario, I'll try to connect to a node until we run out of options. That's the only way to emulate what a load balancer would do.

PS: I would suggest adding a config param for this. If we have a big application and our database just went out, I can't imagine traffic * 3 trying to access to the database...

[jakubriedl]

I think that ideal will be making config more flexible so it could look like this

'connections' => [
    'mysql' => [
        [
            'driver'   => 'mysql',
            'host' => 'primary',
            'actions' => 'read', //read, write, all (default)
            'database' => 'kapture',
            'username' => 'kapture',
            'password' => 'password',
            'prefix' => '',
            'charset'  => 'utf8',
            'collation'  => 'utf8_unicode_ci',
        ],
        [
            'driver'   => 'mysql',
            'host' => 'secondary',
            'actions' => 'all',
            'database' => 'kapture',
            'username' => 'kapture',
            'password' => 'password',
            'prefix' => '',
            'charset'  => 'utf8',
            'collation'  => 'utf8_unicode_ci',
        ]
    ],
],

and when primary actions=>'read' connection fails, use the next with enabled read actions and so on until all was be used. This will gave us

• in master -> slave scenario connect to master if slave fails
• have multiple masters with fallback
• have multiple slaves
• prioritize some connection over another one

and maybe some another which didn't occurred to me now

[mandersondesign]

Would you be able to accomplish this same thing via a load balancer?

I can see the issue if you only have a single read slave setup and, obviously, this gives you more options if you are not wanting to run a load balanced setup.

[philsturgeon]

Failover != load balancer.

Load balancer spreads the load, failover says "AHH THIS BROKE, use something else!"

The two are not mutually exclusive, but not the same thing at all.

[mandersondesign]

@philsturgeon Thinking this through a bit more, I see where I was confused. In our NGINX load balancer for our read slaves, we have it setup to detect whether an instance is up before sending a connection that direction. A really naive "failover" for the read side of things. I now very much see the value of this feature in Laravel and seemingly need to do some thinking on our failover plans. Thanks.

[philsturgeon]

Your system sounds great. This is for people that don't have such mature setups.

Phil Sturgeon

On Wednesday, December 4, 2013 at 3:55 PM, Michael Anderson wrote:

@philsturgeon (https://github.com/philsturgeon) Thinking this through a bit more, I see where I was confused. In our NGINX load balancer for our read slaves, we have it setup to detect whether an instance is up before sending a connection that direction. A really naive "failover" for the read side of things. I now very much see the value of this feature in Laravel and seemingly need to do some thinking on our failover plans. Thanks.

—
Reply to this email directly or view it on GitHub (#2808 (comment)).

[taylorotwell]

No plans to implement this currently.

[GrahamCampbell]

Oh noes. :(

[taylorotwell]

I mean if someone can work up an awesome and elegant pull request, go for it. I just mean I'm not doing it right now.

[philsturgeon]

Seems fair. 

[andrewmclagan]

I'd love to bump this, or even contribute to a PR but very time pressed. I do not like the idea of booting an nginx instance simply for failover, will add to latency.

[underwood]

Just wanted to bump this item.

Having a master and slave databases is pretty common.

How about adding something like this to the /config/database.php to specify the fail over database(s)?

'default' => env('DB_CONNECTION', 'mysql-master'),
'failover' => env('DB_CONNECTION', 'mysql-slave'),

Maybe even add a stack like we have with logging now where we can specify multiple slave databases.

All writes should go to the default connection and it would fail over to read only if the default database is unavailable.

[andrewmclagan]

The more I think on this topic, the more I actually believe its out of Laravel's scope. This is the domain of operations and should not enter the domain of software - especially the application layer. There are so many ways to achieve this outside Laravel I cant see the value in an implementation.