MySQL Bugs: #69593: Workbench not respecting SQL

Bug #69593	Workbench not respecting SQL_MODE parameter
Submitted:	26 Jun 2013 22:01	Modified:	2 Jul 2013 5:14
Reporter:	Chris Nanney	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.0.2	OS:	Windows (7x64 SP1)
Assigned to:		CPU Architecture:	Any

Description:
When syncing a model to a database, Workbench ignores the SQL_MODE parameter and always sets it to 'TRADITIONAL,ALLOW_INVALID_DATES'.

How to repeat:
Case 1:
------

http://cnanney-video.s3.amazonaws.com/workbench_modebug_1.mp4

You can see when I go to sync this model, that the SQL_MODE field is blank. Now, I expect this to mean I do not want Workbench to modify the SQL_MODE in any way.

This entire line:
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
should not even be there, let alone set to 'TRADITIONAL,ALLOW_INVALID_DATES'.

If the SQL_MODE is blank in the connection settings, don't attempt to alter it at all in the sync script.

Case 2:
-------

http://cnanney-video.s3.amazonaws.com/workbench_modebug_2.mp4

This time, I set the SQL_MODE to 'STRICT_ALL_TABLES'. Notice that the sync script still generates SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'. So even when I do try and set a custom mode, Workbench just ignores it.

Suggested fix:
Two things need to be fixed here:

Fix 1: If a stored connection has a blank SQL_MODE field, that means I don't want you to mess with it, so leave out all SQL_MODE references in the sync script.

Fix 2: If I do set a preferred SQL_MODE, use that in the resulting sync script.

I should add, this broken behavior is present in latest v5 release as well.

Hello Chris,

Thank you for the bug report. 
Verified as described on 6.0.2

Thanks,
Umesh

The SQL_MODE that is used in scripts is the one specified in Preferences -> Model: MySQL.

The one you set in the connection, is the one used in DB connections.

Fantastic, thanks for pointing that out.