MySQL strict mode problems
February 26th, 2006
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 Add your own
1. james | August 22nd, 2006 at 5:29 pm
Hi david,I need your help I’ve already installed mySql is there away to disable the strict mode?Namely strict_trans_tables in mysql.I’m almost scared of the mySql monitor as I’ve always used phpmyadmin.I had at one time used mySqlAdministrator but the option under startupvariables/advanced/msqlmode is not there in version 1.2.2.If you can help me resolve this I would be greatly aprreciative.If this is the wrong place to post this I’m sorry.
2. David | August 22nd, 2006 at 5:42 pm
You don’t need to go into MySQL monitor. Open C:\Program Files\MySQL\MySQL Server 5.0\my.ini in a text editor, and search for the following section:
# Set the SQL mode to strictsql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Either delete it or insert a hash (#) sign at the beginning of the line that starts with
sql-mode=.Save my.ini and restart MySQL. Sorted.
3. james | August 22nd, 2006 at 11:58 pm
Thank-you David you are the bomb.You have saved me more than once here and on the F.O.E. forums.Keep up the good work.
4. KJ | September 5th, 2006 at 12:58 pm
Thanks so so much for this. It’s helped me a great deal but not after loosing my youthful hairline.
5. KP | February 23rd, 2007 at 7:38 pm
Thanks! Was looking for this!!!
6. Steve | March 2nd, 2007 at 7:59 pm
Glad I finally stumbled upon this info…it helped me!
7. Jimbo | April 3rd, 2007 at 3:33 pm
Excellent, damn those \xa3 £ pound signs!
8. Steen | April 7th, 2007 at 5:12 pm
Very helpful. Solved something the EE-people should have solved by themselves. Or at least placed a hint in their installation help.
9. Jitsu | April 9th, 2007 at 6:28 pm
You know what… Its cos of people like you that a ginormous screw up is evaded, and something that would have taken me hours to figure out was solved in about 10 minutes thanks to Google and You!
Why they would make strict mode not auto_increment and auto_increment-able field is betond me? How else are you suppose to AA? Thats the whole point.
10. Dean | February 14th, 2008 at 9:31 pm
thanx
Turning off Strict mode has helped with installation of SOHO Launch as well.
11. Adam | March 5th, 2008 at 7:25 pm
Thanks, was wondering why my INSERT queries worked fine on other servers and for some reason wouldn’t work locally without including every field. Nice post, thanks again - and thanks David (comment #2) for the shortcut with the INI file. BTW - Vista wouldn’t let me save the ini file, even with the service turned off and all utils closed (folder permissions i’d imagine). Workaround was saving to another folder, then cut/paste/overwrite into the proper location ;).
Leave a Comment
Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>
Trackback this post | Subscribe to the comments via RSS Feed