MySQL Bugs: #50123: Batch updates bug when UpdateBatchSize

Bug #50123	Batch updates bug when UpdateBatchSize > 1
Submitted:	6 Jan 2010 22:29	Modified:	25 Feb 2010 10:11
Reporter:	Alexander Caravitis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.2.2	OS:	Windows
Assigned to:	Reggie Burnett	CPU Architecture:	Any
Tags:	Batch updates

Description:
Using MySQL Connector/Net 6.1.2, to perform batch updates. 

When seting UpdateBatchSize property > 1 only the first row is being applied to the database. When usign the value 1 everything works fine but without batching. 

Watched the statements which were coming to mySql server, and realized that there is a bug. When using a SourceColumn as parameter to the MySqlCommand, only the first statement is supplied with the value of the sourceColumn. I provide you with an example:
Supposing that we have an procedure named "usp_AddRecord" and sending batches of 2 records, statements that came to MySql is in the following form:

Call usp_AddRecord(12);usp_AddRecord;usp_AddRecord;

How to repeat:
Use MySQL Connector/Net 6.1.2, to perform batch updates

Test case:
            // Change to use the MySqlCommandBuilder
            bool useMySqlCommandBuilder = false;
            int rowsToAdd = 20;
            MySqlDataAdapter adapter = null;
            MySqlCommand ins = null;
            MySqlCommand ins2 = null;

            string connString = "DataSource=opensol;Database=test;UserID=**;Password=**;PORT=**;logging=True;allow batch=true;use procedure bodies=true;pooling=false";
            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 bug50123";
                changed = comm.ExecuteNonQuery();
                Console.Out.WriteLine("Dropped bug50123: Changed {0} rows", changed);

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

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

                // Create the Insert command
                MySqlCommandBuilder builder;

                if (useMySqlCommandBuilder)
                {
                    builder = new MySqlCommandBuilder(adapter);
                    ins = builder.GetInsertCommand();
                    //WA
                    builder.Dispose();
                }
                else
                {
                    ins = new MySqlCommand(
                      "INSERT INTO bug50123(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 = 10;

                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 bug50123: {0} rows", changed);

                //UPDATE VIA SP
                comm.CommandText = "DROP PROCEDURE IF EXISTS pbug50123";
                comm.ExecuteNonQuery();
                comm.CommandText = "CREATE PROCEDURE pbug50123(" +
                    "IN pfldID INT, IN pfldValue VARCHAR(50)) " +
                    "BEGIN INSERT INTO bug50123(fldID, fldValue) " +
                    "VALUES(pfldID, pfldValue); END";
                comm.ExecuteNonQuery();

                // Set the Insert Command
                ins2 = new MySqlCommand();
                ins2.Connection = conn;
                ins2.CommandText = "pbug50123";
                ins2.CommandType = CommandType.StoredProcedure;
                ins2.Parameters.Add("pfldID", MySqlDbType.Int32).SourceColumn = "fldID";
                ins2.Parameters.Add("pfldValue", MySqlDbType.String).SourceColumn = "fldValue";
                ins2.UpdatedRowSource = UpdateRowSource.None;
                adapter.InsertCommand = ins2;
                adapter.UpdateBatchSize = 10;

                for (int i = 21; i < 41; 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 bug50123 via SP: {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();
            }
        }

Hi Alexander and thanks for your report.

Verified as described using test case pasted above.

Output:
Using useMySqlCommandBuilder=False
Dropped bug50123: Changed 0 rows
Created bug50123: Changed 0 rows
SELECT FROM bug50123: 0 rows
INSERT INTO bug50123: 20 rows
WindowsFormsApplication1.vshost.exe Information: 0 : Retrieving procedure metadata for `test`.`pbug50123` from server.
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in System.Data.dll
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pbug50123; pbug50123; pbug50123; pbug50123; pbug50123; pbug50123; pbug50123; pbu' at line 1
   at ...

Further more, there are several problems with latest sources and various. I'll be filing new bug reports for that.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/100888

795 Reggie Burnett	2010-02-19
      - fixed bug where a commands batchable command text was not getting reset when the 
        command text was reset (bug #50444)
      - fixed bug where using a currently non-batchable command in a batch would throw an exception
        (bug #50123)

fixed in 6.0.6, 6.1.4, 6.2.3, 6.3.1+

An entry has been added to the changelogs 6.0.6, 6.1.4, 6.2.3, 6.3.1:

When the UpdateBatchSize property was set to a value greater than 1, only the first row was applied to the database.