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.
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.
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.
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.
Thanks so so much for this. It’s helped me a great deal but not after loosing my youthful hairline.
Thanks! Was looking for this!!!
Glad I finally stumbled upon this info…it helped me!
Excellent, damn those \xa3 £ pound signs!
Very helpful. Solved something the EE-people should have solved by themselves. Or at least placed a hint in their installation help.
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.
thanx
Turning off Strict mode has helped with installation of SOHO Launch as well.
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
.
salutations
i can see that you’ ve made a really usefull thing at least for me
look !! today is 15/05/2009 and even the last comment by Adam was on March 5th, 2008
so …
bravo and arigatoo
Is it possible to turn off STRICT mode for one database? I’d like to run Expression Engine in a Windows shared hosting environment where the host does not want to turn it off at the server level.
I’m not sure how you would do it with Expression Engine, but the way to change the SQL mode at runtime is to set a session-specific mode with the following SQL query:
SET sql_mode = 'TRADITIONAL'Hi Dave,
I’m having the opposite problem:
I run in strict mode and need it. I also need to add a record in a table that has id in autonumber but can’t find the syntax to do so.
INSERT INTO contacts VALUES (”,’Joe’,'joe@blog.com’);
just wouldn’t work. I guess I could calculate myself the next number for the id field bu that sort of looses the point of an autonumber!
Any idea?
There are two ways you can do it:
INSERT INTO contacts (name, email) VALUES ('Joe', 'joe@example.com')INSERT INTO contacts VALUES (NULL, 'Joe', 'joe@example.com')