MySQL Bugs: #24565: Inferring DbType fails when reusing commands and the first time the value is nul

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:	Windows (Windows)
Assigned to:		CPU Architecture:	Any
Tags:	command, MySqlBinary, Parameter

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.

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

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();
                }
            }
        }
    }
}

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.

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

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

Fixed in 1.0.9 and 5.0.3

A note has been added to the 1.0.9 and 5.0.3 changelogs.