mysql升级到5.7时,wordpress导数据报错1067的问题
16lz
2021-12-11
最近把mysql升级到5.7了，wordpress导数据报错
Invalid default value for 'comment_date'
原因出在类似这样的语句
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;
查看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>
例如： vim /etc/my.cnf
Windows环境下修改mysql.ini配置文件就解决了~
在[mysqld]下面添加如下列：
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
更多相关文章
- 升级到Android(安卓)Studio 3.2.1 ，报 Could not find com.androi
- Android：Galaxy Nexus升级到4.1.2，并root（设备是日版）
- Android(安卓)studio2.3.3升级到3.1.2坑(小记)
- 升级到Android(安卓)10和gradle升级
- Android(安卓)support 26升级到28特性总结
- 升级到Android(安卓)Studio 3.0出现 aapt2错误
- Android(安卓)SDK手动升级到Android(安卓)5.0(API Level 21)
- Server2012 R2 Powershell4升级到Powershell5
- Oracle DB 18c - 手动升级到 18c 的完整核对清单 (文档 ID 2469