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: | |
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.
[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