MySQL Bugs: #38411: "Connection must be valid and open" When UpdateBatchSize

Bug #38411	"Connection must be valid and open" When UpdateBatchSize > 1
Submitted:	28 Jul 2008 16:43	Modified:	22 Jul 2010 13:27
Reporter:	Keith Jones	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.2.5	OS:	Windows (Visual Studio 2008)
Assigned to:	Tony Bedford	CPU Architecture:	Any
Tags:	mysql.net tableadapter updatebatchsize visual studio 2008

Description:
When "UpdateBatchSize" is anything but "1", an exception is thrown that says "Connection must be valid and open".  This happens even when "UpdatedRowSource" is "OutputParameters" or "None".  This occurs in MySQL Connector/Net 5.2.2 in Visual Studio 2008.

How to repeat:
1) Create a database.  
2) Auto generate (add) the datasets and tableadapters via Visual Studio 2008.  
3) Enable batched commands with the following code:

MyTableAdapter.Adapter.UpdateBatchSize = 2;

4) Set the UpdatedRowSource as documented:

MyTableAdapter.Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
            
MyTableAdapter.Adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

MyTableAdapter.Adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

5) Add a ton of data to your in memory dataset and appropriate datatable.

6) Synchronize your datatable with your MySql Database (this is where the exception is thrown):

MyTableAdapter.Update(MyDataSet.MyDataTable);

Note that if UpdatedRowSource == 1, then this code above works correctly.

Suggested fix:
When UpdateBatchSize is > 1, several commands must be sent to the database in order to speed up the inserts without throwing this exception while coding in Visual Studio 2008.

In an effort to have this bug verified and fixed, I am attaching a program, called "elogbook". You can create the database with the included elogbook_mysql.sql file.  Then, give permissions to a user named "elogbook" with a password "elogbook" within MySQL.  Then, you can use this Visual Studio 2008 solution to compile and run the sample program that displays this bug.  Upon running, click the "Add Data" button to add some data to the DataGridView.  Next, click the "Commit" button to attempt to write to the database the newly added data when the UpdateBatchSize = 2.  The program will give you the error I initially documented.

I tested this on Visual Studio 2008 SP1 and the error still occurs.  

When you use UpdateBatchSize = 1, the database updates are so slow for 1,000 rows or more that MySql, in my opinion, becomes unusable for the project I am currently working on.

The elogbook sample program to recreate this bug.

Attachment: eLogBook.zip (application/binary, text), 43.25 KiB.

Hello Keith and thanks for your report. It appears to me that your problem is same as one reported in Bug#34657. Can you verify this please?

At this time, I am not fully grasping the similarities other than just resulting with the same error code.  This error seems to be related to the "UpdateBatchSize" value, which I don't see touched in the other bug's comments.  Is there something I can do to help you further verify something?  Let me know and I will try my best.

I have moved this bug, that was originally submitted in mid 2008, from version 5.2.2 to 5.2.5 of the connector/net since 5.2.5 is the most current version on the download page.  I have verified that in version 5.2.5 this bug is still active and prevents multiple row updates.  

I am very much interested in helping to solve this bug as I have a stalled project waiting specifically on this issue.  If UpdateBatchSize is 1, data import operations will take a significant amount of time (unusable, in my opinion when 1,000's of rows are being updated or added simultaneously) whereas if UpdateBatchSize is > 1 the time the user must wait per updated/added row will be significantly less.

If there is any other information I can provide that would be helpful, please do not hesitate to ask.

Hi Keith.

Finally I have VS2008 but your solution just does not load.
Now, in publicly available "test" directory od c/NET driver you may find several tests for update batch of which all work:
            MySqlConnection con = new MySqlConnection();
            con.ConnectionString = "DataSource=**;Database=test;UserID=**;Password=**;PORT=**;logging=True;allow batch=true";
            con.Open();

            MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug38411", con);
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = "CREATE TABLE `bug38411`(id INT, name VARCHAR(20), PRIMARY KEY(id)) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
            cmdCreateTable.ExecuteNonQuery();

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM bug38411", con);
            MySqlCommand ins = new MySqlCommand("INSERT INTO bug38411 (id, name) VALUES (?p1, ?p2)", con);
            da.InsertCommand = ins;
            ins.UpdatedRowSource = UpdateRowSource.None;
            ins.Parameters.Add("?p1", MySqlDbType.Int32).SourceColumn = "id";
            ins.Parameters.Add("?p2", MySqlDbType.VarChar, 20).SourceColumn = "name";

            DataTable dt = new DataTable();
            da.Fill(dt);

            for (int i = 1; i <= 100; i++)
            {
                DataRow row = dt.NewRow();
                row["id"] = i;
                row["name"] = "name " + i;
                dt.Rows.Add(row);
            }

            da.UpdateBatchSize = 10;
            da.Update(dt);

            dt.Rows.Clear();
            da.Fill(dt);
            Assert.AreEqual(100, dt.Rows.Count);
            for (int i = 0; i < 100; i++)
            {
                Assert.AreEqual(i + 1, dt.Rows[i]["id"]);
                Assert.AreEqual("name " + (i + 1), dt.Rows[i]["name"]);
            }
      }

Tonci,

Thanks for getting back to me. I'm not sure why the example code will not open for you. It may depend on the database being up and running, but I do not remember. I think I may have included the SQL commands to create that database. I just tried it on a fresh install of VS2008 and it opened fine.

I think we may be going at the problem 2 different ways. It looks like in the code you attached that you wrote all the database manipulation by hand, rather than auto generating it through VS08's drag/drop functionality. In my bug submission, I said you had to auto generate all of the structures for the database (my original instructions are below). That is how everything was created in the sample I attached last year. I can hand craft it as presented in your example, but I believe that is a different situation than the bug I originally reported.

Let me know if there is anything else I can look up or add for you.

Take care,
Keith

Here is a summary of the problem:

How to repeat:
1) Create a database.
2) Auto generate (add) the datasets and tableadapters via Visual Studio 2008.
3) Enable batched commands with the following code:

MyTableAdapter.Adapter.UpdateBatchSize = 2;

4) Set the UpdatedRowSource as documented:

MyTableAdapter.Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

MyTableAdapter.Adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

MyTableAdapter.Adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

5) Add a ton of data to your in memory dataset and appropriate datatable.

6) Synchronize your datatable with your MySql Database (this is where the exception is
thrown):

MyTableAdapter.Update(MyDataSet.MyDataTable);

Note that if UpdatedRowSource == 1, then this code above works correctly.

Keith, I am trying to get this working but so far, no luck. What I'm missing is:

>> 3) Enable batched commands with the following code:

>> MyTableAdapter.Adapter.UpdateBatchSize = 2;

What is the class of MyTableAdapter? Did you put "Allow Batch=true" in connection string?

>> 4) Set the UpdatedRowSource as documented:
>> MyTableAdapter.

What is documented and where?

MyTableAdapter is a table adapter.  When auto generated, "Allow Batch=true" was not added to the connection string.  I just added it by hand and there was no difference.  The program still failed with the same error.

The documentation was either Microsoft MSDN help or the MySQL help, I do not recall which now since I looked it up last year.

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/113366

824 Vladislav Vaintroub	2010-07-12
      Bug#38411:
      
      When batching is used in MySqlDataAdapter, connection was not opened automatically in 
      MySqlDataAdapter.Update(). 
      
      This resulted into InvalidOperationException (exception text "connection must be valid and open")
      Fix to behave more like SQL Server :if connection is closed, open it  for the duration of update operation.

fixed in 6.0, 6.1, 6.2, 6.3

An entry has been added to the 6.0.7, 6.1.5, 6.2.4, and 6.3.3 changelogs:

When batching was used in MySqlDataAdapter, a connection was not opened automatically in MySqlDataAdapter.Update(). This resulted in an InvalidOperationException exception being generated, with the message text “connection must be valid and open”.

MySQL Connector/NET has been changed to behave more like SQL Server: if the connection is closed, it is opened for the duration of update operation.