Bug #13276 Exception on serialize after inserting null value
Submitted: 16 Sep 2005 18:15 Modified: 23 Sep 2005 15:38
Reporter: Tom Christian Paulsen Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:1.0.5 OS:Microsoft Windows (Windows XP)
Assigned to: Reggie Burnett CPU Architecture:Any

[16 Sep 2005 18:15] Tom Christian Paulsen
Description:
When inserting rows in a table, you use a parameter to set the value for an int field. If the value for the field in the first insert is NULL, and the value for the second insert is an integer, you will get an exception:

MySql.Data.MySqlClient.MySqlException: Only byte arrays and strings can be seria
lized by MySqlBinary
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
   at dbtest.Class1.Main(String[] args) in c:\prog\dbtest\class1.cs:line 40

How to repeat:
TABLE:
create table bug (
id int auto_increment primary key,
foo int
);

CODE:

static void Main(string[] args) {
MySqlConnection con = new MySqlConnection("Database=test;User Id=tom;Password=hfsjfhsk");

con.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter("select * from bug;", con);
DataSet ds = new DataSet();
adapter.Fill(ds, "Bug");
DataTable tbl = ds.Tables["Bug"];
			
DataRow row = tbl.NewRow();
tbl.Rows.Add(row);

row = tbl.NewRow();
row["foo"] = 2;
tbl.Rows.Add(row);

adapter.InsertCommand = createInsertCommand(con);

adapter.Update(ds, "Bug");

con.Close();
}

static MySqlCommand createInsertCommand(MySqlConnection con)
{
	string sql = "insert into bug (foo) " +
		"values (?foo);";
			
	MySqlCommand c = new MySqlCommand(sql, con);
		
	MySqlParameterCollection pc = c.Parameters;
	pc.Add("foo", MySqlDbType.Int32, 0, "foo");
		
	return c;
}

Suggested fix:
in end system:

use MySqlParameter.MySqlDbType property, instead of the constructor MySqlParameter(paramterName, dbType).

	MySqlParameterCollection pc = c.Parameters;
	MySqlParameter p = pc.Add("foo", MySqlDbType.Int32, 0, "foo");
	p.MySqlDbType  = MySqlDbType.Int32

in MySQL Connector/Net:

in the constructor, MySqlParameter(paramterName, dbType), add the following line:

inferType = false;
[18 Sep 2005 8:52] Vasily Kishkin
Sorry...I was not able to reproduce the bug.
I've got the follow result:
mysql> select * from bug;
+----+------+
| id | foo  |
+----+------+
|  1 | NULL |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)

Could you please say me what you use a version of mysqld ?
[18 Sep 2005 13:43] Tom Christian Paulsen
I use version 5.0.11-beta.

but i think the problem is in the connector...
[20 Sep 2005 9:31] Vasily Kishkin
Sorry...I used wrong version of connector. I was able to reproduce the bug on 1.0.5. Thanks for the bug report.
[20 Sep 2005 15:04] Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

fixed in 1.0.6
[23 Sep 2005 15:38] Mike Hillyer
Documented in 1.0.6 changelog:

<listitem>
        <para>
          Serializing a parameter fails if first value passed in
          <literal>NULL</literal>. (Bug #13276)
        </para>
      </listitem>