mysql multiple connections can use same pconnect causing grief.

[eluser]Skotos[/eluser]
Take the following code:
$alphaDB = $this->load->database('alpha', TRUE);
$betaDB = $this->load->database('beta', TRUE);

echo "alpha using: ".mysql_result(mysql_query("SELECT DATABASE()",$alphaDB->conn_id),0)."<br>\n";
echo "beta using: ".mysql_result(mysql_query("SELECT DATABASE()",$betaDB->conn_id),0)."<br>\n";

$alphaDB->select('id, description');
$q = $alphaDB->get('male'); // Select the `male` table from `alpha` schema.
---
If both alpha and beta databases use the same host, same username and same password, then the script errors:

alpha using: beta
beta using: beta

A Database Error Occurred

Error Number: 1146

Table 'beta.male' doesn't exist

SELECT `id`, `description` FROM (`male`)

---
Synopsis:
Maybe it's a bug, or maybe it needs to be documented in the Multiple Database Section of the User Guide that connections need to differ in at least host or authentication credentials, (so that different connections are formed.)

[eluser]Randy Casburn[/eluser]
Hi,

Are you saying the following doesn't work either?

[quote author="Skotos" date="1225427175"]Take the following code:
$alphaDB = $this->load->database('alpha', TRUE);
$betaDB = $this->load->database('beta', TRUE);

$alphaDB->select('id, description');
$q = $alphaDB->get('male'); // Select the `male` table from `alpha` schema.
[/quote]

Can you please expose MySQL driver? (mysqli or mysql) MySQL version? MySQL Query Mode? MySQL Safe Mode? All have potential consequences.

Thanks,

Randy

[eluser]Skotos[/eluser]
Yes the problem is with the first two lines of the code. The echos were just there to indicate which tables were being pointing to.
The problem is with mysql. It has to do with the way that mysql_pconnect works. As php.net says:

http://au2.php.net/manual/en/function.my...onnect.php

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...

I haven't checked other DB engine connection syntaxes. I got around the problem by just creating another mysql user account to allow access to both schemas at the one time.

BTW: I am running CI-1.6 at the moment. Give me 15mins and I'll see if 1.7 is similar.

[eluser]Skotos[/eluser]
Yep! 1.7 is the same.

[eluser]Randy Casburn[/eluser]
Please read this post...

http://ellislab.com/forums/viewreply/478856/

Bottom line is this is not a bug, but expected behavior from both PHP and MySQL implemented correctly by CI. MySQL is not designed to handle connections that are ambigous and PHP is cannot remove the ambiguity we create even when our intentions are noble. So your solution of changing passwords, etc, is one way of removing the ambiguity.

If you don't want to create additional user accounts on the DB server, one or the other connections may be changed to a non-persistent connection. The best way go about this is to consider, really, if persistent connections are necessary for one of the DBs.

I hope you find this helpful.

Randy

[eluser]Skotos[/eluser]
That's what I thought - and that is why I went to the forum instead of the tracker.
I am wondering if it should be mentioned in the pink box at the bottom of http://ellislab.com/codeigniter/user-gui...cting.html

Possibly:

When you connect this way, you will use your object name to issue commands rather than the syntax used throughout this guide. In other words, rather than issuing commands with:

$this->db->query();
$this->db->result();
etc...

You will instead use:

$DB1->query();
$DB1->result();
etc...

Also, be aware that multiple connections to the same host with the same username and password may appear ambiguous to the sql server.

?

BTW: Sorry for double posting. I thought I had searched enough. Obviously I hadn't.

[eluser]Randy Casburn[/eluser]
No big deal about the double post. The perfect search terms aren't the easiest thing to dream up.

I think you have missed my point. The same host,username,passwords can be used, and are not ambiguous to the server, as long as only one connection is persistent and one is non-persistent. (Safe-mode and Query mode have to be taken into consideration too).

Glad I could help.

"The Dereks" watch the forums kind of closely, perhaps they will catch sight of this. If not, we can get word to them at some point.

Randy

[eluser]Mackstar[/eluser]
I am experiencing the same, although this is claimed not to be a codeigniter bug,

I believe it could be possible to be fixed by each db connection using a different mysql_connection handler??

Many thanks

Richard

[eluser]jeephnx[/eluser]
I wanted a controller to drop the connection to an auto loaded DB and reconnect to an alternative one. Just running $this->load->database('new_group_name') in the controller didnt work as it skips if it has already been loaded. The solution I use is to destroy the current db class using unset($this->db) and then running $this->load->database('new_group_name').
Works for me with MySQL.