Multiple databases, codeigniter does not re-select the database

[eluser]dignick[/eluser]
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:

Code:

$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:

Code:

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:

Code:

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

This will produce something like:

Code:

// 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:

Code:

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

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

Code:

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?

[eluser]Hernando[/eluser]
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

[eluser]dignick[/eluser]
The Codeigniter database driver does use the connection id.
The original _execute() function does not work:

Code:

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

The _execute() function with the modifications I made:

Code:

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.'<br />';
    }*/
    return @mysql_query($sql, $this->conn_id);
}

[eluser]Hernando[/eluser]
This is the original version

Code:

/**
     * 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

[eluser]dignick[/eluser]
Yep, that's what it was. 1.7.2.

[eluser]Hernando[/eluser]
Second parameter of mysql_query is the connection

[eluser]dignick[/eluser]
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:

Code:

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

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

Code:

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

[eluser]dignick[/eluser]
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:

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:

Code:

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.'<br />';
    echo '$row[\'DATABASE()\'] = '.$row['DATABASE()'].'<br />';
    echo '$this->conn_id = '.$this->conn_id.'<br />';
    echo '$sql = '.$sql.'<br /><br /><br />';

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

produces:

Code:

$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():

Code:

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.'<br />';
    echo '$row[\'DATABASE()\'] = '.$row['DATABASE()'].'<br />';
    echo '$this->conn_id = '.$this->conn_id.'<br />';
    echo '$sql = '.$sql.'<br /><br /><br />';

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

produces:

Code:

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

[eluser]Hernando[/eluser]
I repeat.

If you use the connection in second parameter:

Code:

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

[eluser]dignick[/eluser]
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.