Shulou(Shulou.com)06/01 Report--

Mysql5.7 datetime default value 0000-00-0000: 00:00 error

Experimental environment: MySQL 5.7.17

Table wp _ posts using wordpress

Mysql > CREATE TABLE `wp_ posts` (- > `ID` bigint (20) unsigned NOT NULL AUTO_INCREMENT,-> `post_ author` bigint (20) unsigned NOT NULL DEFAULT '0mm,-> `post_date_ date`datetime NOT NULL DEFAULT' 0000-00-0000: 00bigint '0000-00-0000: 00bigint' 0000-00-0000: 00unsigned NOT NULL AUTO_INCREMENT,-> `post_ content` longtext NOT NULL-> `Vantle` text NOT NULL -> `text NOT NULL excerpt`,-> `post_ status` varchar (20) NOT NULL DEFAULT 'publish',-> `varchar status` varchar (20) NOT NULL DEFAULT' open',-> `ping_ status` varchar (20) NOT NULL DEFAULT 'open',-> `post_ password` varchar (20) NOT NULL DEFAULT',-> `post_ name`varchar (200) NOT NULL DEFAULT'',-> `to_ ping` text NOT NULL,-> `pinged` text NOT NULL -> ``post_ modified` datetime NOT NULL DEFAULT '0000-00-0000: 0000 NOT NULL DEFAULT,-> `menu_ gmt` datetime NOT NULL DEFAULT' 0000-00-0000: 0000 post_content_ longtext NOT NULL,-> `guide`varchar (20) unsigned NOT NULL DEFAULT '0mm,-> `guide`varchar (255i) NOT NULL DEFAULT',-> `Guid`order` int (11) NOT NULL DEFAULT'0' -> `comment_ type` varchar (20) NOT NULL DEFAULT 'post',-> `post_mime_ type` varchar (20) NOT NULL DEFAULT'',-> `comment_ count` bigint (20) NOT NULL DEFAULT '0mm,-> PRIMARY KEY (`ID`),-> KEY `post_ name` (`post_ name`),-> KEY `type_status_ date` (`post_ type`, `post_ status`, `post_ date`, `ID`),-> KEY `post_ parent` (`post_ parent`) -> KEY `post_ author` (`post_ author`)->) ENGINE=innodb AUTO_INCREMENT=536 DEFAULT CHARSET=utf8 ERROR 1067 (42000): Invalid default value for 'post_date'

The above problem occurs because after the mysql5.7 version, the sql_mode parameter is set to NO_ZERO_IN_DATE,NO_ZERO_DATE

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.01 sec)

Solution:

Method 1: temporarily modify the method

Mysql > set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; mysql > set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Method 2: permanently modify the method, which requires restarting the mysql service: modify the configuration file my.cnf of mysql and add the following parameters

Sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION / / actually removes NO_ZERO_IN_DATE,NO_ZERO_DATE

Method 3: modify the default value of datetime

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.