MySQL Bugs: #64633: System.InvalidCastException when executing a stored function

Bug #64633	System.InvalidCastException when executing a stored function
Submitted:	13 Mar 2012 11:52	Modified:	11 Mar 2013 17:49
Reporter:	James Toyer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.4.3	OS:	Windows
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any
Tags:	stored function

Description:
I've just updated a project from version 6.3.6 to 6.4.3 and I've started to get the following exception when trying to execute a stored proceedure:

System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'   
at MySql.Data.MySqlClient.StoredProcedure.GetAndFixParameter(String spName, DataRow param, Boolean realAsFloat, MySqlParameter returnParameter)
   at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
   at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at Adfero.Photos.Photos.CreatePhoto(String username, Photo photo, Stream fileData, Boolean skipSolr) in C:\Users\james.toyer\Documents\Development\PhotoLibrary\PhotoLibrary\Photos.cs:line 129

The stored function worked fine until the upgrade. I've included the C# and SQL code here

C#:
int createdId = photo.ID;
string access = photo.Access.ToString();

// Write to database.
using (MySqlConnection conn = GetMySqlConnection())
{
    MySqlCommand cmd = new MySqlCommand("create_photo", conn) { CommandType = CommandType.StoredProcedure };
    cmd.Parameters.AddWithValue("in_photo_id", updatedPhoto.ID);
    cmd.Parameters.AddWithValue("in_access", updatedPhoto.Access.ToString());
    cmd.Parameters.AddWithValue("in_collection", updatedPhoto.Collection);
    cmd.Parameters.AddWithValue("in_title", updatedPhoto.Title);
    cmd.Parameters.AddWithValue("in_tags", updatedPhoto.Tags);
    cmd.Parameters.AddWithValue("in_description", updatedPhoto.Description);
    cmd.Parameters.AddWithValue("in_usage_instructions", updatedPhoto.UsageInstructions);
    cmd.Parameters.AddWithValue("in_width", updatedPhoto.Width);
    cmd.Parameters.AddWithValue("in_height", updatedPhoto.Height);
    cmd.Parameters.AddWithValue("in_source_type", updatedPhoto.SourceType);
    cmd.Parameters.AddWithValue("in_source", updatedPhoto.Source);
    cmd.Parameters.AddWithValue("in_source_url", updatedPhoto.SourceUrl);
    cmd.Parameters.AddWithValue("in_copyright", updatedPhoto.Copyright);
    cmd.Parameters.AddWithValue("in_focal_point_x", updatedPhoto.FocalPointX);
    cmd.Parameters.AddWithValue("in_focal_point_y", updatedPhoto.FocalPointY);
    cmd.Parameters.AddWithValue("in_codec_extensions", updatedPhoto.CodecExtensions);
    cmd.Parameters.AddWithValue("in_codec_name", updatedPhoto.CodecName);
    cmd.Parameters.AddWithValue("in_codec_mime_types", updatedPhoto.CodecMimeTypes);
    cmd.Parameters.AddWithValue("in_created", updatedPhoto.Created);
    cmd.Parameters.AddWithValue("in_modifier_username", username);
    MySqlParameter newIdParam = cmd.Parameters.Add(new MySqlParameter("@new_id", MySqlDbType.UInt32) { Direction = System.Data.ParameterDirection.ReturnValue });

    MySqlCommand selectCmd = new MySqlCommand("get_updated_photo", conn) { CommandType = CommandType.StoredProcedure };
    selectCmd.Parameters.AddWithValue("in_photo_id", photo.ID);

    conn.Open();

    using (var trans = conn.BeginTransaction())
    {
        cmd.Transaction = trans;
        selectCmd.Transaction = trans;

        cmd.ExecuteNonQuery();

        if (updatedPhoto.ID == 0)
        {
            if (newIdParam.Value != DBNull.Value)
                createdId = Convert.ToInt32(newIdParam.Value);
            updatedPhoto.ID = createdId;
        }
        selectCmd.Parameters["in_photo_id"].Value = updatedPhoto.ID;

        using (MySqlDataReader r = selectCmd.ExecuteReader())
        {
            if (!r.HasRows || !r.Read())
            {
                LogManager.GetCurrentClassLogger().Warn("Failed getting created dates/user details from database after creating db meta data.");
            }

            updatedPhoto.Created = r.GetDateTime("created");
            updatedPhoto.Modified = r.GetDateTime("modified");
            updatedPhoto.CreatedUsername = r.GetString("created_username");
            updatedPhoto.ModifiedUsername = r.GetString("modified_username");
        }

        trans.Commit();
    }
}

SQL Function:
DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `create_photo`(
  `in_photo_id` INT,
  `in_access` ENUM('public','private'),
  `in_collection` VARCHAR(45),
  `in_title` VARCHAR(255),
  `in_tags` TEXT,
  `in_description` TEXT,
  `in_usage_instructions` TEXT,
  `in_width` SMALLINT UNSIGNED,
  `in_height` SMALLINT UNSIGNED,
  `in_source_type` VARCHAR(45),
  `in_source` TEXT,
  `in_source_url` VARCHAR(1023),
  `in_copyright` TEXT,
  `in_focal_point_x` SMALLINT UNSIGNED,
  `in_focal_point_y` SMALLINT UNSIGNED,
  `in_codec_extensions` VARCHAR(45),
  `in_codec_name` VARCHAR(45),
  `in_codec_mime_types` VARCHAR(255),
  `in_created` DATETIME,
  `in_modifier_username` VARCHAR(255)) RETURNS int(11)
    MODIFIES SQL DATA
BEGIN

INSERT INTO `photo` (`photo_id`,`access`,`collection`,`title`,`tags`,`description`,`usage_instructions`,`width`,`height`,`source_type`,`source`,`source_url`,
  `copyright`,`focal_point_x`,`focal_point_y`,`codec_extensions`,`codec_name`,`codec_mime_types`,`created`,`created_user_id`,`modified_user_id`)
  VALUES
  (`in_photo_id`,`in_access`,`in_collection`,`in_title`,`in_tags`,`in_description`,`in_usage_instructions`,`in_width`,`in_height`,`in_source_type`,
  `in_source`,`in_source_url`,`in_copyright`,`in_focal_point_x`,`in_focal_point_y`,`in_codec_extensions`,`in_codec_name`,`in_codec_mime_types`, `in_created`,
  (SELECT `user_id` FROM `user` WHERE `user`.`username` = `in_modifier_username`), 
  (SELECT `user_id` FROM `user` WHERE `user`.`username` = `in_modifier_username`));

RETURN LAST_INSERT_ID();

END

Permissions for executing user:
GRANT EXECUTE ON FUNCTION `photo_library`.`create_photo` TO 'photos_user'@'%';

How to repeat:
The code in the description should provide enough to recreate.

After some more looking around, it turns out this is not a "bug". 

The way to stop this error from occurring is to add "CheckParameters=false" to the connection string. I only found this out when a stored procedure running a select failed but gave a more helpful error of:

"Unable to retrieve stored procedure metadata for routine 'get_updated_photo'.  Either grant  SELECT privilege to mysql.proc for this user or use "check parameters=false" with  your connection string."

It would be helpful when using stored functions a similar error could be thrown to help troubleshoot

So we have a valid feature request here it seems...

Added to changelog for 6.4.6, 6.5.6, 6.6.6: 

A System.InvalidCastException exception could occur when calling a
stored function. A workaround was to specify the
CheckParameters=false connection option.

setting "check parameters=false" is really a bad idea. When you set this, then the passed variables are not checked with the SP decleration and chaos begins.

I just installed the newest MySQL Server on my new PC and I keep getting errors that conversions are not valid. I am passing 120 parameters form c# to a mysql sp, so the error "invalid conversion" is really not helpful.
Is there any way to find which conversion should suddenly after upgrade no more work?