• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Multiple database + pconnect issues

#11
[eluser]Nanodeath[/eluser]
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.

#12
[eluser]Seppo[/eluser]
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

#13
[eluser]Nanodeath[/eluser]
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.

#14
[eluser]Seppo[/eluser]
Oh, you lost me here...

[quote author="Nanodeath" date="1203661009"]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.[/quote] No idea what you mean... why multidimensional? why not as I put before?

[quote author="Nanodeath" date="1203661009"]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...[/quote]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...

[quote author="Nanodeath" date="1203661009"]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.[/quote]If the server "goes away" doesn't all the script execution stop? should we worry about this?

#15
[eluser]Nanodeath[/eluser]
Okay, sorry, that was perhaps a little pedantic.

Here's an example of how I implement what you said with a multidimensional array:
Code:
function db_select()

{
    static $selected_db = array();
    if($this->_persistent){
        if(@$selected_db[$this->hostname][$this->username][$this->password] != $this->database) {
            $selected_db[$this->hostname][$this->username][$this->password] = $this->database;
        } else {
            return TRUE;
        }
    }

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

}

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.

#16
[eluser]Seppo[/eluser]
Okay, now I got what you meant...

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)

#17
[eluser]Nanodeath[/eluser]
I'm reluctant to force a new connection whenever possible as this defeats the purpose of having persistent connections in the first place (and may cause some exploding with regards to the maximum number of connections the server can have). However, this may be in line with existing CI thinking considering mysql's db_connect always forces a new connection...on the other hand, postgre's db_connect never forces a new connection, even though it *could*. I don't know the reasoning behind this. So...I think we should wait for official input here before we go modifying the classes.

As an aside I think we could do the solution for odbc you listed above for mysql as well (maybe).

#18
[eluser]Seppo[/eluser]
You are right... it's kind of silly to force new connections if it's set to use persistents... Probably mssql and postgre should use the same system that suggested for odbc...

My personal conclusion is to avoid persistents connections as much as you can =P

#19
[eluser]Nanodeath[/eluser]
I'm starting to get to agree, Seppo Tongue

Even so, there's two main things to consider:
1) pconnect is the ""default"" setting. That is, I just copy/paste the default database connection and change the host/user/password, usually. If the behavior is at all less refined, then I think pconnect should "default" to false.

2) Annoying or not, if it's included at all it should still work in a logical way. So one shouldn't be "forced" to avoid certain functions that mostly work but have some weirdness. Even saying "if you're using multiple databases on the same server, don't use pconnect" is too much, because if we were doing this by hand we would be able to do that.

Just my thoughts.

#20
[eluser]bAum[/eluser]
Strange thing is that on MS SQL 2005 the behaviour occurs even with non-persistent connections (see my thread here).


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.