MySQL Bugs: #59868: When synchronizing model I got this error

Bug #59868	When synchronizing model I got this error
Submitted:	1 Feb 2011 10:51	Modified:	28 Jan 2014 14:57
Reporter:	Jacek Budzyń	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.31a	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	datetime, default value

Description:
Executing SQL script in server

ERROR: Error 1067: Invalid default value for 'User_DateCreation'

CREATE  TABLE IF NOT EXISTS `TaskIt`.`User` (
  `User_Id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Identyfikator' ,
  `User_Login` CHAR(50) NOT NULL DEFAULT '' COMMENT 'Login' ,
  `User_Password` CHAR(50) NOT NULL DEFAULT '' COMMENT 'Hasło' ,
  `User_DateCreation` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data założenia' ,
  `User_DateLogin` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniego logowania' ,
  `User_Nick` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Pseudonim' ,
  `User_FirstName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Imię' ,
  `User_LastName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Nazwisko' ,
  `User_Email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Email' ,

  PRIMARY KEY (`User_Id`) ,
  INDEX `User_Login` (`User_Login` ASC) ,
  INDEX `User_Password` (`User_Password` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = 'Tabela Użytkowników'

SQL script execution finished: statements: 3 succeeded, 1 failed

How to repeat:
Create Table and add column DATETIME with NOT NULL and DEFAULT VALUE '0000-00-00 00:00:00' and synchronizing model.

Database is new and dont have any table yet.

You probably have set the sql_mode 'NO_ZERO_DATE' on this server in combination with a 'strict mode' ('STRICT_ALL_TABLES' or 'STRICT_TRANS_TABLES').

Refer: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_zero_date

now .. what does "SHOW VARIABLES LIKE 'sql_mode'" return?

Peter
(not a MySQL person)

sql_mode => empty string

MySQL Server 5.5.8

I am still not convinced that the SESSION mode used by WB is '' (empty string).

Yes, you have right, now I see what to do WB when synchronize model to base:

---
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';

ALTER TABLE `TaskIt`.`Project` ADD COLUMN `Project_DateUpdate` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data aktualizacji'  AFTER `Project_DateCreation` ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
---

So WB set SQL_MODE='TRADITIONAL', thank you for helping Peter :), I try find option, for OFF this SQL_MODE then.

Hmmm, in Preferences -> SQL Editor -> Default SQL_MODE is empty string too... why WB set then TRADITIONAL?

this mode is always used for synchronizing (the preference setting only aplies to the SQL-Editor section of Workbench, not to model<->Database synchronization).
 as a workaround you can remove the SQL-mode statement from the generated SQL code, but that will probably raise other side-effects.

Bug #68489 is a duplicate

Can't repeat in 5.2.47, you have to set the SQL_MODE in Preferences -> Model:MySQL -> SQL_MODE to be used in generated scripts.