Now this would be really funny if it weren’t so sad in so many aspects: I know of more than one company running MySQL. Ok, no news there. But the MySQL servers of said companies are dropping connections. Not twice a week or once a day but two or three times every bloody second. Investigation of the cause is underway but obviously that doesn’t help to fix the problem at hand.
Since the major platform in said companies is PHP, there’s another problem: Tests have shown that if a connection failed a subsequent connection request will go through just fine. While not ideal, the best solution for the moment would therefor be to enable the auto-reconnect feature built into every MySQL client. But for PHP, there is no option to do just that.
That’s because even while PHP is using the
function – which is needed to enable auto-reconnect – internally, nobody cared to make it available as part of PHP’s language. Maybe it would be easy to do just that, but I found it easier to patch PHP directly to enable auto-reconnect by default. You want to know how? Read on.
Since Sun bought MySQL, the pace of the releases has increased a lot. Ok, we’ll see whether this will continue, but still. So now it’s MySQL 5.4. Not only quite a big jump in the minor numbers but also some improvements, the community has asked MySQL to integrate for quite some time. Especially the code contributed by Google has finally made it into MySQL. One might ask “What took you so long?”. Still, even with those improvements, the new version isn’t for me. Why?
After reading the headline, I guess you now wonder what’s so special about a temporary table. And if you are a programmer you might even think “Heck, I’m using temporary tables all the time.”. Good for you, if you really do but chances are that you don’t.
The key here is the definition of what a temporary table in MySQL actually is. To most programmers it’s just a table they create for temporary use and delete afterwards. Well, sorry guys. That’s not a temporary table at all but just your well-known, friendly MySQL table as every other table in your database. It’s just a bit short-lived, that’s all. Now let’s talk about real temporary MySQL tables.
Today one of my MySQL servers started to behave really strange: It would process all requests just fine with the exception of one: It wouldn’t allow one of the forums to display any message threads. What it did instead was to hang there until the connection timeout dropped in. Even stranger was the fact that once I used my patched up PHP which has auto-reconnect capabilities the problem went away with the second request. So once the original request timed out and the re-connect kicked in, the result of the query was delivered in no time.
Resolution? Well, I just restartet the whole MySQL server. I suspect this is yet another problem of the 64-bit MySQL servers I’m using. Going to replace them with the 32-bit version step by step now.