So here’s the problem...if I connect to database A and database B that are both on the same server, and I have pconnect enabled, you’ll hit a problem—since CodeIgniter reuses the same connection, the db_select method that gets called gets called twice for the exact same connection.
So if you have something like this (assume the tables in each database are the same, but fields are different):
then even if the fields are in reality different, the $srcFields and $destFields variables will hold the same values. Weird huh? I’ve made a simple solution, though.
Two caveats: it’s only for mysql, and it’s only php5. Both of these could be remedied easily by someone who cared more, but...yeah.
Add a variable and change two methods in your mysql_driver.php to the following:
First, when connecting, the function would first try to find a (persistent) link that’s already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.
Well, I know it’s because of PHP function that gets used—and that it’s designed to do what it does. However, when coding in straight up PHP, you design for this. In CodeIgniter, you can’t, besides to turn off pconnect.
PHP:
mysql_pconnect(blah); mysql_select_db(blah1); //do stuff mysql_select_db(blah2); //do more stuff
CodeIgniter equivalent:
?
If I have a database connection...that says I’m holding a particular database reference in this variable...and no errors are thrown...I want operations on that variable to happen on that database, no matter what the settings.
Though you could modify the db_select you suggested so that it takes a database_group instead of the name of a single database, but then that group could have other connection settings...see what I mean? It’s just that if you have $db1 = $thisDb and $db2 = $thatDb, that should work regardless of other settings, without extra php on the developer’s side.
Ah, static variables inside methods! I’d forgotten about that...and it’s PHP 4 evidently, which is nice. I think we’re getting closer. So...correct me if I’m wrong, but that $selected_db variable you have there exists only once in memory, correct? Even for multiple mysql driver instances… Which means if you have two mysql connections to _different_ servers, their behavior might be affected (adversely), right?
In other words, I think this combination of our solutions is most correct…
Also, I think db_select should return true if the correct database is already selected, since it is successful in achieving the post-condition is-connected-to-database.
It’s sort of annoying efficiency-wise to call db_select on every sql query, but I don’t really see a way around it that’s PHP 4 compatible (without sticking the var on the CI instance, which...would be horrible, basically).
I’ve thought about that…
Even if the hostname is the same, the username and password must match, and the connection must be a persistent connection to require that step… I thought to do it in all cases, ‘cause it won’t hurt anyone to call mysql_select_db if it’s already selected…
But if we want to do it, maybe we can use
<?php function db_select() { static $selected_db = array();
// If it's a persistent connection we will keep a track on the selected db if ($this->db->pconnect === TRUE) { // Unique key per permanent connection $key = serialize(array($this->hostname, $this->username, $this->password)); if (array_key_exists($key, $selected_db) && $selected_db[$key] == $this->database) { return TRUE; } $selected_db[$key] = $this->database; }
Hmm...I think any function with any sort of complexity should be avoided at all costs, if we’re to keep queries efficient. Here’s another idea, with my discovery of the mysql_thread_id function (PHP 4.3.0):
LOL… I thought we were working together, not against each other =P
I think it’s a great solution, but it will only work with MySQL… how about using strval($this->conn_id) instead? In mysql driver it’s the same that the thread_id solution (a bit cheaper, not storing the thread id ) and the same solution can be implemented in others drivers like odbc or oracle… Is that OK?
Haha, yeah, it’s together of course. Each iteration is better than the last, incorporating previous solutions, etc. Was thinking of dueling banjos I guess.
Hmm, well, the only problem with that is it doesn’t really...work. It’s pretty weird, but two mysql connections can have different connection ids but be using the same underlying connection.
Oh, you are right… I got it to work on PHP 5.2.5 but not on versions prior to 5.1.x
The thing is that we can use thread_id in mysql, but odbc has no similar implementation (as far as i can see). MSSQL and Postgre accepts extra parameters to force a new connection mssql_pconnect and pg_pconnect.
So we still have to find out a way to do it in ODBC… do we have a better choice than serialization or similar?
Edit: the other drivers do not accept pconnect, they just call to connect method
The only 1:1 choice for serialization is a multi-dimensional array (which I prefer just as a matter of personal preference), but as far as I can tell either you have the inconvenience of a method call or the inconvenience of multiple levels of indirection.
I see what you mean about the extra parameters things now—you can’t force a new persistent mysql connection. But the next question is...does it matter? If you look at db_pconnect in the CI postgre driver, you can’t force a new connection (..yet). Same for ms sql. So perhaps just storing the particular database with respect to a particular combination of parameters would be enough…
The last question is...what if the SQL server “goes away” in the middle of a script. Can that happen? Otherwise the connection might get reestablished and the db_select method will still think the old database is selected.
The only 1:1 choice for serialization is a multi-dimensional array (which I prefer just as a matter of personal preference), but as far as I can tell either you have the inconvenience of a method call or the inconvenience of multiple levels of indirection.
No idea what you mean… why multidimensional? why not as I put before?
Nanodeath - 21 February 2008 07:16 PM
I see what you mean about the extra parameters things now—you can’t force a new persistent mysql connection. But the next question is...does it matter? If you look at db_pconnect in the CI postgre driver, you can’t force a new connection (..yet). Same for ms sql. So perhaps just storing the particular database with respect to a particular combination of parameters would be enough…
Why can’t force a new connection? Yes, we can… that’s the point, if a new connection is made, there is no reason to switch database on each query…
Nanodeath - 21 February 2008 07:16 PM
The last question is...what if the SQL server “goes away” in the middle of a script. Can that happen? Otherwise the connection might get reestablished and the db_select method will still think the old database is selected.
If the server “goes away” doesn’t all the script execution stop? should we worry about this?
As for the new connection stuff, mysql_connect does have a $new_link argument, pg_connect has a PGSQL_CONNECT_FORCE_NEW flag, and pg_pconnect also has a PGSQL_CONNECT_FORCE_NEW flag, but there’s no way that I can see to force a new connection for mysql_pconnect, as it does not have a $new_link argument.
It’s *possible* for a SQL server to “go away” (connection times out..?) but since CI doesn’t worry about this happening (i.e. you don’t see mysql_ping before performing a query), I don’t think we have to either. Could happen for extremely long pages or something, but I’m really not an expert.
The solution for this problems could be, then, per driver:
mysqli, oracle and sqlite: there is no problem (not using pconnect at all)
postgre and mssql: force a new connection
mysql: using thread_id to identify each database
odbc: using a static multidimensional array to identify each database
do you agree?
If that so we can do the changes in each file and post them here so Ellislab team can fix it for the next release (if they like it, hehe)
