Friday, April 20, 2007

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.

4 comments:

Anonymous said...

So... in other words, not "the" MySQL mode, just "your" MySQL mode. :-P

rpbouman said...

Uhm...yeah, you're right ;)

What I actually meant by "the" sql_mode is that Wordpress is not coping with the possibility that someone's MySQL server happens to use an odd or at least non-default sql_mode.

But indeed, my specific configuration includes a number of options that don't go well with wordpress.

Simon Mudd said...

I got caught but this recently and 2 years later it seems that this is still not fixed. This "bug" seems to stop you saving drafts of your posts and also the db related errors caused by having (as was my case sql_mode = TRADITIOANL) do not get shown anywhere when posting. If you do not look in the logs you may not notice this problem for some time.

Anonymous said...

This is my first time visit at here and i am genuinely impressed tto read everthing at single place.

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...