[Jump to my step-by-step solution to solve this problem]
WooCommerce and WordPress are taking over my life (in the best way possible!) Helping clients build awesome websites is super rewarding. In between all that, I sneak in some TLC for my own blogs.
WebGrrrl.net launched way back in 2006 (late November/December, to be exact!). That’s a lifetime in the blogosphere! Over the years, WordPress has undergone significant version changes, accompanied by updates to the database structure and schema. Additionally, both PHP and MySQL have received multiple upgrades to continue maintaining their compatibility and functionality.
Of course, with all this upgrading and tinkering, sometimes things get a little wonky and certain parts of the website don’t keep up with one another. Such can be the case with WordPress, PHP and MySQL working together. Fortunately, these issues are often identified by reviewing my trusty PHP and MySQL error logs, which provide valuable insights into potential website malfunctions.
Let’s be honest, battling website errors isn’t exactly everyone’s idea of a good time. But for me, it’s a chance to put my problem-solving skills to the test. And guess what? I’ve been keeping detailed notes on the process, not just for my own future reference, but also to hopefully help any one of you out there who might face the same challenge.
During my database table repair operations, I frequently encountered the following MySQL warning:
Warning Incorrect datetime value: '0000-00-00 00:00:00' for column 'post_date' at row 1
Error Invalid default value for 'post_date'
Thanks to a helpful StackOverflow discussion, I found out that the Strict Mode settings that was introduced starting in MySQL 5.7 causes this error. These settings enforce stricter data handling practices, which is generally a good thing. Still, while existing WordPress sites like mine might not be negatively affected by the error itself (I mean, my site still runs even with that warning), it can create issues when importing databases or maintaining backward compatibility with older WordPress versions. And possibly several other issues.
A WordPress core trac ticket exists for this issue, which at the time of this post is still open. But I bet you’re not here to read the trac, are you?
In a nutshell, until WP core addresses this or stops supporting backward compatibilities, the simplest way to solve the error message for now is by disabling MySQL’s strict mode.
The following steps outline the solution I implemented to address the error on my WordPress databases. Important Note: If you have multiple WordPress databases, you’ll need to repeat this process for each one. Always conduct testing in a non-production environment (such as a development site) before applying these steps to your live website. The solution utilizes PHPMyAdmin version 4:5.1.1+dfsg1-5ubuntu1 and is compatible with MySQL version 8.0.36-0ubuntu0.22.04.1.
Step 1: Back up my database once I logged in to phpMyAdmin.
Step 2: Select my WordPress database.
Step 3: In the Console (usually bottom right of your screen), execute the following query to reveal the current strict mode settings:
SHOW VARIABLES LIKE 'sql_mode' ;This will output the Variable_Name and Value for sql_mode. In my case, the Value in my WordPress database consists of (separated by commas):
- ONLY_FULL_GROUP_BY
- STRICT_TRANS_TABLES
- NO_ZERO_IN_DATE (I need to remove this)
- NO_ZERO_DATE (I need to remove this, too)
- ERROR_FOR_DIVISION_BY_ZERO
- NO_ENGINE_SUBSTITUTION
Step 4: Copy (CTRL+V on a Windows computer) the full Value. If you don’t see the full Value, select the +Options link above the result, select the Full texts radio button, then click the Go button.
Step 5: Create a new query that starts with “SET GLOBAL sql_mode =”. Then, paste the copied list of settings (minus NO_ZERO_DATE, NO_ZERO_IN_DATE, or TRADITIONAL) in between the quotation marks. For example, if the original list was:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONYour new query would look like:
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';Step 6: Execute the query in Console.
Now, run an “Optimize” on your tables. If the “Invalid default value” error has vanished, you’ve successfully tamed the beast! However, if the error persists, it’s best to consult a database expert for further troubleshooting.
If you’re looking for a plugin solution, I would reluctantly point you to Incorrect Datetime Bug Fix. The plugin hasn’t been updated since WordPress 3.2.1, though, so do so with immense care.
Check out my other posts: « Organizing Life (and Websites) for 2024: A WebGrrrl’s Fresh Start / That Time I Went Down the Paid-To-Blog Hole part 1 »