Guess what...Wordpress does not like InnoDB + autocommit=off either

In my previous entry I wrote how to fix Wordpress so it can avoid a possible too restrictive default SQL_MODE. I guess I should have realized that the following two lines from my my.cnf option file would probably not be liked by Wordpress either:

default-storage-engine=InnoDB              #Set the default storage engine (table type) for tables. 
init-connect="set autocommit=0"            #set autocommit off on connect

So, I went back and hacked wp-includes/wp-db.php again. Now the fix read:

// fix sql_mode 
                mysql_query("SET SESSION SQL_MODE := ''",$this->dbh);
                mysql_query("SET SESSION AUTOCOMMIT=ON",$this->dbh);
// end fix sql_mode

Let's hope this was the last one ....

So Wordpress does not like the MySQL SQL Mode

I just downloaded Wordpress to play around with. However, installation failed pretty miserably. The nature of the error messages led me to believe Wordpress does not like the SQL_MODE setting of my lcoal MySQL database.

By default, I have a fairly restrictive SQL_MODE for my local database server, which is set up by this line in my my.cnf option file:

sql_mode=TRADITIONAL,ANSI_QUOTES,PIPES_AS_CONCAT,IGNORE_SPACE,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION  

Now, a lot of people don't know you can SET the SQL_MODE on a per-session basis. (A lot of people also don't know SQL_MODE is available as of MySQL 4.1, but that's another story).

Anyway, I just fixed problem. It really was very, very easy:

Find out which php script actually connects to MySQL

roland@roland-laptop:/var/www/wordpress$ grep -Rn mysql_connect *
wp-includes/wp-db.php:52:               $this->dbh = @mysql_connect($dbhost, $dbuser, $dbpassword);

Open wp-includes/wp-db.php to edit it

roland@roland-laptop:/var/www/wordpress$ kedit wp-includes/wp-db.php

Locate the mysql_connect line, and after the wordpress code that checks whether the connection succeeds, add just one line of code:

// fix sql_mode 
                mysql_query("SET SESSION SQL_MODE := ''",$this->dbh);
// end fix sql_mode
  $this->select($dbname);

So, basically, I reset the SQL_MODE to the default for only this session, and let Wordpress do its thing. Well, that seems to help...at least I can install now ;)

Of course, I just reverted from a rather restrictive setting for the SQL_MODE to an extremely permissive one. But you can do it the other way around too of course!

For example, if your ISP only provides a vanilla MySQL 4.1 or up, you might find MySQL too permissive in accepting invalid dates, or you might not like the way MySQL handles the GROUP BY clause.

Well, it's easy to setup a generic include script to connect to MySQL, just like Wordpress does, and then including a SET SESSION SQL_MODE statement, just like I did. Obviously, in that case you would assign a comma-separated list of SQL_MODEs to it to make MySQL more restrictive. Look here for a full list of SQL_Modes.