When MySQL is upgraded to 5.7, WordPress reports error 1067 when importing data

I recently upgraded MySQL to 5.7, and WordPress reported an error when importing data

Invalid default value for 'comment_date'

The reason is that statements like this

DROP TABLE IF EXISTS `wp_comments`;
CREATE TABLE `wp_comments` (
  `comment_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `comment_author` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_author_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT 0,
  `comment_approved` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_parent` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`comment_ID`) USING BTREE,
  INDEX `comment_post_ID`(`comment_post_ID`) USING BTREE,
  INDEX `comment_approved_date_gmt`(`comment_approved`, `comment_date_gmt`) USING BTREE,
  INDEX `comment_date_gmt`(`comment_date_gmt`) USING BTREE,
  INDEX `comment_parent`(`comment_parent`) USING BTREE,
  INDEX `comment_author_email`(`comment_author_email`(10)) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci ROW_FORMAT = Dynamic;

This error is mostly caused by incompatibility of default values when you upgrade MySQL to 5.7. Check your field name, mine is a time field and its type is datetime. It seems that the default value of the type may be restricted, so check sql_mode. Sure enough: NO_ZERO_IN_DATE, NO_ZERO_DATE these two parameters limit the time cannot be 0

Check sql_mode

mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Temporary modification:

mysql> set session
 -> sql_mode = 'ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

Permanent modification:

You can modify the my.cnf file directly

For example: vim /etc/my.cnf

Modify the mysql.ini configuration file in Windows environment to solve it~

Add the following line under [mysqld]:

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Ok, the problem is solved, now take a look when you import or create a table!

The above is the detailed content of solving the problem of WordPress data import error 1067 when MySQL is upgraded to 5.7. For more information about MySQL database import error 1067, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How to use Navicat to export and import mysql database
Detailed explanation of MySQL command line export and import database instance
Steps to import mysql database under wampserver
MySQL command line export and import database
Modify php.ini to implement the maximum limit of Mysql import database file modification method
Windows cannot start MySQL service and reports error 1067 solution
mysql service cannot start and reports error 1067 solution (mysql startup error 1067)

<<: Detailed explanation of using INS and DEL to mark document changes

>>: Implementation code of Nginx anti-hotlink and optimization in Linux