Bug #107506 ADO.NET insert forgets to reset sql_mode

• Submitted: 8 Jun 2022 0:10
• Modified: 16 Aug 2022 20:17
• Reporter: Shaohua Wang (OCA)
• Status: Not a Bug
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 8.0.26, 8.0.29
• OS: Any
• CPU Architecture: Any

[8 Jun 2022 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 2022 5:00] MySQL Verification Team

Hello Shaohua,

Thank you for the report and feedback.

regards,
Umesh

[16 Aug 2022 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.