Bug #71626 Extra SELECT@@sql_mode queries in batched INSERT
Submitted: 7 Feb 2014 14:36 Modified: 24 Nov 2021 16:32
Reporter: Trevor Samols Email Updates:
Status: Closed Impact on me:
Category:Connector / NET Severity:S2 (Serious)
Version:6.8.3, 6.9.5 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[7 Feb 2014 14:36] Trevor Samols
Occurs in use of MySqlDataAdapter with UpdateBatchSize = 0, or > 1 (i.e. with batching), when inserting multiple rows using a standard INSERT command.

The INSERT queries are batched correctly, but additional, presumably 
unnecessary, SELECT@@sql_mode queries are made (one for every row).
These caused a significant performance hit when connecting
to a remote server. Saving a table of 3000 rows aborted,
presumably due to a time-out.

The problem appears to be in the method

A workaround is to create an INSERT command with a leading white space
so that the code in this method is skipped, but this also means one loses
the reformatting of the queries in multi-value form.

How to repeat:
Save a table containing multiple rows to the DB as above
using MySqlDataAdapter with UpdateBatchSize = 0, or > 1.
The problem will occur with any query for which the first
6 characters are INSERT, so in particular the insert command
automatically generated by MySqlCommandBuilder.

The log file shows the extra SELECT@@sql_mode queries, one for every row.

Suggested fix:
Amend code in MySqlCommand.GetCommandTextForBatching()
[7 Feb 2014 14:39] Trevor Samols
Correct connector version number
[9 Jan 2015 6:13] Chiranjeevi Battula
Hello Trevor Samols,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.5.

[24 Nov 2021 16:32] Christine Cole
Posted by developer:
Fixed as of the upcoming MySQL Connector/NET 8.0.28 release, and here's the proposed changelog entry from the documentation team:

When performing a batched insert using MySqlDataAdapter, the
MySqlCommand.GetCommandTextForBatching() method queried @@sql_mode for
each insert. This fix eliminates the redundant actions and modifies the
behavior to use the sql_mode property, which is obtained at the beginning
of the connection.

Thank you for the bug report.