Digging deep to find an SQL error in a WordPress site migration
We were approached to help a remote client recover their website after their in-house servers crashed. They had all the files and a recent database backup, which is great! We routinely reload websites from files and databases, so we didn’t see any problem walking the client through the steps over Zoom.
But what could have been a very quick repair turned into several hours of digging around because the database backup kept throwing errors when they tried to import it into the new MySQL server.
To be perfectly honest, the error message that showed when we tried to re-import the backup didn’t seem terribly helpful.
Syntax error near 'order, preferred, type, number, visibility) VALUES ( 1, 1, 0, 1, 'workphone', 82' at line 1
Turns out a plugin vendor had chosen a perfectly sensible name, “order,” for a field that happened to also be a reserved keyword in the SQL database programming language. In day-to-day use this probably wasn’t a problem, but the standard MySQL archiving tool didn’t check while exporting the database.
The import tool, on the other hand, definitely did check. And didn’t like it!
It took quite a while to notice that the word “order” in this code snippet wasn’t being used as a reserved word but as the name of a field in a database table. It took a while because very few WordPress users know SQL. Or need to know it! One of the best things about WordPress is that it takes care of that sort of thing for you!
But as luck would have it, back before WordPress was a thing I used to have to hand-code SQL all the time. That was quite a while ago so I’ve gotten a little rusty. But after about the sixth try enough of it had come back for me to remember that the reserved word “order” needs to be enclosed in back-quotes, like this `order`
if it’s going to be used as a name.
The good news is that we were able to tell the client what to do, they added the backquotes where needed, and the import ran fine. Easy peasey, right? 😂 As the old joke goes, SQL is completely intuitive once you understand it really well.
It’s not every day you run into problems like these with WordPress. In fact this is the first time I’ve seen this particular problem in years and years. But it’s good to have a deep enough background to be able to remember how it’s done… even if you hope you don’t have to do it very often.
Posted in: Alerts and Warnings