MySQL Bugs: #26521: SQL Dataadapter stops Fill when table contains null values

Bug #26521	SQL Dataadapter stops Fill when table contains null values
Submitted:	21 Feb 2007 12:27	Modified:	20 Mar 2007 9:24
Reporter:	Roelant Dieben	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.0.3, 5.0.5SVN	OS:	Windows (Windows XP Pro)
Assigned to:		CPU Architecture:	Any
Tags:	dataadapter, fill, hangs, null, SQL, values

Description:
When calling the SQLDataAdapter.Fill(SQLDataSet) the application just hangs. No exception is returned, nor is it trappable with a 'try'. Furthermore it will not time-out (waited for 15 minutes)...it just hangs.

The database connection is tested and I don't have any problems with tables that do not contain null values (i.e. the DataAdapter neatly fills the dataset)

I am using Visual Studio 2005 Pro, .NET framework 2.0.50727. 

How to repeat:
I am using the following code:

Dim SQLConn As New MySQLConnection(MyConnectionstring)
Dim SQLDataAdapter As New MySqlDataAdapter("SELECT * FROM MyTable", SQLConn)

Dim SQLDataSet As New Data.DataSet

SQLDataAdapter.Fill(SQLDataSet)

'opening and/or closing the connection prior or after does not have effect (and same code works for tables without null values)

I have been unable to reproduce this.  Here is the test case I am using.

[Test]
public void FillWithNulls()
{
  execSQL("DROP TABLE IF EXISTS test");
  execSQL("CREATE TABLE test (id INT)");
  execSQL("INSERT INTO test VALUES (NULL)");
  execSQL("INSERT INTO test VALUES (NULL)");
  execSQL("INSERT INTO test VALUES (3)");
  execSQL("INSERT INTO test VALUES (4)");

  MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
  DataSet ds = new DataSet();
  da.Fill(ds);
  Assert.AreEqual(4, ds.Tables[0].Rows.Count);
}

Hi Roelant and thanks for your report. 
I am using MySQL server 5.0.38BK on WinXP Pro SP2 localhost, net fw 2.0 and both c/NET 5.0.3GA and 5.0.5 SVN. Even though I can't actualy repeat dedlock I have found problematic behavior (test case modified from Bug#25603):

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=;PORT=3306;"; 
            conn.Open();

            MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS vtest", conn);
            cmdCreateTable.CommandTimeout = 0;
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = "CREATE TABLE vtest (id INT UNSIGNED NOT NULL AUTO_INCREMENT, " +
                "name VARCHAR(100), PRIMARY KEY(id))";
            cmdCreateTable.ExecuteNonQuery();

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM vtest", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

            DataTable dt = new DataTable();
            da.Fill(dt);
            dt.Columns[0].AutoIncrement = true;
            dt.Columns[0].AutoIncrementSeed = -1;
            dt.Columns[0].AutoIncrementStep = -1;
            DataRow row = dt.NewRow();
            row["name"] = "Test1";
            try
            {
                dt.Rows.Add(row);
                da.Update(dt);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            dt.Clear();
            da.Fill(dt);
            Assert.AreEqual(1, dt.Rows.Count);
            Assert.AreEqual(1, dt.Rows[0]["id"]);
            Assert.AreEqual("Test1", dt.Rows[0]["name"]);

            row = dt.NewRow();
            CheckConn(conn);
            row["name"] = System.DBNull.Value;
            try
            {
                dt.Rows.Add(row);
                da.Update(dt);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            dt.Clear();
            da.Fill(dt);
            Assert.AreEqual(2, dt.Rows.Count);
            Assert.AreEqual(2, dt.Rows[1]["id"]);

            row = dt.NewRow();
            CheckConn(conn);
            row["name"] = "Test3";
            try
            {
                dt.Rows.Add(row);
                da.Update(dt);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            dt.Clear();
            da.Fill(dt);
            Assert.AreEqual(3, dt.Rows.Count); //RETURNS 3 as in Reggies example
            Assert.AreEqual(3, dt.Rows[1]["id"]); //RETURNS 2 !!!
            Assert.AreEqual("Test2", dt.Rows[1]["name"]); //RETURNS NULL!!!

Should be 3:       dt.Rows[1]["id"]	2	object {int}
Should be "Test2": dt.Rows[1]["name"]	{}	object {System.DBNull}

Changing "hangs on" to "stops" even though it's not 100% correct.

Tonci

Your testcase is checking the second row while you are looking for the values from the third row.  I still can't reproduce this.

Reggie sorry, copy/paste... The problem can't be recreated with latest c/NET sources.
Roelant, if you can provide more info, please reopen the report.