Bug #16788 Only byte arrays and strings can be serialized by MySqlBinary
Submitted: 25 Jan 2006 18:03 Modified: 14 Mar 2006 17:23
Reporter: David Anderson Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.7 OS:Windows (XP)
Assigned to: Reggie Burnett CPU Architecture:Any

[25 Jan 2006 18:03] David Anderson
Description:
Stored procedure as follows:

CREATE PROCEDURE `insert_user_states`(
  IN param_user_id integer(9),
  IN param_state varchar(2))
BEGIN
  INSERT INTO user_states(user_id, state)
  VALUES (param_user_id, param_state);
END

C# code as follows:

private void SaveUserStates(string states, int user_id)
{
	StringBuilder sbErrors = new StringBuilder();
	MySqlCommand cmdInsert = MySqlCnn.CreateCommand();
	cmdInsert.CommandType = CommandType.StoredProcedure;
	cmdInsert.CommandText = "insert_user_states";
	cmdInsert.Parameters.Add("param_user_id", MySqlDbType.UInt16, 9);
	cmdInsert.Parameters["param_user_id"].Value = 44;
	cmdInsert.Parameters.Add("param_state", MySqlDbType.VarChar, 2);
	cmdInsert.Parameters["param_state"].Value = "ss";
	try
	{
		MySqlCnn.Open();
		cmdInsert.ExecuteNonQuery();
	}
	catch(MySqlException MySqlEx)
	{
	             sbErrors.Append("MySql error in SaveUserStates: " 
			+ MySqlEx.Message);			
		Debug.WriteLine(sbErrors.ToString());
	}
	catch (Exception ex)
	{
	             sbErrors.Append("Exception in SaveUserStates: " 
			+ ex.Message);
		Debug.WriteLine(sbErrors.ToString());
	}
	finally
	{
		MySqlCnn.Close();
	}
}

Executing this code results in this error: "MySql error in SaveUserStates: Only byte arrays and strings can be serialized by MySqlBinary"

How to repeat:
See description above. As of this writing I have not found a workaround. Other stored procedures that are similar are working fine.
[21 Feb 2006 8:14] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of the SHOW CREATE TABLE user_states statement. What version of MySQL server are you connecting to?
[22 Feb 2006 20:10] David Anderson
I am using MySql 5.0.18-nt. Here's the results you asked for:

'user_states', 'CREATE TABLE `user_states` (
  `user_id` int(11) NOT NULL default '0',
  `state` varchar(2) NOT NULL default '',
  `uid` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`uid`),
  KEY `FK_user_states_users` (`user_id`),
  CONSTRAINT `FK_user_states_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 11264 kB; (`user_id`) REFER `recruiting_listser''
[6 Mar 2006 17:43] Luciano Lemberg
I have been dealing with the same issue. Changing UInt16 or UInt32 to the corresponding Int in the parameter definition made it work.
[7 Mar 2006 22:23] 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:

This was fixed with the same set of changes that corrected bug # 17375
[7 Mar 2006 22:24] Reggie Burnett
Fixed in 1.0.8.  Fixed by providing proper support for unsigned data types
[7 Mar 2006 22:39] 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/3565
[14 Mar 2006 17:23] Mike Hillyer
Documented in 1.0.8 changelog

      <listitem>
        <para>
          Unsigned data types were not properly supported. (Bug #16788)
        </para>
      </listitem>