Problem when creating a table in '5.7.12-0ubuntu1'

CREATE TABLE `staff` ( `workstat_start` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', `workstat_end` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', `is_operator` TINYINT(1) NOT NULL DEFAULT '0', `is_contract` TINYINT(1) NOT NULL DEFAULT '0', `contract_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', `is_asbi` TINYINT(1) NOT NULL, `dismiss_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', `additional_data` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_unicode_ci' ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB AUTO_INCREMENT=15;

error code

 Error Code: 1067. Invalid default value for 'workstat_start'
  • MySQL workbench - bderevyaga
  • And why do you need such defaults? - naiad09
  • According to the documentation everything is OK - bderevyaga

1 answer 1

according to the documentation here:

NO_ZERO_DATE

The NO_ZERO_DATE mode affects whether the server permits as a valid date. SQL mode is enabled.

If this mode is not enabled, the “0000-00-00” is not allowed.

If this mode is enabled, the '0000-00-00'

IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is a warning.

in 5.7 in Ubuntu by default this setting is enabled

is solved, in theory as follows: STRICT_TRANS_TABLES and NO_ZERO_DATE in the server config, or rather change the default settings to

 [mysqld] sql_mode=ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ps about the question in the comments, а почему бы даты не делать NULL - https://stackoverflow.com/questions/1723367/whats-the-point-of-not-null-default

  • Thanks for the help, time did not beat to play. I set myself '5.6.31' - bderevyaga