When starting the migration of a legacy PHP codebase away from the old MySQL extension, a lot of people initially decide to replace it with the MySQLi extension’s procedural interface. On the surface, this seems like the quickest and most straightforward approach. Unfortunately, this is based on a misconception. Although they are very similar, the interfaces of the two extensions are not simply interchangeable. These differences can be a bit subtle and are not always obvious at the outset but they can introduce a considerable unexpected work to your migration process.
In this post, we are going to look at some of the more common pitfalls so you can be aware of them and keep them in mind when you plan for your migration effort. Understanding the amount of effort required might even make you re-evaluate whether MySQLi is the right choice.
To start, let’s look at the origin of the misconception itself.
After a quick glance over the documentation, it’s apparent that MySQLi has an equivalent function for each MySQL function, with the only major difference being an “i” added in the function name:
This could lead you to believe that completing the conversion to MySqli would be as simple as firing up your IDE and doing something like the following:
Migration done! That was easy!
Almost too easy? While this is a necessary first step, there is almost certainly going to be a few things more things you will need to take carre of before you have working code. Let’s look at a few of the most common of these rabbit holes.
Re-ordered Function Parameters
For a number of mysql functions, just changing the function name to the mysqli equivalent will be enough. Look at mysqli_fetch_array for example:
Notice how for each of mysql_fetch_array‘s parameters, mysqli_fetch_array has an equivalent parameter, in exactly the same position of the function’s signature. This is true for a number of the mysqli_* functions, but not for all of them. Consider mysqli_query:
So mysqli_querydoes have equivalents for both of mysql_query‘s parameters, but their position in the function signature is swapped around.
Changing around the order arguments in each of your function calls is obviously going to take more than a quick Find/Replace.
Mandatory Link Id Parameters
:To understand this issue, let’s again compare a call to mysql_query with a call and its replacement function mysqli_query:
On the surface, everything looks good. We’ve changed the function name and switched around the parameter. So that should do the job, right?
If you are lucky, yes it would. It all depends on how the mysql_* calls in your codebase was written. If you are not so lucky, you will find calls in your code that completely leaves out the $link_identifier argument, like so:
How this even possible? Well, it worked because the $link_identifier parameter was optional in the old mysql_* functions. In the mysqli_* functions, however, the $link parameter is very much required.
See how this complicates things? We now need to introduce the missing arguments from… where exactly?
With both MySQL and MySQLi, you obtain a link identifier by calling mysql[i]_connect or one of the other connect functions.
When using MySQL, you had the option of leaving out the link identifier function argument, and, if you did, the function would just use the link identifier of the last opened connection or try to make a new one.
Using MySQLi however, you no longer have this option. You have to explicitly pass a valid link identifier every time you make the function call.
So to fully convert function calls like these, you have to assign the result of the mysqli_connect call, pass it along to where it is needed and then pass as the first argument in all the calls that require it.
Taking care of these could be a relatively quick job, or a massive one, depending on how many of these function calls there is in your codebase and how scattered or centralised they are.
Prepared Statement Complications
Chances are you are migrating away from the old MySQL extensions in the first place because you know about all its various security issues and you know that there are solutions to these in MySQLi. What is important to realize though, is that only converting to the mysqli_* functions as we’ve done above is not going to make our code any more secure than it was before.
To take full advantage of the SQL Injection protection in MySQLi, we have to use a feature called Prepared Statements. The thing is, the series of function calls we need to make to prepare and execute Prepared Statements in MySQLi are very, very different from the old model. How different? Here’s another side-by-side comparison: (There are no arrows this time, I initially tried to put some in but the whole thing just became a mess).
It should be pretty clear that the two interaction models differ so much that our last hope for a simple statement-by-statement conversion is now entirely out of the window.
“Thanks for Ruining Everything! Now What?”
If your only reason for choosing MySQLi procedural interface was that it appeared to be quicker and easier to convert to, the examples above have shown that this is not necessarily the case. There are many very good reasons why you may want to steer clear of MySQLi. There are also more than enough alternative approaches to get rid of the depreciated MySQL extension in your code. I’ll look at all of those in upcoming blog posts.
What if you’ve considered your options and you still decide to stick with MySQLi Procedural as your target technology? Are you doomed to spend endless hours doing nothing but refactoring mysql_* calls by hand? Hopefully not. There are a few tools out there that promise to automate all or at least some of the migration process for you. I’ll review some of these in an upcoming blog post.
Did this help you?
Sign up for my newsletter and I will send you regular articles and other usefull stuff.
You can also expect your first reaction to this abomination to be utter disgust and recoil. This will be probably be followed, shortly after by an overwhelming and determined desire to jump in and fix the whole bloody thing, to chop it down at the roots and rebuild it from the ground up. Every piece of code you see will be festering with ugliness and wrongness and every other kind of technical unholiness.
You will also start to notice that for every rusted nail you suddenly seem to have a ready hammer. Every technique, pattern, tool and framework you have in your mental toolkit will suddenly find a hole to plug, much like the tortured metaphors in this post.
The best you can do when this starts happening is to stop. Stop and feel the urges, feel them writhing in your gut. Then by any means necessary make sure that you DO NOT ACT on them. Drop your hammer and your sword and your axe. This is not a time for heroics.
It’s important to remember that you did not only inherit a code-base. You also very likely inherited a user base. Even if you don’t currently have any active users, you hopefully plan on having some very soon, right? In either case, your Users should be a primary driving force in any decision you make.
You need to focus on what your Users need. Specifically, you need to focus on what they need right now.
The thing your Users need right now is almost never a shiny new framework or perfectly clean code or a rapidly cascading refactoring effort. They need you to fix the most pressing bugs and implement the most overdue features. They need you to do this as quickly as possible and in a way that minimises disruption.
Safely making big sweeping changes to any complicated system requires a fairly thorough understanding of that system. When starting on a project like this you are not going to have a sufficient level of understanding and gaining it usually takes time. Your users, unfortunately, don’t have time to wait for you to figure out the system while they need things to get done. They also are not going to wait around if you prematurely make big drastic changes and things go wrong because there are important things you missed or did not understand.
Your best option, therefore, is to jump in, start working and work with what you have. As you make your changes write good, clean code, but importantly keep it localised, sensible and coherent within the context of the existing code that surrounds it.
What this all implies is that you need to prepare yourself for a difficult choice. Soon you are going to find yourself surrounded by the Complexity and Chaos that is your code base. You are going to have to choose to stay, stand there immersed in the Chaos and start working with it rather than against it.
Start preparing yourself for this choice now.
Choosing to work in the Chaos does not mean that you are turning your back on Order. You will not have to live in this place forever. You do not have to forget your weapons and your tools, they will all prove useful in time. Every day that you labour in the belly of the beast, you are gradually and carefully getting to know it, discovering every nook and cranny, every seam and smell. You are slowly building up a mental model and drawing a detailed map of its twisted insides. Then, in time, as you start to see it more clearly and completely, you will start hatching a plan…
The strategy you develop may be to launch a savage attack and slay the monster. It may be to gradually make her over into a graceful princess. The point is that this is not an undertaking you should make from a position of ignorance.
This choice, to focus on the what is necessary rather than what is interesting, sexy and exiting is not an easy one. It is also a choice you will have to make constantly, every time you stare at a piece of code and wonder what next to do. But in the long run, it’s the right decision, for you and your Users.
If you ever need something to help you stay on the path, a simple reason to keep you making the right choice, remember this one thing:
Your code-base is such a mess, in the first place, because everyone before you chose to do differently.
Did this help you?
Sign up for my newsletter and I will send you regular articles and other goodies.