Bug #48083 dotNet connector guid as binary selection error

• Submitted: 15 Oct 2009 14:26
• Modified: 16 Oct 2009 5:55
• Reporter: Jacob Cagley
• Status: Duplicate
• Category: Connector / NET
• Severity: S1 (Critical)
• Version: 6.1.2
• OS: Windows
• CPU Architecture: Any

[15 Oct 2009 14:26] Jacob Cagley

Description:
The issue is when performing a select with a guid in binary mode ( OldGuids == true ) and the first row has a null value for the guid, the MySqlGuid class stays in string mode ( OldGuids == false ) when trying to convert any future guids from that field

How to repeat:
using System;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;

namespace JDMX.UnitTest {
  public class mysqltest {
    [STAThread]
    public static void Main( string[] args ) {
      string connectionString = "server=localhost;port=3306;uid=test;pwd=test;database=mysqltest;old guids=true;";

      // The Guid is this:
      // 01020304-0506-0708-0900-0a0b0c0d0e0f
      Guid guid = new Guid(
        "01020304" +
        "05060708" +
        "09000A0B" +
        "0C0D0E0F" );

      Console.WriteLine( "The guid defined in the src" );
      Console.WriteLine( guid.ToString( "N" ) + " - " + guid.ToString() );
      Console.WriteLine( "" );

      DbConnection connection = new MySqlConnection( connectionString );
      if ( connection.State == ConnectionState.Closed )
        connection.Open();

      DbCommand command = connection.CreateCommand();

      command.CommandText = "USE mysql";
      command.ExecuteNonQuery();

      command.CommandText = "DROP DATABASE mysqltest";
      command.ExecuteNonQuery();

      command.CommandText = "CREATE DATABASE mysqltest DEFAULT CHARACTER SET utf8";
      command.ExecuteNonQuery();

      command.CommandText = "USE mysqltest";
      command.ExecuteNonQuery();

      command.CommandText = "CREATE TABLE `guid_table` ( `guidint` int NOT NULL, `guid1` binary(16) NOT NULL, `guid2` binary(16) NOT NULL, `guid3` binary(16) NULL )";
      command.ExecuteNonQuery();

      command.CommandText = string.Format( "INSERT INTO guid_table ( guidint, guid1, guid2 )"
                                        + " VALUES ( 1, CAST(0x{0} AS BINARY), CAST(0x{0} AS BINARY) );", guid.ToString( "N" ) );
      command.ExecuteNonQuery();

      command.CommandText = string.Format( "INSERT INTO guid_table ( guidint, guid1, guid2 )"
                                        + " VALUES ( 2, CAST(0x{0} AS BINARY), CAST(0x{0} AS BINARY) );", guid.ToString( "N" ) );
      command.ExecuteNonQuery();

      // test 1 where guid3 is null for all the records
      command.CommandText = "SELECT guidint, guid1, guid2, guid3 FROM guid_table ORDER BY guidint";
      WriteDataReader( command );

      // add a record where guid3 is not null
      command.CommandText = string.Format( "INSERT INTO guid_table ( guidint, guid1, guid2, guid3 )"
                                        + " VALUES ( 3, CAST(0x{0} AS BINARY), CAST(0x{0} AS BINARY), CAST(0x{0} AS BINARY) );", guid.ToString( "N" ) );
      command.ExecuteNonQuery();

      // select the records where the first item has all the guid fields populated
      command.CommandText = "SELECT guidint, guid1, guid2, guid3 FROM guid_table ORDER BY guidint DESC";
      WriteDataReader( command );

      // select the records where the first item has a null guid3 but a later row has a value for guid3
      command.CommandText = "SELECT guidint, guid1, guid2, guid3 FROM guid_table ORDER BY guidint";
      WriteDataReader( command );

      connection.Close();

      Console.WriteLine( "Press enter to continue." );
      Console.ReadLine();
    }

    public static void WriteDataReader( DbCommand command ) {
      DbDataReader dr = command.ExecuteReader();
      int j = 0;

      while ( dr.Read() ) {
        object[] obj = new object[dr.FieldCount];
        dr.GetValues( obj );
        for ( int i = 0; i < obj.Length; i++ )
          Console.WriteLine( "r{5} c{4} -- {0} {1} - v {2} {3}", dr.GetName( i ), dr.GetFieldType( i ), obj[i], obj[i].GetType(), i, j );
        j++;
      }
      dr.Close();
    }

  }
}

Suggested fix:
The error is a very simple fix but horribly hard to track because of threading.  Anyway, in your src, the ReadValue function does not set OldGuids value for a null field.  Somewhere later in the src ( do not know where ) the field definition gets switched from OldGuids == true to OldGuids == false causing the error.

Adding g.OldGuids = OldGuids; after the MySqlGuid g = new MySqlGuid(); fixes the top level problem by keeping the status for even a null field but the results of a ReadValue should not be changing the Field definition

      MySqlGuid g = new MySqlGuid();
      g.OldGuids = OldGuids;
      g.isNull = true;

After verifying the error, this is the solution to fix it
In MySql.Data -> Source -> Types -> MySqlGuid

replace the ReadValue function with the following

    IMySqlValue IMySqlValue.ReadValue( MySqlPacket packet, long length, bool nullVal ) {
      MySqlGuid g = new MySqlGuid();
      g.OldGuids = OldGuids;
      g.isNull = true;
      if ( !nullVal ) {
        if ( OldGuids )
          return ReadOldGuid( packet, length );

        string s = String.Empty;
        if ( length == -1 )
          s = packet.ReadLenString();
        else
          s = packet.ReadString( length );
        g.mValue = new Guid( s );
        g.OldGuids = OldGuids;
        g.isNull = false;
      }
      return g;
    }

[15 Oct 2009 14:50] Jacob Cagley

I think I have tracked down the root cause of the problem.

In MySql.Data -> Source -> ResultSet the LoadMetaData() function creates the initial values[] based on the field definition.

In ReadColumnData, the line 
  values[i] = driver.ReadColumnValue(i, fields[i], values[i]);

passes in values[i] and returns it into the array, essentially wiping out the previously defined value for the new one.  This works just fined until one of the values has mistakenly changed the settings from the field setting ( like in my example )

Perhaps it would be better to have 2 arrays, one for the field settings that remains fixed for the entire recordset read and a second for the actual data being pulled.  So in case there is an error in the creation of the dataset value for one item, it will not carry forward to the next record.

[16 Oct 2009 5:55] Tonci Grgin

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Explanation: Hi Jacob and thanks for your report. I believe this is a duplicate of Bug#47928 and closing as such.

[16 Oct 2009 6:58] Tonci Grgin

Jacob, btw, great report. I wish I get more reports like yours.