MySQL Bugs: #54173: Synchronize Model switches to traditional sql mode

Bug #54173	Synchronize Model switches to traditional sql mode
Submitted:	2 Jun 2010 8:26	Modified:	20 Jul 2012 7:23
Reporter:	Markus Warg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.2.29 CE	OS:	Windows (XP Pro)
Assigned to:		CPU Architecture:	Any

Description:
When doing an forward engineering OR synchronize model, workbench switches the db connection to traditional mode.
This causes problems when you try to synchronize a model in a productional database, i.e. if you have 0000-00-00 00:00:00 default values for datetime fields.

How to repeat:
Create table with datetime fields, insert record which sets the field to '0000-00-00 00:00:00', synchronize from workbench.

Suggested fix:
Use a more commonly used sql mode or at least provide a little better documentation on that configuration attribute. Maybe present a dropdown instead of having a text input field.

Bug #54006 was marked as a duplicate of this one.

Bug #55127 was marked as a duplicate of this one.

Tested with current version.

Seems you can enter an SQL Mode but when doing an sync, mode TRADITIONAL is used, regardless what you entered in the preferences.

The mode is used when fetching the current status from the db, but it is ignored when putting together the modifying sql script.

Also, it seems that an EMPTY mode is valid for mysql, it seems that an empty mode is default.
You can not set the mode to empty in the workbench and you can not COMBINE more than one mode, which should be possible due to the mysql documentation found here: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

This occurs in current 5.2.30 CE. This prevents synchronize scripts and forward engineer scripts from working completely. Dates are primarily the issue, when a default of '0000-00-00 00:00:00' is set. Removing the SQL_MODE settings at the top and bottom of the script resolve this, however, this prevents the Forward Engineer wizard from working at all, as the script cannot be edited before being executed, and instead, one must rely on copying the script, editing the offending lines, and running in a query window instead. There should be an option to select which SQL_MODE is desired, with one of those options being "use server's SQL_MODE".

I'm also experiencing trouble from this bug, dates are the main problem here and I also have to copy the synchronization script manually and run it in a query window to get synchronization to work in some way.

I'm running 5.2.33 and ran into this bug for the same reason -- I crafted a stored procedure that copies TIMESTAMP fields from existing tables into a new one.  The procedure works fine except when I sync it from MySQL Workbench, due to the forced TRADITIONAL sql_mode.

For those who trip into this bug, it rates as higher than an S3:  you really get stopped in your tracks.

I'll share my workaround here, for anyone else who Googles for this bug:

- Create your procedure within Workbench
- The first time you run through the sync process, count *FIVE* mouseclicks on NEXT (don't get me started on how silly this UI is).
- At the screen labeled "Preview database changes to be applied", look on the third line of output for this text:

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

- Edit out the second part, from the comma to the closing quote
- Proceed with the sync

Subsequent syncs won't pick up any diffs so until the next time you need to change the TIMESTAMP-afflicted procedure, you won't have to remember to stop at the Preview screen every time.

Alas my hoped-for workaround does not work:

SET SESSION sql_mode='';

From within the procedure, it apparently can't override settings made at time of procedure definition.

duplicate of bug #54173

This got marked as a dup of itself.  I searched but could not find the similar one which (presumably) got fixed.  If it didn't get fixed, please reopen.

This has been fixed as of the soon-to-be-released Workbench 5.2.41, and 
here's the changelog entry:

A new "Model: MySQL" preference was added for "Forward Engineering and
Synchronization" to configure "SQL_MODE".