Bug #107506 ADO.NET insert forgets to reset sql_mode
Submitted: 8 Jun 0:10 Modified: 16 Aug 20:17
Reporter: Shaohua Wang (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.26, 8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[8 Jun 0:10] Shaohua Wang
Description:
before insert, SET SESSION sql_mode='ANSI'; but forgets to reset it.
So some SQL using the same connection may fails due to sql mode change.

How to repeat:
Source/MySql.Data.Entity/Statements/InsertStatement.cs

public override void WriteSql(StringBuilder sql)
    {   
      // changes sql_mode to allow inserting data without identity columns
      if (ReturningSelect != null && ReturningSelect.Columns.Count > 0)
        sql.Append("SET SESSION sql_mode='ANSI';");

      sql.Append("INSERT INTO "); 
      Target.WriteSql(sql);

Suggested fix:
 SET SESSION sql_mode='DEFAULT'after insert.
[8 Jun 5:00] MySQL Verification Team
Hello Shaohua,

Thank you for the report and feedback.

regards,
Umesh
[16 Aug 20:17] Daniel Valdez
Posted by developer:
 
Setting the "sql-mode" in that instance, only affects the transaction in progress so it doesn't affect any other instruction afterwards. 
If you think this does affect further queries, please provide a test case where it can be reproduced and then we can re-open the bug or create another one to handle this issue.

Thanks.