MySQL Bugs: #13276: Exception on serialize after inserting null value

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:	Windows (Windows XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

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;

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 ?

I use version 5.0.11-beta.

but i think the problem is in the connector...

Sorry...I used wrong version of connector. I was able to reproduce the bug on 1.0.5. Thanks for the bug report.

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

Documented in 1.0.6 changelog:

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