MySQL Bugs: #79678: Server sql_mode replaced with ANSI only by the connector

Bug #79678	Server sql_mode replaced with ANSI only by the connector
Submitted:	16 Dec 2015 17:54	Modified:	8 Nov 2022 21:13
Reporter:	Lionel Najman	Email Updates:
Status:	Closed	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

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

Hello Lionel Najman,

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

Thanks,
Chiranjeevi.

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.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.32 release, and here's the proposed changelog entry from the documentation team:

Connector/NET sets the @@SESSION.sql_mode value to 'ANSI' without
considering the current value. This issue now is fixed by appending 'ANSI'
mode to the current @@SESSION.sql_mode value.

Thank you for the bug report.

Posted by developer:
 
In the final patch for this fix, it was decided that setting any value to "sql_mode" was not necessary, so the instruction was removed.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.32 release, and here's the proposed changelog entry from the documentation team:

When Connector/NET set the @@SESSION.sql_mode value to 'ANSI', it did so
without considering the current value.

Thank you for the bug report.