Connect To Multiple Databases In Loop

Yii 1.1 General Discussion
1

Hi guys,

I have a main database (called dbmain) containing records of my customers.

Each customer has a customer number (1, 2, 3,) and his own database (dbtenant1, dbtenant2, dbtenant3,).

In the config file, I have two database components called db and db2 and both are connected to dbmain.

db -> dbmain

db2 -> dbmain

After a user has login, a behaviour changes the connection of db2 to the user’s branch’s dbtenant.

db -> dbmain

db2 -> dbtenant3

This all works nicely.

But when a user forgets/resets his password, I want to make him inactive in all dbtenant databases, to force him to regain access via his branch’s User Manager (for security reasons).

So I get an array of the existing branches, and simply loop through them, using their branch_id to connect to each branch’s own database.

(At this stage the user is not login - so db2 is still connected to dbmain.)




foreach($branchesToUpdate as $branchToUpdate)

{

	/* Create a new connection. */

	$database = 'dbtenant' . $branchToUpdate->branch_id;

	$dsn = 'mysql:host=' . $branchToUpdate->tenant_db_host . ';dbname='.$database;

	$dbUsername = Yii::app()->db2->username;

	$dbPassword = Yii::app()->db2->password; 

								

	/* Override existing db2 component */

	try{

		$component = Yii::createComponent(array(

			'class'=>'CDbConnection',

			'tablePrefix' => 'cap_',

			'connectionString' => $dsn, 

			'emulatePrepare' => true, 

			'username' => $dbUsername,

			'password' => $dbPassword,

			'charset' => 'utf8', 

			'enableParamLogging'=>true, 

		));

		Yii::app()->db2->setActive(FALSE);

		Yii::app()->setComponent('db2',$component); 

		Yii::app()->db2->setActive(TRUE);

	}

	catch (Exception $e) 

	{

		throw new Exception();

	}

			

	/* All okay. Make the user inactive here - in the current db2 */

}

The code is working and there are no exceptions. The user is made inactive in dbtenant1. (Remember that db2 was first connected to dbmain - so it is impossible to make the user inactive in dbtenant1 if the above code was not working.)

The problem is that the user is only made inactive in dbtenant1 and not in the subsequent dbtenant databases.

Debugging showed that $component is generated correctly; changing from dbtenant1 to dbtenant2 etc. with each loop.

But it is almost as if the following is working only once, because db2 stays connected to dbtenant1.

Yii::app()->db2->setActive(FALSE);

Yii::app()->setComponent(‘db2’,$component);

Yii::app()->db2->setActive(TRUE);

Any ideas?

2

Perhaps try unsetting the component after you have completed the inactivation? (I’m just guessing here). Yii::app()->setComponent(‘db2’, null) would do that.

I don’t know why it’s not working, but why even set db2 at all, why not just use $component to inactivate your user? If your logic isn’t complicated to inactivate a user then you can just use sql commands to do so, as a last resort.

3

Hi MewsicLovr

Cool name. I have a Cort accoustic electric.

Ehm… oh yes, the database.

I change db2 so that AR could work. The models are directly linked to db2. So the only way to get the models to link with another database is via db2 - I think.

I will check out your other suggestions.

Many thanx

4

I made 3 copies of the above code and hardcoded the database names:


foreach($branchesToUpdate as $branchToUpdate) 

{ 

        /* Create a new connection. */ 

        $database = 'dbtenant1'; /*** HARDCODED ****/

        $dsn = 'mysql:host=' . $branchToUpdate->tenant_db_host . ';dbname='.$database; 

        $dbUsername = Yii::app()->db2->username; 

        $dbPassword = Yii::app()->db2->password;  

                                                                 

        /* Override existing db2 component */ 

        try{

                $component = NULL; 

                $component = Yii::createComponent(array( 

                        'class'=>'CDbConnection', 

                        'tablePrefix' => 'cap_', 

                        'connectionString' => $dsn,  

                        'emulatePrepare' => true,  

                        'username' => $dbUsername, 

                        'password' => $dbPassword, 

                        'charset' => 'utf8',  

                        'enableParamLogging'=>true,  

                )); 

                Yii::app()->db2->setActive(FALSE);

                Yii::app()->setComponent('db2',null); 

                Yii::app()->setComponent('db2',$component);  

                Yii::app()->db2->setActive(TRUE); 

        } 

        catch (Exception $e)  

        { 

                throw new Exception(); 

        } 

                         

        /***** Make the user inactive here - in the current db2 *****/

        /************************************************************/


        /* Create a new connection. */ 

        $database = 'dbtenant2'; /*** HARDCODED ***/

        $dsn = 'mysql:host=' . $branchToUpdate->tenant_db_host . ';dbname='.$database; 

        $dbUsername = Yii::app()->db2->username; 

        $dbPassword = Yii::app()->db2->password;  

                                                                 

        /* Override existing db2 component */ 

        try{ 

                $component = NULL;

                $component = Yii::createComponent(array( 

                        'class'=>'CDbConnection', 

                        'tablePrefix' => 'cap_', 

                        'connectionString' => $dsn,  

                        'emulatePrepare' => true,  

                        'username' => $dbUsername, 

                        'password' => $dbPassword, 

                        'charset' => 'utf8',  

                        'enableParamLogging'=>true,  

                )); 

                Yii::app()->db2->setActive(FALSE);

                Yii::app()->setComponent('db2',null);

                Yii::app()->setComponent('db2',$component);  

                Yii::app()->db2->setActive(TRUE); 

        } 

        catch (Exception $e)  

        { 

                throw new Exception(); 

        } 

                         

        /***** Make the user inactive here - in the current db2 *****/

        /************************************************************/


        /* Create a new connection. */ 

        $database = 'dbtenant3'; /*** HARDCODED ***/

        $dsn = 'mysql:host=' . $branchToUpdate->tenant_db_host . ';dbname='.$database; 

        $dbUsername = Yii::app()->db2->username; 

        $dbPassword = Yii::app()->db2->password;  

                                                                 

        /* Override existing db2 component */ 

        try{ 

                $component = NULL;

                $component = Yii::createComponent(array( 

                        'class'=>'CDbConnection', 

                        'tablePrefix' => 'cap_', 

                        'connectionString' => $dsn,  

                        'emulatePrepare' => true,  

                        'username' => $dbUsername, 

                        'password' => $dbPassword, 

                        'charset' => 'utf8',  

                        'enableParamLogging'=>true,  

                )); 

                Yii::app()->db2->setActive(FALSE);

                Yii::app()->setComponent('db2',null); 

                Yii::app()->setComponent('db2',$component);  

                Yii::app()->db2->setActive(TRUE); 

        } 

        catch (Exception $e)  

        { 

                throw new Exception(); 

        } 

                         

        /***** Make the user inactive here - in the current db2 *****/

        /************************************************************/

}

This brought no luck. Only dbtenant1 got updated, with dbtenant2 and dbtenant3 left unchanged.

If you switch the code around and start with dbtenant3, then only dbtenant3 gets updated, with dbtenant2 and dbtenant1 left unchanged.

I don’t use transactions here - so it can’t be a rollback somewhere.

And each time I set both $component and db2 to null, so they were freshly recreated.

Any other ideas? Maybe MySql keeping track of the connection, or something?

5

Could someone try to replicate the problem please.