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.

    Hello everyone,

    The problem:

    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’.”

    (Source: https://dev.mysql.com/doc/refman/5.0/en/datetime.html)

    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.

    My solution:

    Minor adjustments on two files in WordPress:

    1) /wp-admin/includes/schema.php:

    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

    2) /wp-includes/post.php

    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!

    Mineth

Viewing 3 replies - 1 through 3 (of 3 total)

  • Thanks for solving this. I was getting that error and I was suspecting that my online server was being too strict, but I wasn’t sure about what to do about it.

    I managed to get the DB on the web, yay.

    Using WP MU 2.9 and saw the same problem you are seeing. Thanks for posting a fix. We tested it out and did not see any problems.

    Good Catch!!

    Thread Starter mineth

    (@mineth)

    After digging through the files again I have found more occurrences of ‘0000-00-00 00:00:00’ in the following files in the lastest WordPress version (2.9.2). They were likely there before in previous releases, but didn’t break huge things like the main installer or making a quick-post. Small things were still bound to break, and by changing the following files also, it will likely all be fixed:

    wp-links-opml.php
    wp-admin/admin-ajax.php
    wp-admin/edit-attachment-rows.php
    wp-admin/upload.php
    wp-admin/includes/meta-boxes.php
    wp-admin/includes/template.php
    wp-admin/upgrade.php
    wp-includes/general-template.php

    Next to the two following files stated in my OP:

    wp-admin/includes/schema.php
    wp-includes/post.php

    If you do a search and replace on every ‘0000-00-00 00:00:00’ to -> ‘1000-01-01 00:00:00’, it should be fine.

    MySQL5 has been out for quite a while now, and (hopefully) more and more companies are starting use the TRADITIONAL sql-mode MySQL5 has to offer. So it would be nice if this was being looked into sooner or later.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘MySQL 5 Strict/Traditional fix, requesting feedback’ is closed to new replies.