CODEWITHSUNDEEP
phpmysqlpdo
Frodik
Frodik

Reputation: 15455

Why PHP PDO connects to different database when using persistent connection?

I connect to MySQL using PHP's PDO like this:

$driver_options[PDO::ATTR_PERSISTENT] = true;
$db = new PDO('mysql:host='.$host.';dbname='.$db_name, $user, $pass, $driver_options);

I have 2 databases (let's call them database_A and database_B) on this server and sometimes very strange thing happens. Even though, $db_name is 100% set to 'database_A', connection is made to 'database_B'.

It's happening completely random. I can run the same script 10 times over again and everything is fine. And 11th time this problem happens.

I would never expect this to happen. It gave me a lot of headache. Can anyone explain it ? And is the only solution not to use persistence ?

Upvotes: 10

Views: 839

Answers (3)

Bart
Bart

Reputation: 1957

I suppose you are running this in production, or a dev system which is reloaded not so often.

Therefore please restart all your php workers/instances/threads and check if the problem occurs again.

I believe one of these processes is holding your old configuration. And silently causes your errors, randomly when your webserver actually chooses to use the old thing.

Upvotes: 0

LSerni
LSerni

Reputation: 57388

$driver_options[PDO::ATTR_PERSISTENT] = true;
$db = new PDO('mysql:host='.$host.';dbname='.$db_name, $user, $pass,    $driver_options);

When you do the above, the PDO connection is placed in the "persistent connection pool", but the pool purpose is not to cache the database, but rather the memory allocation, authentication and setup groundwork. That is what uses time (not so much, at that).

Whatever else you supply in the new PDO() call is LOST.

And if you have two databases with the same credentials, you can get them swapped at random -- as you experienced.

So, do not specify the DB in the new PDO statement, but use the USE databasename SQL statement as soon as the new PDO object is ready.

Or, as PankajKumar suggests, set up different credentials for the two DBs. Then the mistaken cache hit will not happen (but is wont to happen again as soon as someone reuses those same credentials - such as 'ubuntu/ubuntu' or 'root/').

Upvotes: 0

Plixxer
Plixxer

Reputation: 466

PDO::ATTR_PERSISTENT is partially supported and is dependent on the the PHP version and SQL server you're using. I would recommend never setting this attribute to true in the drive options due to it's instability.

I was able to replicate your case and i found that the ODBC Connection Pooling layer was caching the connection and since your connection is being set to persistent, the cache was being reset each time i made a new connection.

Upvotes: 4

Related Questions