Bug #71626 Extra SELECT@@sql_mode queries in batched INSERT
Submitted: 7 Feb 2014 14:36 Modified: 9 Jan 2015 6:13
Reporter: Trevor Samols Email Updates:
Status: Verified 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.