Multiple Database Connection Problems

[eluser]atompkins[/eluser]
I have set up two database connections in database.php as shown in the user guide.

In my controller I have declared two class-level properties to represent each database connection. In the constructor I have the following code:

Code:

$this->appMgmtDB = $this->load->database('AppMgmt',TRUE);
$this->movieDB = $this->load->database('Movie',TRUE);

When I access the 'movieDB' connection, everything is fine but when I try to use the 'appMgmtDB' connection the queries will always fail unless I fully qualify the table names.

For example, this code:

Code:

$sql = "SELECT Id FROM User WHERE LoginName = ? AND Password = ?";
$query = $this->appMgmtDB->query($sql, array($loginName, $password));

will produce the following error:

Table 'Movies.User' doesn't exist

unless I change the SQL to the following:

Code:

$sql = "SELECT Id FROM AppMgmt.User WHERE LoginName = ? AND Password = ?";

For some reason, if the table is not fully qualified, it thinks that I'm querying the wrong database (Movies instead of AppMgmt) even though the connection object should be pointing at the AppMgmt database and a fully qualified table name is unnecessary.

All the queries against the movieDB connection work fine without the need to fully qualify the tables.

I'm out of ideas, can anyone suggest what might be wrong?

Thanks.

- Adam

[eluser]rogierb[/eluser]
Can you post the configs for both databases?
Can you do a "show databases" for each connection and post that aswell?

[eluser]atompkins[/eluser]
Doing SHOW DATABASES for each connection produces exactly the same result:

information_schema

AppMgmt

Movies

Here are the configs:

Code:

$active_group = "AppMgmt";
$active_record = FALSE;

$db['Movie']['hostname'] = "localhost";
$db['Movie']['username'] = "appUser";
$db['Movie']['password'] = "???";
$db['Movie']['database'] = "Movies";
$db['Movie']['dbdriver'] = "mysql";
$db['Movie']['dbprefix'] = "";
$db['Movie']['pconnect'] = TRUE;
$db['Movie']['db_debug'] = TRUE;
$db['Movie']['cache_on'] = FALSE;
$db['Movie']['cachedir'] = "";
$db['Movie']['char_set'] = "utf8";
$db['Movie']['dbcollat'] = "utf8_general_ci";

$db['AppMgmt']['hostname'] = "localhost";
$db['AppMgmt']['username'] = "appUser";
$db['AppMgmt']['password'] = "???";
$db['AppMgmt']['database'] = "AppMgmt";
$db['AppMgmt']['dbdriver'] = "mysql";
$db['AppMgmt']['dbprefix'] = "";
$db['AppMgmt']['pconnect'] = TRUE;
$db['AppMgmt']['db_debug'] = TRUE;
$db['AppMgmt']['cache_on'] = FALSE;
$db['AppMgmt']['cachedir'] = "";
$db['AppMgmt']['char_set'] = "utf8";
$db['AppMgmt']['dbcollat'] = "utf8_general_ci";

[eluser]Kamarg[/eluser]
I believe the problem is in the way mysql_connect (or probably any of the *_connect methods) work. It will reuse a connection with the same hostname/username/password unless the fourth parameter is set to true. Unfortunately, I'm unaware of any way to force CodeIgniter to pass that parameter as true without changing core files.

[eluser]The Wizard[/eluser]
yep it occurs when the hostname's match AFAIRemember.
IMHO its a (php/mysql) bug.

[eluser]atompkins[/eluser]
Thanks for the help guys - you were absolutely correct, the connection is being reused.

But there is a way in CodeIgniter to prevent that - in the database config you can simply set 'pconnect' to FALSE and the database driver class will use 'db_connect' instead of 'db_pconnect'.

Code:

$db['Movie']['pconnect'] = FALSE;

I made the change and the problem has gone away.

- Adam

[eluser]The Wizard[/eluser]
[quote author="atompkins" date="1259901523"]Thanks for the help guys - you were absolutely correct, the connection is being reused.

But there is a way in CodeIgniter to prevent that - in the database config you can simply set 'pconnect' to FALSE and the database driver class will use 'db_connect' instead of 'db_pconnect'.

Code:

$db['Movie']['pconnect'] = FALSE;

I made the change and the problem has gone away.

- Adam[/quote]

even if persistent connection is disabled it sometimes causes problems.
be aware