MySQL workbench to PHPMyAdmin: 0000-00-00 00:00:00 - Invalid default value

Question

Why do I get an error message when I import SQL created MySQL workbench?

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';





-- -----------------------------------------------------

-- Table `$type`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `type` (

  `type_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,

  `name` VARCHAR(255) NULL DEFAULT NULL ,

  `created_on` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ,

  `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`type_id`) )

ENGINE = MyISAM

AUTO_INCREMENT = 1

DEFAULT CHARACTER SET = utf8;



SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

error message,

#1067 - Invalid default value for 'created_on'

What is wrong with this value - 0000-00-00 00:00:00

Not sure... timestamp, and datetime both accept '0000-00-00 00:00:00' as a default construct. — Mike Purcell, Feb 26 '12 at 18:58
sorry. it now works. I should not have set the default value in DATETIME. — Run, Feb 26 '12 at 19:05
missing a word in my reply - I should not have set the default value as `NULL` in the column of DATETIME — Run, Feb 27 '12 at 02:56

score 11 · Answer 1 · answered Oct 17 '13 at 11:08

Shortly, the answer by wormhit is wrong. Notice 3rd line:

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

TRADITIONAL according to the manual is a shortcut that expands to

STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER

So by specifying the mode you actually prohibiting zero dates. In MySQL Workbench 6 go Preferences -> Model: MySQL and remove TRADITIONAL from SQL_MODE to be used in generated scripts.

Also notice that TIMESTAMP is timezone-aware type, whereas DATETIME is not. So depending on timezone settings of machine, MySQL's global and session settings result-sets could vary.

If I were you, I'd remove the reference to the other user's answer. — iajrz, May 14 '15 at 02:33

score 0 · Answer 2 · edited Feb 23 '18 at 19:34

0

If the other methods mentioned here don't work, you can try this:
Modify my.ini of the mysql path.

sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

edited Feb 23 '18 at 19:34

NightOwl888

55,572
24
139
212

answered Nov 08 '16 at 09:50

Zelda

1
1

if not denfine sql-mode, l think the answer of saaj can work – Zelda Nov 08 '16 at 09:53

score 0 · Accepted Answer · answered Feb 26 '12 at 19:01

0

Use DATETIME instead of TIMESTAMP

answered Feb 26 '12 at 19:01

wormhit

3,687
37
46

got it - it now works with ``created_on` DATETIME NOT NULL ,` – Run Feb 26 '12 at 19:04
Im not quite sure how timestamp is working in mysql, but I also have this problem all the time, so I'm using timestamp default current_timestamp on one field and on other datetime with 0000-00-00 00:00:00. The answer should be somwere here http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html but i'm too lazy to find it :p – wormhit Feb 26 '12 at 19:08
See the answer below, by @saaj. – iajrz May 14 '15 at 02:31

MySQL workbench to PHPMyAdmin: 0000-00-00 00:00:00 - Invalid default value

3 Answers3