As you might know, there is a fix which allows WordPress to run on MySQL strict. WordPress version 3.1.1 includes a database upgrade, which will break your WordPress installation if you run MySQL on strict/traditional if you upgrade automatically; I found out the hard way (although luckily on a test environment). The best bet is to upgrade the database manually.
Disclaimer: No server hamsters were (severely) harmed in the process.
If you’ve ever tried installing WordPress on a MySQL5-server where the SQL-mode includes either NO_ZERO_DATE or TRADITIONAL (aka ‘strict’ mode), you will be greeted by a whole lot of errors upon trying to complete the installation.
Firstly, let’s look at MySQL’s definition of the TRADITIONAL SQL-mode:
Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column.
Definition of NO_ZERO_DATE:
In strict mode, do not permit
'0000-00-00'as a valid date. You can still insert zero dates with theIGNOREoption. When not in strict mode, the date is accepted but a warning is generated. (Added in MySQL 5.0.2)
This pretty much fails the WordPress installation, since a whole lot of database tables aren’t being created, since they include errors according to the server’s SQL standard.
WordPress database error: [Invalid default value for 'comment_date'] CREATE TABLE wp_comments ( ...
WordPress uses quite a bit of ‘zero dates’ (dates consisting of 0000-00-00), which conflicts with the NO_ZERO_DATE constraint, which is included in the TRADITIONAL sql mode.
Most webhosters don’t use these kind of server settings. But for those that do (including this website), I have a very simple solution that fixes it. The only things you are required to do is be extra cautious about updating the WordPress installation (not plugins, only WP itself), since you need to manually add a line of code after each update. Be sure to make backups before updating (just to be safe) and ask your hosting provider if you’re allowed to ‘change the session sql-mode’ .
Note: This fix is for WordPress 3.1. Older versions require a slightly different approach, explained below.
After uploading your WordPress to your (restrictive) server, before installation, open your favorite FTP-client and edit file ‘/wp-includes/wp-db/php’. Find the current line:
$this->select( $this->dbname, $this->dbh );
This should be on or around line number 1065. Simply replace that with the following code:
mysql_query( "SET SESSION SQL_MODE := ''", $this->dbh ); $this->select( $this->dbname, $this->dbh );
After this, you can safely run the WordPress 3.1 installation and use it to your heart’s content, like you normally would. For WordPress versions older than 3.1, you need to call the ‘SET SESSION SQL_MODE’ query before each call to $this->select() in the code.
