[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?
