Latheesan
Latheesan

Reputation: 24116

MySQL Community 5.7 - Invalid default value (datetime field type)

I've just installed MySQL Community Server 5.7 and I am trying to create the following table:

CREATE TABLE IF NOT EXISTS `atcommandlog` (
  `atcommand_id` mediumint(9) unsigned NOT NULL auto_increment,
  `atcommand_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `account_id` int(11) unsigned NOT NULL default '0',
  `char_id` int(11) unsigned NOT NULL default '0',
  `char_name` varchar(25) NOT NULL default '',
  `map` varchar(11) NOT NULL default '',
  `command` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`atcommand_id`),
  INDEX (`account_id`),
  INDEX (`char_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1;

This gives me the following error:

#1067 - Invalid default value for 'atcommand_date'

This same query works fine on MySQL 5.6. Has the default datetime value been changed in MySQL 5.7?

Upvotes: 4

Views: 4315

Answers (1)

Konstantin Kulakov
Konstantin Kulakov

Reputation: 136

This looks like more of a SQL mode issue than the char set. Strict mode (and more specifically NO_ZERO_DATE which is part of strict mode) usually sets off that error if the table was created with an all zero default date before turning on strict.

What we use (for similar modified date columns), is the '1970-01-01 00:00:01'.

and on a slightly related note, we use timestamp for these columns (and for created time too). Takes half the storage space, and is faster access.

https://dba.stackexchange.com/questions/6171/invalid-default-value-for-datetime-when-changing-to-utf8-general-ci

Upvotes: 3

Related Questions