MySQL Bugs: #54006: MySQL Workbench SQL Modeling SQL

Bug #54006	MySQL Workbench SQL Modeling SQL_MODE Forced
Submitted:	26 May 2010 20:29	Modified:	16 Jul 2010 14:38
Reporter:	P Lance Sheldon	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.21 RC, 5.2.25	OS:	Windows (SQL_MODE is Forced to value that may not match connected server)
Assigned to:		CPU Architecture:	Any

Description:
When connecting with MySQL Workbench 5.2.21 RC SQL Development SQL Editor, the Workbench interface forces SQL_MODE settings which don't match the settings of the server it's connecting to.  This occurs even though "Default SQL_MODE" in the Workbench Preferences SQL Editor dialog tab is blank.  This behavior is unexpected and can cause bogus scripting errors when trying to run otherwise error free scripts which I verified run fine in MySQL Query Browser.

How to repeat:
With the "Default SQL_MODE" set to blank in the preferences, make a new connection and ensure that the results of these statements match:
SELECT @@SESSION.sql_mode;
SELECT @@GLOBAL.sql_mode;

Suggested fix:
I suggest that the connection use the SQL_MODE settings of the server it's connecting to, unless the user specifies some other settings in the preferences dialog.  This could be accomplished by not sending a SQL_MODE statement on connection unless the user has specified something in preferences.

Oops, the bug is really in the Data Modeling Synchronize Model tool.  The script it generates adds the statement:
SET SQL_MODE='TRADITIONAL'
If the server you're comparing is set differently, this can cause the script to fail.

Many thanks for writing a bug report.

I am not able to follow you.

mysql> show variables like 'sql_mode';
sql_mode      | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI

Open Workench
Create diagram from existing database
Create a new table in that diagram
Sync model

Here is the script:

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

CREATE  TABLE IF NOT EXISTS `miracee`.`t` (
...

SET SQL_MODE=@OLD_SQL_MODE;
...

mysql> show variables like 'sql_mode';
sql_mode      | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI

After the script finished it will set back the sql mode.

I am not able to see the bug here.

Hi Susanne,

We have several tables with a DATETIME column default of '0000-00-00'.
This default value is not allowed with the "traditional" sql mode.  Therefore, errors are generated and the synch functionallity of workbench modeling fails.  I'm certain there are other cases where forcing a particular sql mode will cause errors.  A modeling tool should take what it sees and not add code that will change a database's characteristics which can make reproduction unreliable, unpredictable and potentially impossible.

Here's example sql demonstrating the failure:

    -- Code does not work:
    SET SQL_MODE = '';
    CREATE SCHEMA IF NOT EXISTS `SynchBugTest` CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
    USE `SynchBugTest`;
    -- forcing mode to traditional causes table create to generate an error
    SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
    CREATE TABLE IF NOT EXISTS `SynchBugTest`.`WeirdDefaults` (ID INT, MyDate DATETIME DEFAULT '0000-00-00');
    SET SQL_MODE=@OLD_SQL_MODE;

    -- Code works:
    SET SQL_MODE = '';
    CREATE SCHEMA IF NOT EXISTS `SynchBugTest` CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
    USE `SynchBugTest`;
    CREATE TABLE IF NOT EXISTS `SynchBugTest`.`WeirdDefaults` (ID INT, MyDate DATETIME DEFAULT '0000-00-00');

Thanks,
Lance

Could you please try version 5.2.22.Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hello,

I have the same problem with the forced SQL_Mode, I need also ZERO Dates in some tables as sample "delete_date" = '0000-00-00 00:00:00' means, row is not deleted.

A workaround for Workbench is, to execute the created SQL-Script in a Script-Tab without the line where the SQL_Mode is set, but it's not nice. I think the best will be a option to set the SQL_Mode per Model.

regards
Ricardo

I'd say this is a duplicate of bug #54173.