strange multiple database problem

[eluser]applepear[/eluser]
hi, there,

trust me... I know there are many discussion on this topic and tried to look at older posts on this. here is my problem.

i have two dbs, and here is how they are set up in database.php.

Code:

$active_group = 'userdb';
$active_record = TRUE;

$db['userdb']['hostname'] = 'localhost';
$db['userdb']['username'] = 'root';
$db['userdb']['password'] = '';
$db['userdb']['database'] = 'user';
$db['userdb']['dbdriver'] = 'mysql';
$db['userdb']['dbprefix'] = '';
$db['userdb']['pconnect'] = TRUE;
$db['userdb']['db_debug'] = TRUE;
$db['userdb']['cache_on'] = FALSE;
$db['userdb']['cachedir'] = '';
$db['userdb']['char_set'] = 'utf8';
$db['userdb']['dbcollat'] = 'utf8_general_ci';
$db['userdb']['swap_pre'] = '';
$db['userdb']['autoinit'] = TRUE;
$db['userdb']['stricton'] = FALSE;

$db['knowledgedb']['hostname'] = 'localhost';
$db['knowledgedb']['username'] = 'root';
$db['knowledgedb']['password'] = '';
$db['knowledgedb']['database'] = 'knowledge';
$db['knowledgedb']['dbdriver'] = 'mysql';
$db['knowledgedb']['dbprefix'] = '';
$db['knowledgedb']['pconnect'] = TRUE;
$db['knowledgedb']['db_debug'] = TRUE;
$db['knowledgedb']['cache_on'] = FALSE;
$db['knowledgedb']['cachedir'] = '';
$db['knowledgedb']['char_set'] = 'utf8';
$db['knowledgedb']['dbcollat'] = 'utf8_general_ci';
$db['knowledgedb']['swap_pre'] = '';
$db['knowledgedb']['autoinit'] = TRUE;
$db['knowledgedb']['stricton'] = FALSE;

this example involves a few model classes. user model class uses the userdb, and a few other classes uses the knowledgedb.

in user model class, DB is called by eg.

Code:

$this->db->get('user')

in eg. knowledge model class, i have

Code:

function __construct(){
        $this->kdb = $this->load->database('knowledgedb', true);
    }

    function func1(){
        ....
        $this->kdb->get('knowledge');
        ....
    }

now in the controller, i have the following code:

Code:

function __construct(){
        parent::__construct();
        $this->load->model('user');
        $this->loggedin = $this->user->is_loggedin();
        $this->load->model(array('knowledge', 'subject'));
        $this->param = $this->knowledge->func1();
    }

and so far, everything works well. then in one of the functions, i have:

Code:

function comment($kid) {
        $n = $this->knowledge->another_func();
        $n->uid = $this->user->get_userid($param1);
}

the first call works fine, but the second call gives me a Database error as the following:

Error Number: 1146

Table 'knowledge.user' doesn't exist

SELECT * FROM (`user`) WHERE `username` = 'apple'

Filename: ....\system\database\DB_driver.php

Line Number: 330

I think I did exactly as said in the user guide... how come the call to user model class is with the wrong DB if this is called after the $this->load->model('knowledge'...)?

hope the story is told clearly... thanks to you all in advance.

[eluser]Aken[/eluser]
Assuming your user model is using the default database methods ($this->db->get(), etc), I would guess that when you load the knowledge database, it also sets the $this->db object database to the same, even though it's returning the object as well. Not custom tested, but it's something to look into.

If you're using more than one database throughout your application, I'd recommend always assigning your databases to a separate variable like the user guide suggests, so you're always sure which one you're pulling from.

[eluser]applepear[/eluser]
I did the following experiment, and found that it seems that multiple database connection in CI does not work...

here is my code:

Code:

$dbh1 = mysql_connect('localhost', 'root', '');
        $dbh2 = mysql_connect('localhost', 'root', '', true);

        mysql_select_db('user', $dbh1);
        mysql_select_db('knowledge', $dbh2);        
        
        $res = mysql_query('select * from subject', $dbh2);
        echo mysql_num_rows($res).'<p/>';
        $res = mysql_query('select * from user', $dbh1);        
        echo mysql_num_rows($res).'<p/>';
        $res = mysql_query('select * from subject', $dbh2);
        echo mysql_num_rows($res).'<p/>';
        
        $db2 = $this->load->database('knowledgedb', true);
        $db1 = $this->load->database('userdb', true);
        
        $x1 = $db2->query('select * from knowledge');
        $x1 = $db1->query('select * from user');

for the first part, I directly tested multiple db connection on my system, and it seems to be working just fine... echo result output from the two db back and forth without problem.

then when it comes to CI, it will output the following error:

A Database Error Occurred
Error Number: 1146

Table 'user.knowledge' doesn't exist

select * from knowledge

Filename: C:\software\web\xampp\xampp\htdocs\education\branch1\system\database\DB_driver.php

Line Number: 330

any idea?

the DB configuration is the same as in the original post.

[eluser]applepear[/eluser]
I did a little digging in the above code:

$dbh1: resource id = 34 type=mysql link
$dbh2: resource id = 36 type=mysql link
$db1: resource id = 43 type=mysql link persistent
$db2: resource id = 41 type=mysql link persistent

they are all referring to different db, but dbh1 and dbh2 works, but db1 and db2 object from CI do not...

[eluser]applepear[/eluser]
problem seems to be pconnect... if multiple db connection is necessary, persistent connection does not work... set pconnect to false in db config, and it works.

is this a problem limited to mysql db?

[eluser]applepear[/eluser]
quote from mysql_pconnect on php.org:

mysql_pconnect() acts very much like mysql_connect() with two major differences.

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.

Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).

This type of link is therefore called 'persistent'.

So, in mysql, when multiple persistent DB connections that share the same uname/passwd do not work.

[eluser]broadband[/eluser]
I tried quite the same code as you did. Codeigniter code just does not work.

Code:

<?php

  $username = "xxxxt";
  $password = "xxxx";
  $hostname = "localhost";

  if( !($link = @mysql_pconnect($hostname, $username, $password)) )
  {
    echo "Error connecting to database." . PHP_EOL;
    exit(0);
  }

  if( !mysql_select_db('ci', $link) )
  {
    echo "Can't use ci datavase." . PHP_EOL;
  }

  echo mysql_num_rows(mysql_query("select count(*) from ci_session;"));


  if( !mysql_select_db('db1', $link) )
  {
    echo "Can't use qpcr database." . PHP_EOL;
  }

  echo mysql_num_rows(mysql_query("select count(*) from table1;"));
  mysql_close($link);

?>

Above code returns:

1
1

I think that codeigniter does't change mysql_select_db when you use persistent connection.

[eluser]broadband[/eluser]
@returns 1 1, I meant that my current two databases have one row each.

[eluser]InsiteFX[/eluser]
If you go to the MySQL website and lookup persistent connection you see that even they do not recommend it.


persistent connections are EVIL!