MySQL Bugs: #59050: Unable to synchronize table from model containing datetime column

Bug #59050	Unable to synchronize table from model containing datetime column
Submitted:	20 Dec 2010 9:21	Modified:	21 Dec 2010 13:05
Reporter:	Michael Kakuschky	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.31a	OS:	Windows (WIndows 7 x64)
Assigned to:		CPU Architecture:	Any
Tags:	database, datetime, model, synchronize, workbench

Description:
I tried to synchronize a table (CREATE statement at end of this mail) with the model from my workbench. The only thing which should happened is to add a column:

ALTER TABLE `comzatel`.`employee` ADD COLUMN `remote_user` VARCHAR(32) NULL DEFAULT NULL  AFTER `remote_host` ;

But the statement fails even if the bithday column is not effected directly by the statement.

Error Code 1292:Incorrect datetime value:'0000-00-00 00:00:00' for column birthday

I tried to execute the ALTER statement directly from the mysql console of the server and it works perfectly.

I'm wotking on a Windows System with German localization but the machine where the database runs it's running on american localization

I guess there is maybe something wrong with the session date format. But in workbench I did not find a setting regarding this. 

So is this a bug, I never got a message like this with earlier versions of MySQL workbench.

CREATE TABLE `employee` (
  `employee_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(32) default NULL,
  `last_name` varchar(32) default NULL,
  `gender` varchar(1) default NULL,
  `address_id` int(11) default NULL,
  `birthday` datetime default NULL,
  `created` timestamp NULL default CURRENT_TIMESTAMP,
  `entry_date` datetime default NULL,
  `department_id` int(11) default NULL,
  `job_title` varchar(48) default NULL,
  `username` varchar(32) default NULL,
  `password` varchar(32) default NULL,
  `company_email` varchar(128) default NULL,
  `prefered_language` varchar(2) default NULL,
  `remote_ip` varchar(128) default NULL,
  `remote_host` varchar(128) default NULL,
  PRIMARY KEY  (`employee_id`),
  KEY `FK_EMPLOYEE_ADDRESSID` (`address_id`),
  KEY `FK_EMPLOYEE_DEPARTMENID` (`department_id`),
  CONSTRAINT `FK_EMPLOYEE_ADDRESSID` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_EMPLOYEE_DEPARTMENID` FOREIGN KEY (`department_id`) REFERENCES `department` (`department_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8$$

How to repeat:
CREATE the table on a database which runs on a system with American localization.
Execute the workbench on a system with German localization

Suggested fix:
Maybe it's regarding somehow to session datetime format settings. But I did not find a place to correct them.

I did some more tests. If I connect via the Windows command line from the windows machine where the workbench is running it works.

It works also like above described If I log in via SSH to the database server and start a mysql console directly on the machine

But it' does not work if I try to synchronize the database with the the model via the Synchronize Database Dialog or running the the statement from the Workbench SQLQuery Tab.

So it looks that it depends from the Workbench and not from the machine where the command is executed.

just edit the Synopsis

Tiis is an SQL_MODE issue.  Simple test case: 

SET SQL_MODE = 'no_zero_date,strict_all_tables';
CREATE TABLE `test`.`dttest`(`id` INT NOT NULL AUTO_INCREMENT,`dt` DATETIME, PRIMARY KEY (`id`));
ALTER TABLE `test`.`dttest` ADD COLUMN `txt` CHAR(5) NULL AFTER `dt`;
-- also
-- Incorrect datetime value: '0000-00-00 00:00:00' for column 'dt' at row 1

SET SQL_MODE = ''; 
ALTER TABLE `test`.`dttest` ADD COLUMN `txt` CHAR(5) NULL AFTER `dt`;
-- success.

So WB must use some strict mode for its connection similar to my first example here.  Why it does I cannot tell.

I think this is a server bug.  The error does not make sense as long as there are no rows with a date value of 0000-00-00. I will open a separate issue for this.

Posted: http://bugs.mysql.com/bug.php?id=59062

o.k. there was one entry with a date equals 0  and after setting the value to a valid date it works. So maybe it's not a bug. But is there a way to set the SQL_MODE within the Workbench?

I closed my report.  Seems that I did a mistake (overlooked a row).

But this error will only occur in no_zero_date+strict_***_tables sql_mode (with no_zero_date alone it will return a warning - not an error).

Check SQL Editor tab in Preferences if you want to set default SQL mode to something specific.

I checked the SQLEditor preferences tab. The hint of the SQL_MODE textbox says enter a whitespace to reset the SQL_MODE. I tried this and after that adding columns works with columns containing a 0(zero) date. 

I removed the whitespace from the SQL_MODE textbox and restarted the workbench to check if the insert fails again. But it didn't. Seems that the reset did some permanent changes somewhere in the configuration of the Workbench.