Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

8 MySQL gotchas worth a rant

Peter Wayner | July 23, 2015
MySQL is easy to install, relatively fast, and loaded with features. If that's not enough, it's also one of the most prominent flagships of the open source movement, the big success story that showed us that a winning company could be built around open source code.

8 MySQL gotchas worth a rant

MySQL is easy to install, relatively fast, and loaded with features. If that's not enough, it's also one of the most prominent flagships of the open source movement, the big success story that showed us that a winning company could be built around open source code.

Yet anyone who has worked with the bits has shaken a fist at the screen on any number of occasions for any number of reasons. You can't build a technology that stores a bazillion new rows of Internet blather each second and not have a few cracks show.

In the interest of being cranky this summer, we've rolled up eight reasons why the open source relational database of choice sometimes gets us grumbling. Not all of the reasons given below are limited to MySQL alone. Some are broader salvos aimed at relational databases in general. But if we don't think clearly about relational databases and MySQL, we'll be stuck in the 1990s forever. We need to tear down to build up. (Or switch to a newfangled database that hasn't existed long enough for us to develop a list like this.)

Deep-seated bugs and oddities

Any big software package has bugs. But dig a little deeper, and MySQL's bugs take on a life of their own. Suddenly, you have to pay attention because it's not clear that NULL will always behave the same way, nor will the foreign key constraints be enforced as you might expect ... nor will auto increment do the right thing.

There are dozens of small issues, and they don't always get fixed. That's why some people keep gotcha lists. At least MySQL maintains a good bug reporting system, so we can know we're not imagining things. Others are experiencing the same frustrations.

The inflexibility of relation tables

Tables bring discipline and discipline is good -- until it forces programmers to fudge or shoehorn data into the inflexible columns defined by schema. One of the big reasons that NoSQL is becoming popular is that it gives programmers enough flexibility to enhance the data model on the fly. If one street address needs another line, well, you can insert it easily into a NoSQL document. If you want to add an entire new block of who knows what, the document model is also ready to accept your data for what it is, not what it wants your data to be.

Imagine you build a table full of ZIP codes stored as integers. It's wonderfully efficient, and it enforces the rules well. Then someone sends along a nine-digit ZIP with a hyphen. Or maybe you get a customer from Canada who has letters in the postal code.

 

1  2  3  Next Page 

Sign up for CIO Asia eNewsletters.