[PHP] Still using MDB2? Getting a “no such table” error?
So we're currently stuck using MDB2 at work. Like a vast majority of PEAR's repositories, it's just awful, awful code. I'm sure it served some kind of purpose before PDO came along (like what 5 or more years ago??), but now it's just a thorn in the side of any decent developer stuck dealing with it.
I recently came across a problem where I was accessing data across multiple database instances, and it was all well and good. Until you tried to use one of the first database connections again. Running anything against it would just result in a "no such table" error.
Buh? How can there not be a table there when I just queried from it not even a second before? Doing a dump of the MDB2 object itself even showed it was using the correct database information and it claimed to be using the correct database name.
Ultimately the problem turned out to be the fact that it still relies on the old mysql_* methods. This error occurred on our dev server, which is set up to model production, but isn't quite the same. On production, all our instances are actually on separate hosts. On dev, they're in separate databases, but the host and connecting information is all the same. If you look in the PHP docs for the mysql_connect() method's new_link parameter:
If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.
Yay. So successive calls to mysql_connect() with the same host, username, and password will return the same connection. It's a good thing to have to prevent opening too many connections, but it also largely depends on your database framework being able to correctly give you separate connections if you really want them. (In fact, I might go so far as to say it SHOULD be the code that caches connections, not the underlying driver, but whatever. mysql_connect()'s new_link param does give back a new connection if set to true.) Unfortunately, MDB2 caches the database name and checks its own cached name to ensure the database hasn't been switched, so it never detects that the same connection was pulled and switched elsewhere.
The easy fix is just to call mysql_select_db() every time you're pulling a cached DB connection to ensure your DB is correct. I'm honestly not sure of the overhead of doing that, but assuming you're not making tens of thousands of those calls, I'm sure it's reasonably minuscule. I'd honestly just suggest switching to PDO and caching your own connections, anyway.