MySQL Bugs: #71626: Extra SELECT@@sql_mode queries in batched INSERT

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:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.8.3, 6.9.5	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any

Description:
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
MySqlCommand.GetCommandTextForBatching()

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()

Correct connector version number

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.

Thanks,
Chiranjeevi.

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.