Multiple database + pconnect issues

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

Code:

$src = 'commontable';

$db_source = $this->load->database($database_source, true);
$db_destin = $this->load->database($database_destination, true);
$srcFields = $db_source->list_fields($src);
$destFields = $db_destin->list_fields($src);

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:

Code:

private static $selected_db = array();


function db_select()

{
    self::$selected_db[$this->hostname] = $this->database;

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

}

function _execute($sql)

{
    if(self::$selected_db[$this->hostname] != $this->database){
        $this->db_select();
    }


    $sql = $this->_prep_query($sql);

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

}

I have tested this, of course, and it works as expected. Let me know if you try it out!

[eluser]Seppo[/eluser]
[quote author="Nanodeath" date="1203588975"]since CodeIgniter reuses the same connection, the db_select method that gets called gets called twice for the exact same connection.[/quote]That's not a CI issue... it's a PHP behaviour.

mysql_pconnect

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.

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

Code:

mysql_pconnect(blah);
mysql_select_db(blah1);
//do stuff
mysql_select_db(blah2);
//do more stuff

CodeIgniter equivalent:

Code:

?

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.

[eluser]Seppo[/eluser]
Oh, now I understand what you say...
How about doing something like this on lines 80-83 of mysql driver?

Code:

function db_select($database = '')
    {
        if ($database != '')
        {
            $this->database = $database;
        }
        return @mysql_select_db($this->database, $this->conn_id);
    }

[eluser]Nanodeath[/eluser]
Hmm, while that would work, I think it makes things a little less...encapsulated? Also possibly more confusing.

My preferred way:

Code:

$src = 'commontable';

$db_source = $this->load->database($database_source, true);
$db_destin = $this->load->database($database_destination, true);
$srcFields = $db_source->list_fields($src);
$destFields = $db_destin->list_fields($src);

Way you suggested:

Code:

$src = 'commontable';

$db = $this->load->database($database_source, true);
$srcFields = $db->list_fields($src);
$db->db_select('someotherdb');
$destFields = $db->list_fields($src);

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.

[eluser]Seppo[/eluser]
I see your point...
The problem is that your first suggestion does not work on PHP 4

So I suggest to use, instead,

Code:

function db_select()
    {
        static $selected_db;
        if ($selected_db == $this->database)
        {
            return FALSE;
        }
        return @mysql_select_db($this->database, $this->conn_id);
    }


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

which I've tested OK on php 4.3.9, 4.4.7, 5.0..0, 5.1.6 and 5.2.5

is that ok, then?

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

Code:

function db_select()

{
    static $selected_db = array();
    if(array_key_exists($this->hostname, $selected_db) && $selected_db[$this->hostname] == $this->database){
        return TRUE;
    }
    $selected_db[$this->hostname] = $this->database;

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

}

function _execute($sql)

{
    $this->db_select();

    $sql = $this->_prep_query($sql);

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

}

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 tested it on 5.2.5 only.

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

Code:

<?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;
    }

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

}
?>

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

Code:

function db_pconnect(){
    $ret = @mysql_pconnect($this->hostname, $this->username, $this->password);
    if($ret){
        $this->_thread_id = mysql_thread_id($ret);
    }
    return $ret;
}

var $_thread_id = FALSE;

function db_select(){
    static $selected_db = array();
    if($this->_thread_id !== FALSE) {
        if(array_key_exists($this->_thread_id, $selected_db) && $selected_db[$this->_thread_id] == $this->database){
            return TRUE;
        }        
        $selected_db[$this->_thread_id] = $this->database;
    }
    return @mysql_select_db($this->database, $this->conn_id);
}

This feels like a coding duel for some reason

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