MySQL strict mode problems

Simon Collison does such a good sales patter for ExpressionEngine in Blog Design Solutions that I decided to try it out. Things looked even brighter than his description when I disovered that not only has the price for the non-commercial licence has gone down from $149 to $99, but there’s now also a “Core” version, which is a free, unlimited trial. The free version lacks a lot of features, but it’s a good way to give ExpressionEngine a test drive without needing to worry about how many days you’ve got left to make up your mind whether to buy the paid-for version.

My enthusiasm took a sharp turn for the worse when the installation process failed at the last fence. I kept on getting the following message:

Error: Unable to perform the SQL queries needed to install this program. Please make sure your MySQL account has the proper GRANT privileges: CREATE, DROP, ALTER, INSERT, and DELETE.

I knew, however, that my account had all those privileges - and more.Eventually, I dug into the code of the installation file, search for the error message, and did a little debugging to find out what lay behind this unexplained failure. I got the page to display the problem SQL query, and then ran it directly in MySQL Monitor. All became clear: the primary key of each table is set to auto_increment, and the ExpressionEngine installation script attempts to insert an empty string into the primary key field. In older versions of MySQL, this works just fine, as the empty string is replaced by the next available number. In MySQL 5.0 running in SQL Strict Mode, though, this represents an out-of-range number, so the whole process comes to a grinding halt.

The solution was simple: switch off SQL Strict Mode in MySQL. Although it didn’t take me all that long to work this out, I suspect it’s going to catch out a lot of MySQL beginners. SQL Strict Mode is now turned on by default in the Windows Essentials version of MySQL 5.0, and ExpressionEngine isn’t the only mainstream script to use an empty string for the primary key in INSERT commands. It’s also used by phpMyAdmin.

So, for the time being, Windows Essentials users of MySQL should deselect the SQL Strict Mode option in the MySQL Configuration Wizard. A better solution, though, would be for everyone to start writing SQL commands that adhere to Strict Mode.

11 comments February 26th, 2006