9

EDIT: When updating an existing database from mysql 5.6 and executing:

UPDATE phppos_register_log SET shift_end = '2015-01-01 00:00:00' WHERE shift_end = '0000-00-00 00:00:00';

This produces:

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'shift_end' at row 1
#1067 - Invalid default value for 'shift_start' 

This worked in mysql <=5.7. I couldn't find any documentation about this...What is the issue with this?

CREATE TABLE `phppos_register_log` (
      `register_log_id` int(10) NOT NULL AUTO_INCREMENT,
      `employee_id_open` int(10) NOT NULL,
      `employee_id_close` int(11) DEFAULT NULL,
      `register_id` int(11) DEFAULT NULL,
      `shift_start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `shift_end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `open_amount` decimal(23,10) NOT NULL,
      `close_amount` decimal(23,10) NOT NULL,
      `cash_sales_amount` decimal(23,10) NOT NULL,
      `total_cash_additions` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
      `total_cash_subtractions` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
      `notes` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `deleted` int(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`register_log_id`),
      KEY `phppos_register_log_ibfk_1` (`employee_id_open`),
      KEY `phppos_register_log_ibfk_2` (`register_id`),
      KEY `phppos_register_log_ibfk_3` (`employee_id_close`),
      CONSTRAINT `phppos_register_log_ibfk_1` FOREIGN KEY (`employee_id_open`) REFERENCES `phppos_employees` (`person_id`),
      CONSTRAINT `phppos_register_log_ibfk_2` FOREIGN KEY (`register_id`) REFERENCES `phppos_registers` (`register_id`),
      CONSTRAINT `phppos_register_log_ibfk_3` FOREIGN KEY (`employee_id_close`) REFERENCES `phppos_employees` (`person_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  • 1
    This issue is documented... but first, which release of MySQL 5.7 are you using (particularly relevant, since 5.7 isn't yet GA as of now)? and there was an ill-conceived change introduced in 5.7.4 that was reverted in 5.7.8, that would explain this. Please confirm your version. – Michael - sqlbot Aug 29 '15 at 19:48
  • mysql-5.7.8-rc-osx10.9-x86_64.dmg – Chris Muench Aug 30 '15 at 5:19
  • I posted an edit with another issue. This seems like a real issue with mysql 5.7.8 – Chris Muench Aug 31 '15 at 13:56
  • 2
    Further review of the 5.7.8 release notes suggests that one change was reverted, but that you may need to to remove NO_ZERO_DATE, which is now part of the default configuration. Can you confirm? Please SELECT @@SQL_MODE;. – Michael - sqlbot Aug 31 '15 at 15:05
  • Yes I changed the mode and that worked. I found a lot of things mysql 5.7 added which caused issue so I set the mode to ""; this seems like a pretty big update with a lot of changes. – Chris Muench Aug 31 '15 at 19:30
6

since mysql 5.7, SQL_MODE default value is strict, please view this page: https://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.