MySQL Bugs: #39815: Using MySqlCommandBuilder with Batch Inserts causes

Bug #39815	Using MySqlCommandBuilder with Batch Inserts causes
Submitted:	2 Oct 2008 15:18	Modified:	22 Feb 2010 16:16
Reporter:	John Bayly (OCA)	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.2.3.0	OS:	Windows (XP Pro sp3 .Net v.2)
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any

Description:
When using the MySqlCommandBuilder to generate an INSERT COMMAND to be used with BatchUpdates an InvalidOperationException is throw with the mesage:
"When batching, the command's UpdatedRowSource property value of UpdateRowSource.FirstReturnedRecord or UpdateRowSource.Both is invalid."

It appears that by merely attaching a MySqlCommandBuilder to the MySqlDataAdapter the value for the Command's UpdatedRowSource property is reset from None to FirstReturnedRecord when Update() is called on the MySqlDataAdapter

How to repeat:
// Change to use the MySqlCommandBuilder
bool useMySqlCommandBuilder = false;
int rowsToAdd = 200;

string connString = "";
MySqlDataAdapter adapter = null;
MySqlCommand ins = null;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
try {
  MySqlCommand comm = new MySqlCommand();
  int changed = 0;
  comm.Connection = conn;

  Console.Out.WriteLine("Using useMySqlCommandBuilder={0}", useMySqlCommandBuilder);

  // Drop the table
  comm.CommandText = "DROP TABLE IF EXISTS tblXYZ";
  changed = comm.ExecuteNonQuery();
  Console.Out.WriteLine("Dropped tblXYZ: Changed {0} rows", changed);

  // Create the table
  comm.CommandText = "CREATE TABLE tblXYZ " +
    "(fldID INT NOT NULL, fldValue VARCHAR(255) NOT NULL, PRIMARY KEY(fldID))";
  changed = comm.ExecuteNonQuery();
  Console.Out.WriteLine("Created tblXYZ: Changed {0} rows", changed);

  // Create the DataAdapter
  adapter = new MySqlDataAdapter();
  adapter = new MySqlDataAdapter(
    "SELECT * FROM tblXYZ", conn);
  DataTable data = new DataTable();
  adapter.Fill(data);
  Console.Out.WriteLine("SELECT FROM tblXYZ: {0} rows", data.Rows.Count);

  // Create the Insert command
  MySqlCommandBuilder builder;

  if (useMySqlCommandBuilder) {
    builder = new MySqlCommandBuilder(adapter);
    ins = builder.GetInsertCommand();
  } else {
    ins = new MySqlCommand(
      "INSERT INTO tblXYZ(fldID, fldValue) VALUES (?p1, ?p2)", conn);
    ins.Parameters.Add("p1", MySqlDbType.Int32).SourceColumn = "fldID";
    ins.Parameters.Add("p2", MySqlDbType.String).SourceColumn = "fldValue";
  }

  // Set the Insert Command
  ins.UpdatedRowSource = UpdateRowSource.None;
  adapter.InsertCommand = ins;
  adapter.UpdateBatchSize = 100;

  // Add 100 rows
  for (int i = 0; i < rowsToAdd; i++) {
    DataRow row = data.NewRow();
    row["fldID"] = i + 1;
    row["fldValue"] = "ID = " + (i + 1);
    data.Rows.Add(row);
  }

  // Do the update
  changed = adapter.Update(data);
  Console.Out.WriteLine("INSERT INTO tblXYZ: {0} rows", changed);

} catch (Exception ex) {
  Console.Out.WriteLine(ex.Message + "\n" + ex.StackTrace);
  Console.Out.WriteLine("ins.UpdatedRowSource = {0}", ins.UpdatedRowSource);

} finally {
  conn.Close();
}

# Output
#Using useMySqlCommandBuilder=False
#Dropped tblXYZ: Changed 0 rows
#Created tblXYZ: Changed 0 rows
#SELECT FROM tblXYZ: 0 rows
#INSERT INTO tblXYZ: 200 rows

# Output
#Using useMySqlCommandBuilder=True
#Dropped tblXYZ: Changed 0 rows
#Created tblXYZ: Changed 0 rows
#SELECT FROM tblXYZ: 0 rows
#When batching, the command's UpdatedRowSource property value of #UpdateRowSource.FirstReturnedRecord or UpdateRowSource.Both is invalid.
#   at #System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs #rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
#   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs #rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
#   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, #DataTableMapping tableMapping)
#   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable #dataTable, DataTableMapping tableMapping)
#   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
#ins.UpdatedRowSource = FirstReturnedRecord

Suggested fix:
Call Dispose() on the MySqlCommandBuilderafter used it to create the command, but before the MySqlDataAdapter *Command property is set

ie:
builder = new MySqlCommandBuilder(adapter);
ins = builder.GetInsertCommand();
builder.Dispose();
adapter.InsertCommand = ins;

Note:
If the MySqlDataAdapter's *Command property is set before the MySqlCommandBuilder is Disposed, it will be set to null

Hi John and thanks for your report. This might well be duplicate of Bug#38411. What do you think?

Tonci,
Comparing the stacktraces, it does seem to be a similar bug, however as the code in Bug#38411 is auto generated by VS2008 and I can't even start to compare the sequence of events that are being called.

I added the MySql connector source to my solution so that I could step through the code when Update is called. As I suggested in the submission, it appears that the UpdatedRowSource is being reset, so I added added a breakpoint at:
  set_UpdatedRowSource in command.cs (line 836)
and saw from the stacktrace that it's being reset by:
  RowUpdating event handler in CommandBuilder.cs (line 270)

I can honestly say that I don't have an idea what UpdatedRowSource does (I'm looking through the code at the moment), however on a whim I commented out the line:
  args.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
and ran the batch insert with Wireshark running and could confirm that two insert commands were issued. This explains why disposing the MySqlCommandBuilder prevents this issue from occuring as the RowUpdating event is handled.

Commenting out the line isn't a solution as it's clearly needed, as I found out when I tried running the code on a table with the Primary Key being an Auto Increment value; it only inserted the first value. Again, using Wireshark, I checked what command is being issued and this is it:
  INSERT INTO `tipstrade_2`.`tblXYZ` (`fldValue`) VALUES ('Index 000')
  ; SELECT last_insert_id() AS `fldID`
  , ('Index 001'), ('Index 002'), ('Index 003'), ('Index 004')
  # New lines added for readability

So basically, the line:
  args.Command.CommandText += finalSelect;
should only be issued on the last command of the batch.

Of course, I'm still not sure why the UpdateRowSource is being explicitly set to UpdateRowSource.FirstReturnedRecord by the MySqlCommandBuilder

Looking into this again, I've realised that batch inserts shouldn't be done when adding to a table with an Auto Increment field. However if there is no Auto Increment field, the CommandBuilder shouldn't be reseting the UpdatedRowSource property.

Hence, I've submitted a diff for the MySqlCommandBuilder that does the following:
1). Create the Final Select statement before testing the UpdatedRowSource property
2). Test the Final Select statement, and if it's empty leave the RowUpdating handler
3). Modified the CreateFinalSelect method so that it can deal with a closed Connection as per bug #34657
4). Remove the StringBuilder, as it appeared to be a slight overkill as it was appended to only once

As far as I can tell it shouldn't interfere with the existing functionality.

@@ -275,16 +275,21 @@
 
             if (ReturnGeneratedIdentifiers)
             {
-                if (args.Command.UpdatedRowSource != UpdateRowSource.None)
-                    throw new InvalidOperationException(
-                        Resources.MixingUpdatedRowSource);
-                args.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
-                if (finalSelect == null)
-                    CreateFinalSelect();
+              // Get the Final Select if it's unset
+              if (finalSelect == null) CreateFinalSelect();
+
+              // Ignore if there is no FinalSelect set
+              // ie. There is no AutoIncrement column
+              if (finalSelect == "") return;
+
+              if (args.Command.UpdatedRowSource != UpdateRowSource.None)
+                  throw new InvalidOperationException(
+                      Resources.MixingUpdatedRowSource);
+              args.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
             }
 
-            if (finalSelect != null && finalSelect.Length > 0)
-                args.Command.CommandText += finalSelect;
+            if (!string.IsNullOrEmpty(finalSelect))
+              args.Command.CommandText += finalSelect;
         }
 
         /// <summary>
@@ -293,21 +298,43 @@
         /// </summary>
         private void CreateFinalSelect()
         {
-            StringBuilder select = new StringBuilder();
-
-            DataTable dt = GetSchemaTable(DataAdapter.SelectCommand);
-
-            foreach (DataRow row in dt.Rows)
-            {
-                if (!(bool)row["IsAutoIncrement"])
-                    continue;
+          // Check if the Insert command is being executed on a table
+          // with an AutoIncrement field
+          bool connOpened = false;
+          string autoIncrementField = null;
+          try {
+            // Ensure the Select command's connection is open
+            // As per bug #34657
+            // http://bugs.mysql.com/bug.php?id=34657
+            if (DataAdapter.SelectCommand.Connection.State != ConnectionState.Open) {
+              DataAdapter.SelectCommand.Connection.Open();
+              connOpened = true;
+            }
 
-                select.AppendFormat(CultureInfo.InvariantCulture, 
-                    "; SELECT last_insert_id() AS `{0}`", row["ColumnName"]);
+            // Get the Select command's schema, and check each field for
+            // and AutoIncrement flag
+            DataTable schema = GetSchemaTable(DataAdapter.SelectCommand);
+            foreach (DataRow field in schema.Rows) {
+              if ((bool)field["IsAutoIncrement"]) {
+                autoIncrementField = (string)field["ColumnName"];
                 break;
+              }
+            }
+            schema.Dispose();
+          } finally {
+            // Close the Select command's connection, so it's reset
+            // to it's original state
+            if (connOpened) {
+              DataAdapter.SelectCommand.Connection.Close();
             }
+          }
 
-            finalSelect = select.ToString();
+          if (autoIncrementField == null) {
+            finalSelect = "";
+          }else{
+            finalSelect = string.Format(CultureInfo.InvariantCulture,
+              "; SELECT last_insert_id() AS `{0}`", autoIncrementField);
+          }
         }
     }
 }

John, verified as described along with nice workaround (builder.Dispose()).

Thanks for your interest in MySQL.

Test case is provided. I used bare minimum connection string and 5.2 branch from svn.

This behavior was changed several versions back and we are no longer supporting 5.x versions.  We are currently on 6.2.  Please download and try one of our 6.x products.

This issue still affects me using the 6.2.5 version of Connector/Net.

Please disregard my last comment.