[DigitalAlien]

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

[DigitalAlien]

Anybody?

DigitalAlien, on 18 December 2012 - 08:53 AM, said:

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

[Da:Sourcerer]

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.

[DigitalAlien]

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]

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).

[DigitalAlien]

Thanks! i will check those out.
In the meantime, does anyone have any suggestions for doing this in Yii?

[Da:Sourcerer]

DigitalAlien, on 19 December 2012 - 10:58 AM, said:

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

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 ...

[DigitalAlien]

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.

[DigitalAlien]

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]

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

[DigitalAlien]

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]

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

thanks