7

I am using Wordpress 5.5 with MySQL 8. I have everything set up, and went to change the structure of the the wp_users table, but can't modify the table because of the error Invalid default value for 'user_registered'

This is the current column definition as created by Wordpress:

user_registered datetime default '0000-00-00 00:00:00' not null,

Changing the default value to '0' gives the same error message.

How can a table exist and work but have an invalid default value? And what is a default value I can enter that will not cause issues with Wordpress?

I am using DigitalOcean shared MySQL server, so I can't change the server mode to allow zero datetime values.

I have seen a few answers on this, but they all related to MySQL 5.5 and prior, or don't address how to make Wordpress work with this issue, or require changing the MySQL configuration which is not possible here.

MySQL version: 8.0.19

InnoDB version: 8.0.19

Wordpress version: 5.5.1

Wige
  • 3,788
  • 8
  • 37
  • 58
  • What statement does give the error? Setting a datetime field to default 0 will generate a error, 0 is not a date. – Grumpy Sep 06 '20 at 19:30
  • Any statement to change the structure of the table generates an error, such as alter table wp_users comment 'users'; – Wige Sep 06 '20 at 19:38
  • According to the MySQL documentation, '0' is a valid default value: CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0, dt DATETIME DEFAULT 0 ); see https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html – Wige Sep 06 '20 at 19:39

2 Answers2

20

This is usually an issue with moving an exported DB from MySQL 5 to MySQL 8.

You need to open the .sql file and put these lines at the top:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 
SET time_zone = "+00:00";

This resolves import issues for me on both WordPress and Magento.

Fabian S.
  • 2,441
  • 2
  • 24
  • 34
Adam Jackson
  • 301
  • 2
  • 2
4

The problem is because of sql_modes. Please check your current sql_modes by command:

show variables like 'sql_mode' ; And remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work. This is the default sql_mode in mysql new versions.

You can set sql_mode globally as root by command:

set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

See solution here

carlhandy
  • 315
  • 1
  • 8
  • 22