Beware MySQL 5.1 my son

MySQL does a lot of magic automated things, ostensibly to make it easier to write code that doesn't fail. Unfortunately, as with similar attempts in PHP those can cause problems as often as not.

One in particular is NULL handling. MySQL allows, contrary to the SQL specification, a column to be defined as NOT NULL (NULL values are forbidden), and to have a default value. When you try to write a record using INSERT or UPDATE that would place a NULL value in that column MySQL will silently use the default value for you, assuming that's what you meant.

Now that's all well and good and can be quite helpful at times. However, it is contrary to spec and can lead to odd bugs that go unnoticed because the database isn't giving you sufficient red flags. MySQL 5, therefore, added a "Strict mode". Strict mode, among other things, disables that behavior and causes MySQL to whine loudly if you try to insert NULL into a NOT NULL column. Think of it as the MySQL equivalent of PHP's "E_NOTICE" setting. Sounds great, and helps lead to better code.

Well, except that lots of existing Drupal code doesn't work in Strict mode, because it assumes that magic behavior and just inserts NULLs into the database. It's not always laziness, either. Take, for instance, CCK fields. They define a partial schema record, but it's CCK that does the actual database query. But CCK will write NULL values. If a CCK field is defined to have a "NOT NULL default 0" column, that will fail far up in CCK. I know, it happened to me and it took a day and a half to track down. :-)

So why did it fail? Here's where MySQL 5.1 comes in. (I told you that was the issue, didn't I?) Specifically, this bug. In January of 2008, a bug fix was committed to MySQL 5.1 that caused MySQL to throw an error on a NULL value even if it wasn't in Strict mode. The net result is that suddenly Drupal modules that rely on that behavior will fail in MySQL 5.1.

But wait, there is hope! The bug was fixed. Unfortunately, it only fixed on 17 February of this year, so there are no releases yet that have the fix. The net result is that MySQL 5.1.23 through 5.1.32 (not yet released) is incompatible with at least some Drupal modules due to a bug in MySQL.

Fail!

For now, the best solution is to simply stick with MySQL 5.0 for Drupal 6. Fortunately, Drupal 7 now enables MySQL Strict mode automatically. That means modules will have to handle NULL values properly, not just for MySQL but because Drupal itself will enforce it. As a side effect, we work around that bug in MySQL 5.1 in the process.

OK, so Drupal 7 is still a ways away and we have to deal with the here and now. If your module is affected, dig in and fix it! It should be a simple change now that you know how to find it. And if you get bitten by this bug when running a site, well, good luck downgrading to MySQL 5.0.

This has been a public service announcement.