MySQL Bugs: #51615: MySqlDataAdapter silently ignores updates/inserts when UpdateBatchSize = 0

Bug #51615	MySqlDataAdapter silently ignores updates/inserts when UpdateBatchSize = 0
Submitted:	1 Mar 2010 16:57	Modified:	24 Sep 2010 10:19
Reporter:	Mathieu van Loon	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.2.2, 6.1.3, 6.0.3	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	MySqlDataAdapter, UpdateBatchSize

Description:
Observed the following behaviour when using the MySqlDataAdapter with UpdateBatchSize=0 to insert a large amount of records:

- input record set contains 27276 records
- target table is empty (eg no rows at all)
- In debugging mode, right before running adapter.Update(table) the table is confirmed to contain 27276 records
- after running the Update statement, program continues without exception thrown
- target table now contains 25782 records (1494 records are missing).

Workaround: setting UpdateBatchSize to a non-zero value (100 was tested) will result in the correct number of rows inserted. NOTE see http://bugs.mysql.com/bug.php?id=50123 to use this workaround - you need a recent connector release

Full example code is below:

DataTable table = new DataTable("tblTestQuestionStudyLevel");
            table.Columns.Add("qsQuestionId", typeof (UInt32));
            table.Columns.Add("qsStudyLevelId", typeof (UInt32));
            table.Columns.Add("qsApplicable", typeof (string));
            table.Columns.Add("qsModified", typeof(DateTime));

            using(MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                //MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT qsQuestionId, qsStudyLevelId, qsApplicable, qsModified FROM tblTestQuestionStudyLevel", connectionString);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.UpdateBatchSize = 0;
                adapter.ContinueUpdateOnError = false;
                adapter.InsertCommand =
                    new MySqlCommand(
                        "INSERT INTO tblTestQuestionStudyLevel(" + SELECT_FIELDS_SMALL +
                        ") VALUES (?qsQuestionId, ?qsStudyLevelId, ?qsApplicable, ?qsModified)", connection);
                adapter.InsertCommand.Parameters.Add("?qsQuestionId", MySqlDbType.UInt32, 4, "qsQuestionId");
                adapter.InsertCommand.Parameters.Add("?qsStudyLevelId", MySqlDbType.UInt32, 4, "qsStudyLevelId");
                adapter.InsertCommand.Parameters.Add("?qsApplicable", MySqlDbType.String, 50, "qsApplicable");
                adapter.InsertCommand.Parameters.Add("?qsModified", MySqlDbType.DateTime, 4, "qsModified");
                adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

                studies.ForEach(
                    level =>
                    table.Rows.Add(level.QuestionId, level.StudyLevelId, level.Applicable.AsFormalString,
                                   level.ModificationDate));

                adapter.Update(table);
            }

How to repeat:
prepare a 'large' dataset and try to insert it using the MySqlDataAdapter.

Hi Mathieu and thanks for your report.

Alas, it is not complete... Can you please post full code needed (along with connection string which is very very important). Also, can you use my code from Bug#50123 or at least make all necessary DDL/DML statements available in your test?
Finally, did you check MySQL server's error log? I would really like to know if execution was interrupted on server side.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Feedback is still needed.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".