Bug #34657 MySqlDataAdapter.Update(DataRow[] rows) fails with MySqlCommandBuilder
Submitted: 19 Feb 2008 9:52 Modified: 9 Sep 2009 10:07
Reporter: Franco A. Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:5.1.5, 5.2 OS:Windows
Assigned to: CPU Architecture:Any
Tags: MySqlCommandBuilder, MySqlDataAdapter

[19 Feb 2008 9:52] Franco A.
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#
[20 Feb 2008 8:30] Franco A.
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"])
[4 Mar 2008 15:47] Franco A.
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"])
[29 Jul 2008 10:43] Tonci Grgin
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.
[29 Jul 2008 10:49] Tonci Grgin
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)
[30 Jul 2008 7:23] Franco A.
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
[30 Jul 2008 10:13] Tonci Grgin
Franco, snapshots are rather old so I advise building on your own. Sources are available via SVN as you guessed.
[4 Aug 2008 14:43] Franco A.
Seems I'm not able to connect so the svn sources anymore, any hints?
[5 Aug 2008 7:48] Franco A.
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
[11 Aug 2008 19:33] Tonci Grgin
Hmmm, we are back on specs question... Will see what I can dig out.
[10 Oct 2008 17:16] Marshall Macy II
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.
[21 Oct 2008 16:23] Tonci Grgin
Truly, some things should have happened automagically. Removing cn.Open() from my test case produces the error.

Reggie?
[21 Oct 2008 17:06] Tonci Grgin
I think problem reported in Bug#38411 has the same origin.
[25 Aug 2009 16:33] Bugs System
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)
[25 Aug 2009 16:37] Reggie Burnett
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.
[9 Sep 2009 10:07] Tony Bedford
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
[26 Apr 2010 20:16] Matthew Bilek
This still occurs on the 6.1.2 version.
[6 Sep 2010 12:03] Tonci Grgin
Matthew, running same test case against latest sources shows no problems:
                //DROP TABLE IF EXISTS bug34657;
                //CREATE TABLE bug34657 (id INT, expr INT,txt VARCHAR(20), val DECIMAL(11,2), PRIMARY KEY(id));
                //INSERT INTO bug34657 VALUES(1, 2,"Tri", 0);
                string s = "Server=***;Database=test;Uid=***;pwd=***;logging=true";//;port=5068";
                MySqlConnection cn = new MySqlConnection(s);
                //cn.Open(); <<<
                s = "SELECT * FROM bug34657";
                MySqlDataAdapter da = new MySqlDataAdapter(s, cn);
                Console.WriteLine("CP 1");
--<cut>--
Output:
CP 1
mysql Information: 1 : 1: Connection Opened: connection string = 'server=***;database=test;User Id=***;password=***;logging=True'
mysql Information: 3 : 1: Query Opened: SHOW VARIABLES
mysql Information: 4 : 1: Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=268, skipped rows=0, size (bytes)=6334
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SHOW COLLATION
mysql Information: 4 : 1: Resultset Opened: field(s) = 6, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=127, skipped rows=0, size (bytes)=3958
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET NAMES utf8
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET character_set_results=NULL
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 10 : 1: Set Database: test
mysql Information: 3 : 1: Query Opened: SELECT * FROM bug34657
mysql Information: 4 : 1: Resultset Opened: field(s) = 4, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=13
mysql Information: 6 : 1: Query Closed
CP 2
Conn closed 1
mysql Information: 10 : 1: Set Database: test
mysql Information: 3 : 1: Query Opened: SET SQL_SELECT_LIMIT=0
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SELECT * FROM bug34657
mysql Information: 4 : 1: Resultset Opened: field(s) = 4, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 3 : 1: Query Opened: SET SQL_SELECT_LIMIT=DEFAULT
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: UPDATE `bug34657` SET `val` = 99.9 WHERE ((`id` = 1) AND ((0 = 1 AND `expr` IS NULL) OR (`expr` = 2)) AND ((0 = 1 AND `txt` IS NULL) OR (`txt` = 'Tri')) AND ((0 = 1 AND `val` IS NULL) OR (`val` = 0.00)))
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
CP 3
Conn closed 2
CP 4
CP 5
Conn closed 3
mysql Information: 10 : 1: Set Database: test
mysql Information: 3 : 1: Query Opened: INSERT INTO `bug34657` (`id`, `expr`, `txt`, `val`) VALUES (4, NULL, 'sds', 113.2)
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
Conn closed 4
CP 6