Bug #35302 ExecuteScalar returns VarBinary when it's a string

• Submitted: 14 Mar 2008 23:18
• Modified: 15 Mar 2008 0:56
• Reporter: Gary Smith
• Status: Not a Bug
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 5.2.1
• OS: Windows
• CPU Architecture: Any

[14 Mar 2008 23:18] Gary Smith

Description:
Running the code below, I expect to get a VarChar string (which worked under 5.0.x) but the result coming back from reader (command.cs line 469) is type VarBinary.

Relevant connection string information
Server=Host;Database=DB;UserID=User;Password=Pass; Allow Zero Datetime=False; Convert Zero Datetime=True; Pooling=false;

This prevents a clean upgrade from 5.0.x to 5.2.x.  If this can be resolved via SQL, then we can easily change the SQL statements, but it the output type doesn't match it in this case, what others will it fail on.

Or did I miss something else more fundamental in the upgrade notes (didn't see anything at first read).

How to repeat:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace DBTest
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection conn = null;
            MySqlCommand command = null;
            Object scalar = null;

            try
            {
                conn = new MySqlConnection(ConfigurationManager.AppSettings["MySqlReaderConnectionPool"]);
                conn.Open();
                command = new MySqlCommand();
                command.Connection = conn;
                command.CommandText = "SELECT MD5(@value) AS md5value";
                command.CommandType = CommandType.Text;
                command.Prepare();
                command.Parameters.AddWithValue("@value", "beforemd5");
                scalar = command.ExecuteScalar();
                command.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

        }
    }
}

[15 Mar 2008 0:17] Gary Smith

Using:

command.CommandText = "SELECT CONVERT(MD5(@value)  USING utf8) AS md5value";

Seems to make the problem go away.  It seesm that the MD5 function may be partly to blame.  It returns a binary string which on the older connector came back as a VarChar but it appears to be returning the binary type now.  

Not sure if this should be more clearly documented in the MD5 defination (that it's returned as non-string) or not.

[15 Mar 2008 0:56] Reggie Burnett

This is not a bug.  The server is returning the output of MD5 as a binary string and the connector is honoring that.  You can either use the SQL workaround you gave or give a connection string option 'respect binary flags=false' which will reproduce 5.0 behavior.  This is not recommended but for large apps that really can't be reworked, it's a good option.