Check if can connect to database

[eluser]tim peterson[/eluser]
I'd like to backup my read replica(i.e., slave) database with my master database but this simple boolean I did failed:

Code:

$config['hostname'] = "myReadReplicaDatabase.com";
    //...$config['other_stuff']; other config stuff...
    $db_obj=$CI->load->database($config, TRUE);

    if(!$db_obj){
         $config['hostname'] = "myMasterDatabase.com";
         $db_obj=$CI->load->database($config, TRUE);
    }

After terminating my read replica database I expected the boolean to evaluate to `FALSE` and the script to then use my master database. Unfortunately, instead I got the following PHP error:

Code:

Unable to connect to your database server using the provided settings.
    Filename: core/Loader.php

All i want is for the connection to return true or false, does anyone know how to do this in Codeigniter?

[eluser]yacman[/eluser]
Try changing this setting in your /application/config/[env]/database.php

Code:

...
$db['myReadReplicaDatabase.com']['db_debug'] = FALSE;
...
$db['myMasterDatabase.com']['db_debug'] = FALSE;

[eluser]tim peterson[/eluser]
Hi yacman,

Setting

Code:

'db_debug'=FALSE

partially works. It spares me waiting for the timeout (~30secs) to determine that read replica host doesn't exist. However, the $db_obj does not return FALSE and therefore the master database config hostname doesn't get used.

Might I ask how to change the if clause (below) to accomplish this?

Code:

if(!$db_obj){

thanks,
tim

[eluser]yacman[/eluser]
Since you want to know the response to the intialize, I'd recommend turning off auto initialize to false.

Code:

$db['xxx']['autoinit'] = FALSE;

Then in your code that checks the db state:

Code:

$db_obj = $this->database->load('xxx',TRUE);
  $connected = $db_obj->initialize();
  if (!$connected) {
     $db_obj = $this->database->load('yyy',TRUE);
  }

[eluser]tim peterson[/eluser]
I spoke too soon, the `initialize()` function is returning FALSE regardless of whether or not the read replica exists. So that doesn't work.

thoughts?

[eluser]yacman[/eluser]
In your config, I'd recommend turning on logging and then tailing that log file.

If initialize() is returning false, then the db is failing to initialize. This means there is something incorrect in your configuration.

Have you ever successfully connected to the db before using CI?

[eluser]tim peterson[/eluser]
yes, i can connect to the db using CI just fine.

My problem is simply that I can't switch the config to go from one database to the other in the event of the first one failing to connect.

[eluser]yacman[/eluser]
Can you post some more of your code? You said that database initialize is false.

Where is '$this->CI' being instantiated?

Have you tried putting this logic into the model file like:

Code:

function __construct() {
parent::__construct();
if ($this->load->database('slave_db') === FALSE) {
              $this->load->database('master_db');
        }
    }

In your library you'd then just use:

Code:

$this->CI->load->model('a_model');
      $this->CI->a_model->db->query($sql);

*edit: In reference to above,I would re-enable autoinit in the DB config.

[eluser]tim peterson[/eluser]
My master database is autoloaded since it is used by many models throughout my application.

I'd like to use the read replica database in some of the models for SELECT/read-only scenarios.

Here's my entire read replica config which, like my master database, i'm autoloading as a library : https://gist.github.com/3749863.

Therefore in a Model I normally have this code:

Code:

$query=$this->db->query("SELECT * WHERE...

And for read-only scenarios I'd like to do this:

Code:

$query=$this->readreplica->load->query("SELECT * WHERE ...

The trick is that if my read replica database fails to connect I'd like to fall back to using $this->db (my master database config). My master database already has failover protection since I'm using Amazon RDS.

[eluser]yacman[/eluser]
Looks good, so in those models that you want to use the replica db for read only,

Code:

class a_model extends CI_Model
{
    public $replica;
    function __construct() {
         parent::__construct();
         //This will make available $this->db and is using your Default DB settings.
         $this->load->database();
         if (!$this->replica = $this->load->database('replica',true)) {
              //Loading the replica FAILED.  
              //Use a pointer to point to the master db instance.
              $this->replica =& $this->db;
         }
    }

    function getstuff() {
       //Then when you reference replica, you trust that
       //it is available either by master
       //or slave, and its preference is slave.
       return  $this->replica->get('stuff');
    }
}

...
//Outside of the model, say in a library:

$this->load->model('a_model');
$theStuff = $this->a_model->replica->get('stuff');
or
$theStuff = $this->a_model->getstuff();