Database Fail Over


(Amallonee) #1

Hello all! I am currently working on a project for which we have chosen Yii as our new Framework of choice. I am currently trying to figure out the best way to implement some sort of automatic database fail over in Yii. My thought so far is to over-ride the getDBCOnnection() in CActiveRecord, but I am not sure if this is the correct path to take. Basically what I am looking to do is check a DB connection and if it fails connect to another DB. Simple concept I am just not sure where to put it. I know there are better ways to do this by using mysqlnd_ms but it is not setup on the servers we are using yet so have to come up with a way to do this in Yii. Any help is greatly appreciated. -DA


createUrl() not encoding the name value in 1.1
(Amallonee) #2

Anybody?


(Da:Sourcerer) #3

Something like HAProxy is not an option? Also I’m pretty sure your approach is a bit off. If I were you, I’d start by extending CDbConnection instead.


(Amallonee) #4

Hey, thanks so much for the reply.

So, I am not familiar with HAProxy and how it could help me, I will however look into it.

The thing is, we already have servers with stuff on them, I am not in the networking department so load balancing is basically up to them. That being said, our old framework had DB fail over in the code and I have been tasked with doing that same thing in Yii. I need to know how to best approach it from that perspective, but also can recommend better ways if they are out there.


(Da:Sourcerer) #5

HAProxy used to be a tcp/http load balancer. But it is also good for mysql failovers. There’s also mysql-proxy, which is operating closer to the MySQL protocol and might be even better (although it’s in alpha stage).


(Amallonee) #6

Thanks! i will check those out.

In the meantime, does anyone have any suggestions for doing this in Yii?


(Da:Sourcerer) #7

Yes. As I wrote above: Extend CDbConnection so you can specify multiple connection params. I remember somebody implementing split read/write like that. Unfortunately I cannot find that solution again …


(Amallonee) #8

I did see something like that somewhere also, but it was changing core Yii stuff and also not exactly what I am looking for, I don’t think.

So, if I took the route of extending CDbConnection, I am not sure exactly what methods I would change. I wouldn’t know where to begin honestly. Where in CDbConnection does it get the config variable and connect to the DB? I assume that is where I would say “test connection” if it can’t connect try the other.


(Amallonee) #9

So this is what I have come up with so far. I’d love to get some thoughts or suggestions on whether this is the correct approach.


class DaDbConnection extends CDbConnection{


public $dbConnectTries = 6;

public $numDatabases = 3;

private $_tries =0;

private $_db = 1;


/*

 * Extends CDbConnection open() method

 * Tries to connect to database connections setup in config/main.php up to 

 * the value of $dbConnectionTries or a connection is successful

 * @throws CException If it can not connect to any DBs

 */

protected function open()

{

    try{

        //try to connect to the default DB

        parent::open();


    }catch(Exception $e){

        if($this->_tries < $this->dbConnectTries){


            //If there aren't anymore DBs to try we must start over from the first

            if($this->_db >= $this->numDatabases){

                $tryDb = 'db';

                $this->_db = 0;

            }else{

               $tryDb = 'db'.$this->_db;

            }


            $this->_db++;

            $this->_tries++;


            $this->connectionString = Yii::app()->$tryDb->connectionString;

            $this->username = Yii::app()->$tryDb->username;

            $this->password = Yii::app()->$tryDb->password;

            $this->open();

        }else{

            throw new CDbException('Could Not Connect to a DB.');

        }

    }    

}


(Da:Sourcerer) #10

You do not happen to have read this blog post recently? Anyway: In the Yii world, this does not look like a sound approach.


(Amallonee) #11

I am fairly new to Yii, so I am confused on what the Yii approach would look like. I really hope someone could point me in the correct direction more directly. I looked at that blog post, but it doesn’t look to be specifically for Yii I will look at it more closely to see if it would be of any help.

Thanks!


(Maxxer) #12

hi. did you manage to solve your issue? I’m interested too…

thanks