Bug #24565 Inferring DbType fails when reusing commands and the first time the value is nul
Submitted: 24 Nov 2006 3:20 Modified: 4 Dec 2006 13:22
Reporter: John Baro Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.7/1.0.8 OS:Microsoft Windows (Windows)
Assigned to:
Tags: command, MySqlBinary, Parameter

[24 Nov 2006 3:20] John Baro
Description:
When a parameter is created without explicitly setting the dbtype it sets it dynamically when the value gets set. (parameter.cs:432\SetTypeFromValue())

If the command gets executed with a value of DbNull.Value then the valueObject is incorrectly set as MySqlBinary (default) which will only work for Byte, String and Char[].

The next time the command gets executed and a value has been set that is not DbNull.Value, the valueObject tries to serialize the value but fails as it is not one of the aforementioned types and throws an exception "Only Byte Arrays can be serialized by MySqlBinary".

How to repeat:
Create a table with a nullable datetime column.
Create a command to insert a value into this table.
Create a parameter, do not specify a type.
Set DbNull.Value as the value for the parameter.
Execute the command.
Set DateTime.Now as the value for the parameter.
Execute the command.
You should receive an exception.

Suggested fix:
In testing I set valueObject to null in parameter.cs:376\SetDbType() if the target did not match the source.
This forced valueObject to be regenerated when the parameter was serialized next time.
This worked but there might well be a better way.
[24 Nov 2006 14:05] Tonci Grgin
Hi John and thanks for your problem report.
Please provide following:
 - MySQL server version and host OS
 - NET fw version
 - Small but complete test case showing this error every time
[26 Nov 2006 21:53] John Baro
Server version is 5.0.24a-Debian_9-log (results of SELECT @@VERSION)
Host OS is Ubuntu 6.10
Framework is 2.0.50727

Example project:
/*
CREATE TABLE BlackHole(
  UniverseEnding BIT NULL
);
*/
using System;
using System.Collections.Generic;
using System.Text;

using MySql.Data.MySqlClient;

namespace MySqlBugDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (MySqlConnection connection = new MySqlConnection("server=<server>; user id=<user>; password=<password>; database=<db>"))
            {
                connection.Open();
                using (MySqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "INSERT INTO BlackHole(UniverseEnding) VALUES(?UniverseEnding)";
                    MySqlParameter param = new MySqlParameter("?UniverseEnding", DBNull.Value);
                    command.Parameters.Add(param);
                    command.ExecuteNonQuery();

                    param.Value = true;
                    command.ExecuteNonQuery(); //fails
                    Console.WriteLine("Should have failed by now, otherwise the universe has ended");
                    Console.Read();
                }
            }
        }
    }
}
[27 Nov 2006 11:31] Tonci Grgin
Hi John. Verified as described by reporter with test case provided on latest connector/NET sources.

>If the command gets executed with a value of DbNull.Value then the valueObject
is incorrectly set as MySqlBinary (default) which will only work for Byte, String and Char[].
If I may add, MySQLDBType is set to BLOB which leads to Binary flag set as it is default value.
[29 Nov 2006 17:45] 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/16132
[29 Nov 2006 17:45] 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/16133
[29 Nov 2006 17:46] Reggie Burnett
Fixed in 1.0.9 and 5.0.3
[4 Dec 2006 13:22] MC Brown
A note has been added to the 1.0.9 and 5.0.3 changelogs.