Wordpress move MySql runs into DATETIME DEFAULT SQL Error: 1067

Question

I run Wordpress with MySql 5.7.25.0ubuntu18.04.2 and want to move database to other machine.

When I do it with Database Migration I run into errors with DATETIME fields, eg

Invalid default value for 'comment_date'. SQL Error: 1067

I figured out, that given DEFAULT value 0000-00-00 00:00:00 don't work anymore. So I tried to change it to 1000-01-01 00:00:00but this only works for

ALTER TABLE `wordpress`.`wp_links` ALTER `link_updated` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_users` ALTER `user_registered` SET DEFAULT '1000-01-01 00:00:00';

successful without errors.

fine !

But these following ones still doesn't work: same field type, same default value... ?

ALTER TABLE `wordpress`.`wp_comments` ALTER `comment_date` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_comments` ALTER `comment_date_gmt` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_posts` ALTER `post_date` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_posts` ALTER `post_date_gmt` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_posts` ALTER `post_modified` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_posts` ALTER `post_modified_gmt` SET DEFAULT '1000-01-01 00:00:00';

eg:

Error Code: 1067. Invalid default value for 'post_date'

The questions are

How to move 'MySql' to an other machine ?
What's wrong with ALTER TABLE x ALTER columnY SET DEFAULT '1000-01-01 00:00:00';?

Bill Karwin · Answer 1 · 2019-04-06T15:00:39.713

The value is okay, but I suspect you can't create the table with the old value, which you need to do before you can alter it.

The issue is that MySQL 5.7 and later enable a strict SQL mode by default, which makes nonsense dates like 0000-00-00 an error. But that's the default value assigned to many datetime columns in Wordpress.

There's been discussion of fixing Wordpress to work with MySQL strict mode, since that's now the default on modern instances of MySQL. But it would take too much work, and risk incompatibility with many Wordpress plugins that expect the value '0000-00-00 00:00:00' to be used in lieu of NULL for a missing date. So your solution of using '1000-01-01 00:00:00' might not work anyway.

In that discussion, the lead developer of Wordpress wrote:

"WordPress just pretty simply does not support strict mode."

Then he removed "milestone: future release" from the feature request, meaning there is no intention to fix this in Wordpress, ever.

So the solution is to disable strict mode on MySQL. It's a pity.

In the Wordpress code, you can find includes/wp-db.php which lists:

    /**
     * A list of incompatible SQL modes.
     *
     * @since 3.9.0
     * @var array
     */
    protected $incompatible_modes = array(
            'NO_ZERO_DATE',
            'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'STRICT_ALL_TABLES',
            'TRADITIONAL',
    );

Set the sql_mode option in your my.cnf file to omit those sql modes. You can keep other sql modes, like the ones in the default set of sql modes for MySQL 5.7:

[mysqld]
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

You can also change the global sql mode set without needing to restart the MySQL Server:

mysql> SET GLOBAL sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

score 0 · Answer 2 · answered Apr 07 '19 at 06:23

Thank you @bill-karwin for the explaination, but unfortunately I have the issue during move MySQL Database to new server, at this point this has nothing to do with Wordpress setup, so there must be a MySQL-only solution.

I found a very easy solution: Simply copy the datafiles !

But because I can't logon with root, I've to switch temporary ownership:

on source server:

sudo su
/etc/init.d/mysql stop
cp -r /var/lib/mysql /home/myuser/mysql
chown myuser:myuser /home/myuser/mysql -R

on target server:

scp -r myserver:/home/myuser/mysql /home/myuser/mysql # I can't do it with root
sudo su
/etc/init.d/mysql stop
cd /var/lib
mv mysql mysql.save # backup
mv /home/myuser/mysql/ .
chown mysql:mysql mysql -R
/etc/init.d/mysql start # all is done and work properly

score 0 · Answer 3 · answered May 19 '21 at 15:59

The problem with invalid default of datetime columns (0000-00-00 00:00:00) you couldn't solve with ALTER TABLE is for all tables with more than one column with problematic default (in your case wp_comments had 2 colums, wp_posts had 4 columns).

The issue is that fixing one column leaves table in still inconsistent state due rest wrong columns defaults. If there would be one ALTER TABLE command for multiple commands (maybe there is?), this probably would be solved.

But there is a solution, in current session disable STRICT_TRANS_TABLES mode:

SET sql_mode = '';

Then run ALTER TABLE commands, in your case:

ALTER TABLE `wordpress`.`wp_comments` ALTER `comment_date` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_comments` ALTER `comment_date_gmt` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_posts` ALTER `post_date` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_posts` ALTER `post_date_gmt` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_posts` ALTER `post_modified` SET DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `wordpress`.`wp_posts` ALTER `post_modified_gmt` SET DEFAULT '1000-01-01 00:00:00';

Close mysql session (CTRL+D) and start new mysql cli session again what will reset mode to default

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session
-- returns long string that contains: STRICT_TRANS_TABLES

Now you can test if table is in consistent state. I was instructed by mysql-server installation process to run (please don't run this unless you know what this does - I don't !!!):

ALTER TABLE `wp_posts` FORCE; 
-- previously this reported: ERROR 1067 (42000): Invalid default value for 'post_date'

Wordpress move MySql runs into DATETIME DEFAULT SQL Error: 1067

3 Answers3