Thread

I’m having some serious problems with multiple databases in CI!

What I’m actually doing is too long to paste here, but I think the problem should be reproducable:

$db1 = $this->load->database('db1', TRUE);
$db2 = $this->load->database('db2', TRUE);

// DB2
$db2->set('test', 2);
$db2->where('id', 0);
$db2->update('test2');

// DB1
$db1->set('test', 1);
$db1->where('id', 0);
$db1->update('test1');

where db1 and db2 are different databases on the same server with the same login credentials.

If you do this, you will get an error similar to:

A Database Error Occurred

Error Number: 1146

Table 'DB2.test1' doesn't exist

UPDATE `test1` SET `test` = `1`

So, even though you are using the $db1 object to execute the mysql queries, it’s trying to use the $db2 database.  I spent a long time trying to figure out why.  If you insert this code into the _execute() function of system/database/drivers/mysql/mysql_driver.php, just under the first line of code, you can see what is going wrong:

echo $this->database.' - ';
$result = mysql_query('SELECT DATABASE()', $this->conn_id);
while ($row = mysql_fetch_assoc($result)) {
    echo $row['DATABASE()'].' - '.$this->conn_id.' - '.$sql.'
';
}

This will produce something like:

// in the format:
// what code igniter wants - what mysql has selected - connection id - query
DB2 - DB2 - Resource id #70 - UPDATE `test2` SET `test` = `2`
DB1 - DB2 - Resource id #48 - UPDATE `test1` SET `test` = `1`

What this is saying is that mysql only changes the selected database when you use mysql_select_db independent of the resource id, and because this is not called before each query is executed then the database is not changed for the second query.  If you look at the code, the database is only selected when the database object is initialised.
If you insert:

mysql_select_db($this->database, $this->conn_id);

at the top of the _execute() function, it works great:

DB2 - DB2 - Resource id #70 - UPDATE `test2` SET `test` = `2`
DB1 - DB1 - Resource id #48 - UPDATE `test1` SET `test` = `1`

This doesn’t seem like the correct behaviour on MYSQL’s part here as they are separate connections and should be treated as such - the selected database should differ for every connection.  Maybe someone can shed some more light on this problem?

If you use a connection as parameter,

Example: return @mysql_query($sql, $this->conn_id);

You don’t need to set a default database.

Cheers

The Codeigniter database driver does use the connection id.
The original _execute() function does not work:

function _execute($sql)
{
    $sql = $this->_prep_query($sql);
    return @mysql_query($sql, $this->conn_id);
}

The _execute() function with the modifications I made:

function _execute($sql)
{
    $sql = $this->_prep_query($sql);
    mysql_select_db($this->database, $this->conn_id);
    /*echo $this->database.' - ';
    $result = mysql_query('SELECT DATABASE()', $this->conn_id);
    while ($row = mysql_fetch_assoc($result)) {
       echo $row['DATABASE()'].' - '.$this->conn_id.' - '.$sql.'
';
    }*/
    return @mysql_query($sql, $this->conn_id);
}

This is the original version

/**
     * Execute the query
     *
     * @access    private called by the base class
     * @param    string    an SQL query
     * @return    resource
     */    
    function _execute($sql)
    {
        $sql = $this->_prep_query($sql);
        return @mysql_query($sql, $this->conn_id);
    }

What version of Codeigniter do you use?. Second parameter of mysql_query is the connection

Yep, that’s what it was.  1.7.2.

Second parameter of mysql_query is the connection

Yes, but it doesn’t work, as I explained above.  It should work as the selected database should be specific to each connection, but it doesn’t.  Perhaps it is a bug in the version of MySQL I’m using, 5.1.44.  If I change the _execute() function to the one I provided above, it works.

Look at the code I used in my first post:

mysql_query('SELECT DATABASE()', $this->conn_id);

The connection id was for db1, but the selected database returned by mysql was db2.

// $this->conn_id - SELECT DATABASE() - Resource id
DB2 - DB2 - Resource id #70
DB1 - DB2 - Resource id #48

Just to clarify, I have tested the following with Codeigniter 1.7.2 on php 5.3.2 + mysql 5.1.44 and php 5.3.2 + mysql 5.1.30.

Test code:

$test1['database'] = "test1";

...

$test2['database'] = "test2";

...

$db1 = $this->load->database($test1, TRUE);
$db2 = $this->load->database($test2, TRUE);
        
// DB2
$db2->set('test', 2);
$db2->where('id', 0);
$db2->update('test2');
        
// DB1
$db1->set('test', 1);
$db1->where('id', 0);
$db1->update('test1');

The original _execute() does not work:

function _execute($sql)
{
    $sql = $this->_prep_query($sql);

    $result = mysql_query('SELECT DATABASE()', $this->conn_id);
    $row = mysql_fetch_assoc($result);
    echo '$this->database = '.$this->database.'
';
    echo '$row[\'DATABASE()\'] = '.$row['DATABASE()'].'
';
    echo '$this->conn_id = '.$this->conn_id.'
';
    echo '$sql = '.$sql.'


';

    return @mysql_query($sql, $this->conn_id);
}

produces:

$this->database = test2
$row['DATABASE()'] = test2
$this->conn_id = Resource id #47
$sql = UPDATE `test2` SET `test` = 2 WHERE `id` = 0


$this->database = test1
$row['DATABASE()'] = test2
$this->conn_id = Resource id #47
$sql = UPDATE `test1` SET `test` = 1 WHERE `id` = 0

Note the $row[‘DATABASE()’] for both queries is the same.

Modifying _execute():

function _execute($sql)
{
    $sql = $this->_prep_query($sql);

    mysql_select_db($this->database, $this->conn_id);

    $result = mysql_query('SELECT DATABASE()', $this->conn_id);
    $row = mysql_fetch_assoc($result);
    echo '$this->database = '.$this->database.'
';
    echo '$row[\'DATABASE()\'] = '.$row['DATABASE()'].'
';
    echo '$this->conn_id = '.$this->conn_id.'
';
    echo '$sql = '.$sql.'


';

    return @mysql_query($sql, $this->conn_id);
}

produces:

$this->database = test2
$row['DATABASE()'] = test2
$this->conn_id = Resource id #47
$sql = UPDATE `test2` SET `test` = 2 WHERE `id` = 0


$this->database = test1
$row['DATABASE()'] = test1
$this->conn_id = Resource id #47
$sql = UPDATE `test1` SET `test` = 1 WHERE `id` = 0

success!  $row[‘DATABASE()’] is as it should be.

Edit:  I have just noticed that in the results above the connection id is the same, however when run on the other server (mysql 5.1.44) the resource id’s are different…

Edit 2: Just realised that because all the other connection details are the same across both connections, php uses the same connection to mysql.  Because of this, the different database names need selecting before each query.  So a proper solution in CI would be either to:
a) in the database constructor, check if the resource id already exists in another database object, and if it does then enable database switching on query execution
or
b) do as I have done and switch database for every sql execution.

I may try to implement a) myself if I can.

I repeat.

If you use the connection in second parameter:

return @mysql_query($sql, $this->conn_id);

You don’t need to set default database.

Your code with original _execute work fine in my machine.

Greetings

Doing a bit more digging I can see the issue here.
While the normal mysql_connect is passed TRUE for the new_link parameter by codeigniter, mysql_pconnect does not have such an option.  If you have persistent connection enabled, then codeigniter uses mysql_pconnect and if you have used the same server login previously it returns the existing resource id (however sometimes the resource id’s are different but are for the same connection…).  Because of this, the database needs switching before every execution.

If I have pconnect set to TRUE, it doesn’t work.  If I set it to FALSE, it does.  I still believe codeigniter should account for this condition and do some checking to see if database switching is required.  I will write my own workaround for this issue today.

I’ve developed a solution that seems to work ok, however it uses a global variable.  I could have used the codeigniter object, but then problems would occur if you started a database in a pre-controller hook for example.
The solution checks if the database is using pconnect, and then cycles through pointers to other database objects that are also using pconnect, checking if the login credentials are equal.  It is not safe to just check the connection id as it seems they sometimes differ even for the same connection.  If the credentials are the same it sets a property of both objects to TRUE so when executing the sql it knows it needs to switch database.

insert into system/database/DB_driver.php, after line 127 (initialize(), after connection)

/**** CUSTOM ****/
// multiple dbs with pconnect hack
$this->_select_db_on_execute = FALSE;
// check if pconnect
if($this->pconnect) {
    // check if any dbs with pconnect already exist
    if(isset($GLOBALS['dbs'])) {
        // cycle through all pconnect dbs
        foreach($GLOBALS['dbs'] as &$db) {
            // check if login credentials are equal
            if($db->hostname == $this->hostname &&
                $db->username == $this->username &&
                $db->password == $this->password) {
                
                // they are, so set the property in both dbs
                $this->_select_db_on_execute = TRUE;
                $db->_select_db_on_execute = TRUE;
            }
        }
    }
    // add this db to the array
    $GLOBALS['dbs'][] =& $this;
}
/**** END CUSTOM ****/

That’s determined if the database needs selecting before every query, now we need to select it.  Insert into system/database/drivers/mysql/mysql_driver.php after line 161 (start of _execute()):

if($this->_select_db_on_execute) mysql_select_db($this->database, $this->conn_id);

This works for me now if pconnect is true or false.

Just enter the database name

$this->db->select('*')->from( 'db2.users' )

$this->db->select('*')->from( 'db1.users' )

If you have persistent connection enabled, you should trun it off!

Persistent connections with MySQL cause’s problems.
It is recommended not to use persistent connections.