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) Email Updates:
Status: Not a Bug Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.26, 8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[8 Jun 2022 0:10] Shaohua Wang
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:

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 "); 

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.

[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.