Bug #79678 Server sql_mode replaced with ANSI only by the connector
Submitted: 16 Dec 2015 17:54 Modified: 19 Jan 2016 7:52
Reporter: Lionel Najman Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.9.8 OS:Any
Assigned to: CPU Architecture:Any
Tags: SQL_MODE, stric

[16 Dec 2015 17:54] Lionel Najman
Description:
Hi,

I'm using .Net connector latest version (6.9.8 ) with entity Framework.

I've enabled strict mode in my mysql server (for example to avoid the 2038 timestamp "bug" serverside)

I've tested this strict mode sucessfully with MySQL Workbench.

My problem is that behaviour is totally ignored using .Net MySQL Connector. Enabling general log points me out that the connector is sending a session sql_mode

2015-12-16T17:21:44.438980Z    7 Query	SET SESSION sql_mode='ANSI';
2015-12-16T17:21:44.438980Z    7 Query	INSERT INTO

But the connector documentation doesn't tell how to enforce sql_mode with entity Framework...

Moreover My.SqlData.Entity.InsertStatement source code confirms me that there is an awfull bug!!!

public override void WriteSql(StringBuilder sql)
        {
            if (this.ReturningSelect != null && this.ReturningSelect.Columns.Count > 0)
            {
                sql.Append("SET SESSION sql_mode='ANSI';");
            }
            sql.Append("INSERT INTO ");
            this.Target.WriteSql(sql);
            if (this.Sets.Count > 0)
            {
                sql.Append("(");
                base.WriteList(this.Sets, sql);
                sql.Append(")");
            }
            sql.Append(" VALUES ");
            sql.Append("(");
            base.WriteList(this.Values, sql);
            sql.Append(")");
            if (this.ReturningSelect != null)
            {
                sql.Append(";\r\n");
                this.ReturningSelect.WriteSql(sql);
            }
        }

sql_mode is set to ANSI, being careless of global sql_mode!!! ANSI mode should be added to existing global options, it must not replace global mode

(and in a perfect world we should be able to specify our session sql_mode, whild should be merged with ANSI mode in this case!!!)

So Am I really stuck? Or is there a way to trully enable strict mode with entity Framework and the MySQL .Net Connector?

Thanks for your help...

How to repeat:
Use .Net entity Framework to make an insert

Suggested fix:
sql.Append("SET SESSION sql_mode='ANSI';");

this line should retrieve GLOBAL sql_mode (or connector configuration sql_mode) AND merge options
[19 Jan 2016 7:52] Chiranjeevi Battula
Hello Lionel Najman,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.
[15 May 2017 20:36] Michael Hogue
Any updates on this? It's been over 17 months. 

Is there any way to provide access to the bug notes on what the original issue fixes? Bug #16494585. (Per the connector net release notes: A workaround was to set global sql_mode = "ANSI";. Bug #16494585)

The fact that the sql_mode is being changed to ANSI (adding ANSI and removing STRICT TRANS TABLES) is so bad in our environment that we're considering launch with a customized build of the Connector Net where we've patched it ourselves. 

Whatever bug #16494585 is, if it is benign in some environments, could we not get a connector net connection string option or something to disable the damaging behavior (to tell the Connector Net that the issue fixed in bug 16494585 doesn't affect our environment, and we want it to just leave our server sql mode alone?) This seems like it was a breaking change intentionally released into the wild without a way to disable it (and I can't tell what the changing of the sql mode is actually fixing.)

Thanks for any info that can be provided.