MySQL Bugs: #34657: MySqlDataAdapter.Update(DataRow[] rows) fails with MySqlCommandBuilder

Bug #34657	MySqlDataAdapter.Update(DataRow[] rows) fails with MySqlCommandBuilder
Submitted:	19 Feb 2008 10:52	Modified:	9 Sep 2009 12:07
Reporter:	Franco A.
Status:	Closed
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	5.1.5, 5.2	OS:	Microsoft Windows
Assigned to:		Target Version:
Tags:	MySqlCommandBuilder, MySqlDataAdapter
Triage:	D3 (Medium)

Description:
Using a dataadapter with linked MySqlCommandBuilder throws an Exception with message
"Connection must be valid and open" while trying to call da.Update(DataRow[] rows)
listing a new added row in rows array.

How to repeat:
Use this sample table:
'CREATE TABLE `test3` (
  `id` int(11) NOT NULL default '0',
  `txt` varchar(100) default NULL,
  `val` decimal(11,2) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

And this code snippet:

string s = "Server=localhost;Database=test;Uid=root;pwd=password";
MySqlConnection cn = new MySqlConnection(s);
s = "SELECT * FROM test3";
MySqlDataAdapter da = new MySqlDataAdapter(s, cn);

MySqlCommandBuilder bld = new MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);

ds.Tables[0].Rows[0]["val"] = 99.9M;
da.Update(new DataRow[] { ds.Tables[0].Rows[0] });

DataRow r = ds.Tables[0].NewRow();
r["id"] = 4;
r["txt"] = "sds";
r["val"] = 113.2M;
ds.Tables[0].Rows.Add(r);
da.Update(new DataRow[] { r });

The last row fails with "Connection must be valid and open" thrown by
MySqlCommand.CheckState().

Here the stack trace:

MySql.Data.dll!MySql.Data.MySqlClient.MySqlCommand.CheckState() Riga 279	C#
MySql.Data.dll!MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(System.Data.CommandBehavior
behavior = SchemaOnly | KeyInfo) Riga 344 + 0x8 byte	C#
MySql.Data.dll!MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(System.Data.CommandBehavior
behavior = SchemaOnly | KeyInfo) Riga 794 + 0xa byte	C#
[Codice esterno]	
MySql.Data.dll!MySql.Data.MySqlClient.MySqlCommandBuilder.CreateFinalSelect() Riga 270 +
0x24 byte	C#
MySql.Data.dll!MySql.Data.MySqlClient.MySqlCommandBuilder.RowUpdating(object sender =
{MySql.Data.MySqlClient.MySqlDataAdapter},
MySql.Data.MySqlClient.MySqlRowUpdatingEventArgs args =
{MySql.Data.MySqlClient.MySqlRowUpdatingEventArgs}) Riga 255 + 0x7 byte	C#
MySql.Data.dll!MySql.Data.MySqlClient.MySqlDataAdapter.OnRowUpdating(System.Data.Common.RowUpdatingEventArgs
value = {MySql.Data.MySqlClient.MySqlRowUpdatingEventArgs}) Riga 238 + 0x1c byte	C#

Possible solution:

--- CommandBuilder.cs	2007-11-05 16:14:42.000000000 +0100
+++ CommandBuilder.cs	2008-02-20 09:27:39.734375000 +0100
@@ -266,9 +266,15 @@
         private void CreateFinalSelect()
         {
             StringBuilder select = new StringBuilder();
-
-            DataTable dt = GetSchemaTable(DataAdapter.SelectCommand);
-
+						try
+						{
+							DataAdapter.SelectCommand.Connection.Open();
+							DataTable dt = GetSchemaTable(DataAdapter.SelectCommand);
+						}
+						finally
+						{
+							DataAdapter.SelectCommand.Connection.Close();
+						}
             foreach (DataRow row in dt.Rows)
             {
                 if (!(bool)row["IsAutoIncrement"])

Sorry, my corrected workaround is:

--- CommandBuilder_orig.cs	2007-11-05 16:14:42.000000000 +0100
+++ CommandBuilder.cs	2008-03-04 16:41:51.921875000 +0100
@@ -266,9 +266,22 @@
         private void CreateFinalSelect()

         {

             StringBuilder select = new StringBuilder();

-

-            DataTable dt = GetSchemaTable(DataAdapter.SelectCommand);

-

+						DataTable dt;

+						bool doclose = false;

+						try

+						{

+							if (DataAdapter.SelectCommand.Connection.State != ConnectionState.Open)

+							{

+								DataAdapter.SelectCommand.Connection.Open();

+								doclose = true;

+							}

+							dt = GetSchemaTable(DataAdapter.SelectCommand);

+						}

+						finally

+						{

+							if (doclose)

+								DataAdapter.SelectCommand.Connection.Close();

+						}

             foreach (DataRow row in dt.Rows)

             {

                 if (!(bool)row["IsAutoIncrement"])

Hi Franco and sorry for the long delay.

Now, I believe your first statement causes problem (update) as table is empty (or at
least it's empty in your example). After that, Bug#37991 kicks in and you have connection
being closed. Can you please try c/NET 5.1.7 (at least) and inform me of result. I have no
problems when 1st row is added and using 5.2 latest sources.

No problem detected with 5.1 latest sources too:

         try{
            string s = "Server=localhost;Database=test;Uid=root;pwd=***;port=***";
            MySqlConnection cn = new MySqlConnection(s);
            cn.Open();
            s = "SELECT * FROM bug34657";
            MySqlDataAdapter da = new MySqlDataAdapter(s, cn);
            Console.WriteLine("CP 1");
            MySqlCommandBuilder bld = new MySqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);
            Console.WriteLine("CP 2");
            if (cn.State == ConnectionState.Closed) { Console.WriteLine("Conn closed 1");
}
            ds.Tables[0].Rows[0]["val"] = 99.9M;
            da.Update(new DataRow[] { ds.Tables[0].Rows[0] });
            Console.WriteLine("CP 3");
             if (cn.State == ConnectionState.Closed) { Console.WriteLine("Conn closed
2"); }
            DataRow r = ds.Tables[0].NewRow();
            Console.WriteLine("CP 4");
            r["id"] = 4;
            r["txt"] = "sds";
            r["val"] = 113.2M;
            ds.Tables[0].Rows.Add(r);
            Console.WriteLine("CP 5");
            if (cn.State == ConnectionState.Closed) { Console.WriteLine("Conn closed 3");
}
            da.Update(new DataRow[] { r });
            if (cn.State == ConnectionState.Closed) { Console.WriteLine("Conn closed 4");
}
            Console.WriteLine("CP 6");
        }
        catch (Exception ex)
        {
            Assert.Fail(ex.Message);
        }

Server version: 5.0.68-pb10-log MySQL Pushbuild Edition, build 10

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `bug34657` (
    ->   `id` int(11) NOT NULL default '0',
    ->   `txt` varchar(100) default NULL,
    ->   `val` decimal(11,2) default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.17 sec)

mysql> select * from bug34657;
Empty set (0.00 sec)

mysql> insert into bug34657 values (0, "Some text", 15.25);
Query OK, 1 row affected (0.08 sec)

--Do test above--

mysql> select * from bug34657;
+----+-----------+--------+
| id | txt       | val    |
+----+-----------+--------+
|  0 | Some text |  99.90 |
|  4 | sds       | 113.20 |
+----+-----------+--------+
2 rows in set (0.00 sec)

Hi,
You're right, I forgot to add the INSERT statements to the report to make the first
Update work. 
Where can I found a snapshot for 5.1.7 and 5.2? Should I compile from source? If yes, are
they still under svn?
Thanks,
Frank

Franco, snapshots are rather old so I advise building on your own. Sources are available
via SVN as you guessed.

Seems I'm not able to connect so the svn sources anymore, any hints?

Hi,
Please remove the line "cn.Open();" in your example. 
In my environment, having a single record in the table with id=1, the first da.Update()
works while the second one (which should execute an insert) gives "Connection must be
valid and open". I don't know if the correct behaviour is that connection must be open
but it is indeed a strange behaviour to me.

For example, docs for SqlDataAdapter class (look at the constructor description) say that
this connection, if closed, will be opened and then closed by the implementation itself.

I tested against sources for 5.2.
Thanks

Hmmm, we are back on specs question... Will see what I can dig out.

I've just run into this bug with Connector/Net 5.2.3 using the MySqlCommandBuilder for
MySqlDataAdapter command generation. On a table already containing rows as retrieved from
the database, if the first update action performed is a DELETE, a subsequent INSERT will
fail with:

---
Connection must be valid and open.

   at MySql.Data.MySqlClient.MySqlCommand.CheckState()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbCommandBuilder.GetSchemaTable(DbCommand sourceCommand)
   at MySql.Data.MySqlClient.MySqlCommandBuilder.CreateFinalSelect()
   at MySql.Data.MySqlClient.MySqlCommandBuilder.RowUpdating(Object sender,
MySqlRowUpdatingEventArgs args)
   at MySql.Data.MySqlClient.MySqlDataAdapter.OnRowUpdating(RowUpdatingEventArgs value)
   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(DataSet dataSet, String srcTable)
   at Library first chance exception of type 'System.InvalidOperationException' occurred
in MySql.Data.dll
s.Library.Method(String table) in D:\application\library\codefile.cs:line 604
---

I'm currently using the manual open/close workaround above to mitigate the issue,
however, this behavior does seem inconsistent with the "automatic" nature of the
MySqlDataAdapter and the MySqlCommandBuilder combination.

Truly, some things should have happened automagically. Removing cn.Open() from my test
case produces the error.

Reggie?

I think problem reported in Bug#38411 has the same origin.

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

711 Reggie Burnett	2009-08-25
      - fixed bug that causes a 'connection not open' error when using a commandbuilder
with
        a data adapter and inserts are used (bug #34657)

fixed in 5.2.8 and 6.0.5.  This fix has been applied to 6.1.2 but we will be doing
something a little different there.

An entry was added to the 5.2.8 and 6.0.5 changelogs:

Using a DataAdapter with a linked MySqlCommandBuilder the following exception was thrown
when trying to call da.Update(DataRow[] rows):

Connection must be valid and open