MySQL 5 Strict/Traditional fix, requesting feedback
A quick foreword, this post contains a guide to modify the official WordPress files. I do NOT recommend just anyone to do it as it MIGHT break your WordPress installation and I do NOT want to be held responsible.
As you might or might not be aware, the default WordPress installation doesn’t install well on MySQL 5 databases with SQL-MODE set to Traditional (AKA Strict). When running the installation tool, a lot of these error messages will appear:
WordPress database error: [Invalid default value for ‘comment_date’]
CREATE TABLE wp_comments ( …etc
This can be quite annoying for people who are forced with Traditional mode on MySQL 5, including myself.
Luckily, I’m a creative web developer and I think I have found the solution for this:
The MySQL 5 Reference manual for the DATETIME, DATE and TIMESTAMP types reads:
“The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.”
What MySQL’s strictmode doesn’t allow is out of range values, so it just returns an error, instead of ignoring the fauly value in nonstrict and doing it’s own magic.
Minor adjustments on two files in WordPress:
Replace every ‘0000-00-00 00:00:00’ with ‘1000-01-01 00:00:00’, should be 8 occurrences in this WP version (2.8.4), for table rows (in order of appearance):
comment_date, comment_date_gmt, link_updated, post_date, post_date_gmt, post_modified, post_modified_gmt, user_registered
Same as above, replace every ‘0000-00-00 00:00:00’ with ‘1000-01-01 00:00:00’, 5 occurrences in this WP version.
The modifications in the first file allows the automated installer to successfully create every database table, and thus run the installer without errors.
The modifications in the second file are fixes for Drafts. I used the first fix a while ago, but drafts weren’t working whatsoever, so I didn’t bother to post it just yet. But just now I found a fix to get drafts back to work, so I’m posting my solution on this.
I would like to request feedback and ask thoroughly testing from the developer team on this, and possibly include this in future WordPress releases. Again, I ask people again to NOT just go messing around in the source files (especially on live and running installs), unless you’re absolutely sure what you’re doing!
- The topic ‘MySQL 5 Strict/Traditional fix, requesting feedback’ is closed to new replies.