Multiple databases, codeigniter does not re-select the database - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21) +--- Thread: Multiple databases, codeigniter does not re-select the database (/showthread.php?tid=31908) Pages:
1
2
|
Multiple databases, codeigniter does not re-select the database - El Forum - 07-06-2010 [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); 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 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.' - '; Code: // in the format: 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); Code: DB2 - DB2 - Resource id #70 - UPDATE `test2` SET `test` = `2` 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? Multiple databases, codeigniter does not re-select the database - El Forum - 07-06-2010 [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 Multiple databases, codeigniter does not re-select the database - El Forum - 07-06-2010 [eluser]dignick[/eluser] The Codeigniter database driver does use the connection id. The original _execute() function does not work: Code: function _execute($sql) Code: function _execute($sql) Multiple databases, codeigniter does not re-select the database - El Forum - 07-06-2010 [eluser]Hernando[/eluser] This is the original version Code: /** What version of Codeigniter do you use?. Second parameter of mysql_query is the connection Multiple databases, codeigniter does not re-select the database - El Forum - 07-06-2010 [eluser]dignick[/eluser] Yep, that's what it was. 1.7.2. Multiple databases, codeigniter does not re-select the database - El Forum - 07-06-2010 [eluser]Hernando[/eluser] Second parameter of mysql_query is the connection Multiple databases, codeigniter does not re-select the database - El Forum - 07-06-2010 [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 Multiple databases, codeigniter does not re-select the database - El Forum - 07-06-2010 [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"; The original _execute() does not work: Code: function _execute($sql) produces: Code: $this->database = test2 Note the $row['DATABASE()'] for both queries is the same. Modifying _execute(): Code: function _execute($sql) produces: Code: $this->database = test2 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. Multiple databases, codeigniter does not re-select the database - El Forum - 07-06-2010 [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 Multiple databases, codeigniter does not re-select the database - El Forum - 07-07-2010 [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. |