MySQL Extension, Going, Going, almost Gone - PHP MySQL to MySQLi package blog

We are Builders

Have you ever considered that programming is a lot like construction work? Every day I put on my work belt full of the tools I will need to construct the latest and greatest application. I have building plans in hand so that I can set a proper foundation for the frame. Add the walls to separate different parts of the application and doorways to allow navigation from one part to another. Completing the project with the fine details and finish work to transform the empty space into an inviting and functional environment.

Okay, construction workers are generally more fit than me since their tools have mass while mine are mostly digital. They spend more energy than I do, having to carry materials up and down ladders compared to my trips to the fridge for that refreshing caffeinated sugary beverage, resulting in my personal increase in mass. My skin is a bit more pale and sickly looking compared to their glistening tan since my monitor does not produce ultra violet rays like the sun.

I admit, the construction comparison falls apart at the physical concrete level, so I am using it in the abstract. If you don't know what I am talking about here, take your fit tanned body back to the construction site where they have real concrete. Of course I am not serious, this article has something for everyone.

A Crack in the Foundation

I have designed and built a few applications from the ground up and worked in team environments, developing pieces of the whole. Regardless of the extent of my personal role, every application was built using tried and time tested methods as the foundation.

If I had to interact with a MySQL database using PHP, I use what I have always used, the MySQL extension. We have to remember the old adage, 'if it ain't broke, don't fix it'.

Part of what made construction with PHP so appealing as a tool is that its developers really worked hard to support backward compatibility. A new version never resulted in foundation problems with past projects, sure there was an occasional touch up needed on some of the finish work, however the foundation remained rock solid.

Then, suddenly project tests start throwing low level warnings that the MySQL extension is deprecated. Come on, seriously, there is no way that this extension is going away. I estimate that it is used 80% more often than its counterparts like MySQLi or PDO. It is just a small crack in the foundation of everything I have ever built, suppress low level warnings and all is good again.

Ignorance is Bliss

If you have never heard the statement, ignorance is bliss, then you probably where not as ignorant as I must have been as a child. It relates to the adage of burying your head in the sand like ostriches supposedly do when faced with danger.

I have my doubts about that last one, every time I have been close to an ostrich, they must not have seen me as much of a threat because there was a lot of chasing with no head burying, however I digress. Whatever your local saying is for... out of sight, out of mind, apply it here.

It became the standard operating procedure to suppress the deprecated warning, once again I fall back on the assumption that the MySQL extension can't go away. Getting back to the construction analogy, I have people working, living and playing in these structures I have built. So the foundation has cracked a little, some filler and paint and everything looks as good as new.

Besides, there are more important things that I need to focus my attention on, like planning to build the next highest skyscraper or better security to keep the vagrants out. After all, the MySQL extension won't go away, the warnings are a bluff to bolster the use of the 'better' tools.

The Wakeup Call

No matter what a persons level of ignorance, stubbornness, delusion, or whatever other word you want to use, eventually reality is going to show its face. It is amazing how changing the word, may, to will, can have such a profound effect, as in... the extension may go away, changed to... the extension will go away.

I have to admit, with a little bit of shame, that my first reaction was based purely on how the loss is going to affect me. Does anybody realize how much code I am going to have change? In my imagination I could see all these buildings falling down around the people who relied on them, who believed they would stand forever. In hindsight, it was a bit melodramatic, in my defense, I was dealing with a gut punch, or better still, a slap in the face telling me to wake up.

Once I had a firm grasp on reality again, the next step was to prepare to deal with the issue at hand. The MySQL extension was going away and something had to be done. Like the ostrich, I had to spread my wings and attack.

Face those Fears

We tend to give the things we fear more power over us than they deserve. The same holds true when your favorite programming language is going to eliminate your favorite database extension. The fear of the unknown is best tempered by shining a light on it and facing it head on.

For the most part, the MySQLi (i for improved) extension has replacements or aliases that fit nicely with the familiar MySQL extension, so the method calls are a simple replacement of mysql with mysqli, for example mysql_connect becomes mysqli_connect.

The biggest change is that you must specify the connection in MySQLi where MySQL would use the last connection opened if one was not specified.

That said, to appease those more technically minded individuals, I must mention that MySQL uses a resource value and MySQLi uses an object value, because without this mention, those individuals will let me know that there is more of a difference than just adding the letter i to mysql.

To them I say, you are correct, however in the practical deployment PHP has done a good job of helping to make the move over to MySQLi seamless.

From Ignorance to Optimism

For every problem there is a solution, and from what I have heard recovering addicts say over and over holds true, the first step is admitting there is a problem.

At first I started getting into the archived files, searching for mysql and making the appropriate changes to change the statements over to mysqli.

This quickly became tedious, so I created the package tool PHP Deprecated Function Checker which is a package that can be found here on PHP Classes. This tool allowed me to point to a file and know where and what changes needed to be made. The class was quickly improved by another developer, who has been credited with his work in the class, to report changes needed in all files within a directory structure.

The biggest hurdle is the requirement to always supply the connection for MySQLi. It was a common practice for me to connect to the database on initialization of a script and then all classes automatically used the current connection to make calls to the database.

The problem is that the connection is not global anymore, so the class methods can't find the connection. The easiest approach I have found to deal with this quickly is to use a global variable to store connection object and then wherever the connection must be specified in a class it will be available globally.

I realize that this is not 'best practices', however we already have a problem and we want to get a fix in place as quickly as soon as possible with as little change to the core operation as possible.

Creating a Lifeline

After reading a statement in a blog about how nice it would be to have something that developers could just dropped in to replace the missing extension, I took the next step and the PHP MySQL to MySQLi wrapper was created.

Once again I have to acknowledge the help of others in guiding me to a simple solution since my first attempt was very heavy handed.

This wrapper provides replacement functions for MySQL, when they finally do go away, which pass the properties to MySQLi for processing. It was developed at first as a stop gap for those developers falling behind in updating their projects, however as possible uses where discussed, it became clear that a bigger problem was on the horizon.

In my own, self absorbed universe, the loss of the MySQL extension is really no more than a inconvenience. A major inconvenience, however I have the skills necessary to fix my, or any other developers, code that breaks when the extension goes away.

What about the users who just trust that their applications are going to always work without knowing how they work? How many of these users are on shared hosting servers and have no control when the version of PHP they are using is updated to a version without the MySQL extension?

The PHP MySQL to MySQLi wrapper can act as more than just a stop gap for us lazy... I mean busy, developers who didn't immediately heed the warnings, and become a lifeline to keep these smaller sites working.

If your site has suddenly broke down and won't communicate with the MySQL database any longer, it may be the loss of the MySQL extension. If so, simply get this wrapper, include it before the connection and everything should magically start working again.

It is strongly recommended that you upgrade to the newest release of the script, find a replacement script or have a professional fix your current one when your budget will allow it.

Conclusion

We developers are builders. Our projects may be digital and difficult to physically touch. However they are relied upon by end users to be as stable and reliable as any structure.

We are not always able to predict what will happen in the future and how those changes will affect our projects so we must be able to adapt. The loss of the MySQL extension is not really a bad thing, it allows PHP to grow by shedding some dead weight.

Ten years from now we will probably be dealing with the loss of objects and OOP as a better alternative replaces it.

If you liked this article or have a question about the solutions to deal with MySQL extension removal, just post a comment here.