The mysql extension for PHP confuses connection references to different databases of the same server

Question

PHP

I was just shocked that this code in both cases returns data from the sh2 database. What if you write $conn2 = mysql_pconnect('127.0.0.1', 'root', ''); the data of different bases is returned. Does anyone know how to deal with this? Need solution for mysql_pconnect connect function. We already know about the fourth argument mysql_connect. // obvious solutions: using mysqli, or explicitly specifying the database name in queries are not suitable :)

<?php

$conn1 = mysql_pconnect('localhost', 'root', '');
$conn2 = mysql_pconnect('localhost', 'root', '');

mysql_select_db('sh1', $conn1);
mysql_select_db('sh2', $conn2);

$res = mysql_query('SELECT * FROM users_collections', $conn1);
while($row = mysql_fetch_assoc($res)) {
print_r($row);
}

$res = mysql_query('SELECT * FROM users_collections', $conn2);
while($row = mysql_fetch_assoc($res)) {
print_r($row);
}


Answer 1

The documentation says about it

If a second call to mysql_connect() is made with the same arguments, then a new connection will not be established. Instead, the function will return a link to an already established connection.

en.php.net/manual/en/function.mysql-connect.php

Answer 2

That's the point of mysql_pconnect, it returns an already existing connection. He is in shock.

Answer 3

And it will be much more reasonable to use PDO, mysql_ * has already been sort of thrown out of the standard package. This is of course if you are writing from scratch.

Answer 4

Judging by your experiment with changing localhost to 127.0.0.1, you do not create a second connection to the DBMS, but a link to the already created one is returned (because they are identical).
Therefore, the active base for both (actually one) connections is the last selected base.
I think if you write
mysql_select_db('sh2', $conn2);
mysql_select_db('sh1', $conn1);
then all requests will go to sh1.
Sea of ​​solutions.
1. Make your own user for each database (this is correct from many sides)
2. Force the DBMS to listen to several ports and connect to different ones.
3. Make several localhost aliases and specify different ones in connections.
PS
This is my hypothesis, because there is nothing to check now, but it seems to me that I am right.

Answer 5

Do this:
$conn1 = mysql_connect('localhost', 'root', '', true);
$conn2 = mysql_connect('localhost', 'root', '', true);
Your default new_link parameter is false, so a new connection is not created, but an existing one is overwritten. Just appends true :)
Proofs:
resource mysql_connect ([ string $server = ini_get("mysql.default_host") [, string $username = ini_get("mysql.default_user") [, string $password = ini_get("mysql.default_password" ) [, bool $new_link = false [, int $client_flags = 0 ]]]]] )
new_link
If a second call to mysql_connect() is made with the same arguments, then a new connection will not be established. Instead, the function will return a link to an already established connection. The new_link parameter can cause the mysql_connect() function to open another connection, even if a connection with the same parameters is already open. In SQL safe mode, this option is ignored.

Answer 6

For mysql_connect, use the fourth parameter:
resource mysql_connect ([ string $server = ini_get("mysql.default_host") [, string $username = ini_get("mysql.default_user") [, string $password = ini_get("mysql.default_password") [, bool $new_link = false [, int $client_flags = 0 ]]]]] )

Answer 7

Came across something like this. It is solved by using different hosts when connecting. For example, you have a master and a replica on the same machine. Specify in DNS: ms.domain.com A 192.168.0.1, rp1.domain.com A 192.168.0.1 Use these two different domain names to establish different connections. You can use the domain name, ip address, and aliases like localhost if you don't have DNS access.