How to re-connect to default db

Yii 1.1 General Discussion
1

Hi,

In config/main.php there’s a ‘db’ component with parameters and such, so in my app it is accessed as Yii::app()->db. So far, it is pretty standard. Now, question - how I can re-initialize it?

The problem is that I’m working on a complicated CLI script that at some point forks a number of kids to do some networking. If one of the kids unexpectedly dies, PHP does regular cleanup and closing descriptors including MySQL connection. After that the main script tries to do some db operations and got an error that db connection is gone away. Since this ->db property is a read-only, I can’t just open a new one and re-assign it.

So far my workaround is when I need to do some sql, I just create a new connection using CDbConnection.open. But there should be a proper way, right?

Again, the question is, how I can re-open the default db connection using parameters specified in the config file?

2

have you tried to set the active property to true?

3

Not for the main db. Does that going to help?

4

Just tried, doesn’t work.

In the db config:




	'class' => 'system.db.CDbConnection',

	'connectionString' => 'mysql:host=localhost;dbname=some_db',

	'emulatePrepare' => true, // TODO: ?

	'charset' => 'utf8',

	'active' => TRUE,

	// ...

The test script:




		Yii::import('ext.Benchmark');

		Yii::import('ext.Daemon');

		$daemon = new Daemon;

		$bench = Benchmark::getInstance();


		$bench->start('start db testing');


		// this one works:

		$r = Yii::app()->db->createCommand('select 2+2 as r')->queryRow();

		$bench->mark("2+2=" . $r['r']);


		// fork a malfunctioning kid

		$daemon->run(array($this,'kid'));


		// wait until it dies

		while ($daemon->count_kids() > 0)

			sleep(1);


		// this one doesn't:

		$r = Yii::app()->db->createCommand('select 3+3 as r')->queryRow();

		$bench->mark("3+3=" . $r['r']);

The first mark (2+2) works as expected

The second one (3+3) produces an error

PHP Error[2]: Error while sending QUERY packet

so ‘active’ property doesn’t help. Any ideas?

5

change the last part:




// this one doesn't:

                $r = Yii::app()->db->createCommand('select 3+3 as r')->queryRow();

                $bench->mark("3+3=" . $r['r']);

to




$connection=new CDbConnection($dsn,$username,$password);

$connection->active=true;

...do your query here

no tested. hope it works

6

check this

Check this

7

what i would do:

  1. define multiple db connection in config file.

'db'=>array(

	'connectionString' => 'mysql:host=localhost;dbname=db_default',

	//~~

),


'db2'=>array(

	'connectionString' => 'mysql:host=localhost;dbname=db_anotherone',

	//~~

),
  1. create two base activerecord models

protected/components/ActiveRecord.php


abstract class ActiveRecord extends CActiveRecord

{

    //~~~

    public function getDbConnection()

    {

		return self::$db;

    }


    //~~~	

}

protected/components/ActiveRecord2.php


abstract class ActiveRecord2 extends CActiveRecord

{

    //~~~

    public function getDbConnection()

    {

		return self::$db2;

    }


    //~~~	

}
  1. in your models:

in model1


class Model1 extends ActiveRecord {

	//~~~

}

in model2


class Model2 extends ActiveRecord2 {

	//~~~

}
  • note * by doing above you never worry about which is current db connection and when you refer another model in one model, you never worry about they would mess up with each other.

not tested, however it should work. please give us your feedback if it’s working.

8

No, no, guys, no. Your solutions are for the different problem. @Bettor, you came the closest - this is what I’ve been using. But that’s not an elegant solution.

So, let me rephrase the question:

I have a CLI script running for hours and doing occasional DB operations. There’s a good chance that the DB connection will go away at some point (MySQL is restarted, temporary network problems etc.). So Yii::app()->db is no longer usable. AND I NEED TO RE-INITIALIZE IT with the same DB parameters defined in config/main.php. Is there any way to do it?

9

Hi,

you could achieve desired behavior by adding ensureConnection() method to CDbConnection class (using class inheritance or Yii’s behavior feature).

My example is using class inheritance:

[b]

[/b]application.components.EDbConnection.php:




class EDbConnection extends CDbConnection {

	

	public function ensureConnection() {

		try {

			//test connection

			$this->getPdoInstance()->query('select 1;');

		}

		catch (PDOException $e) {

			//try to re-connect

			$this->setActive(false);

			$this->setActive(true);

		}

	}

	

}

application.config.main.php:[b]

[/b]




...

		'db'=>array(

			'class'=>'application.components.EDbConnection',//add this line

			...

		),

...

[b]

[/b]now you should perform your queries in the following manner:




$rows = Yii::app()->db->ensureConnection()->createCommand('...')->queryAll();