MySQL Bugs: #49080: inserting a guid into a Binary(16) column doesn't insert the guid correctly

Bug #49080	inserting a guid into a Binary(16) column doesn't insert the guid correctly
Submitted:	24 Nov 2009 23:30	Modified:	1 Dec 2009 7:09
Reporter:	Olivier Poquet	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.7	OS:	Windows (4.1.22 DB)
Assigned to:		CPU Architecture:	Any

Description:
If you create a command to insert a guid into a table with a Binary(16) column to hold it and call Parameters.AddWithValue("?param", guid) where guid is a System.Guid, the value actually inserted into the database does not match the binary value of the guid.  

If you use the connector to retrieve the inserted value and compare it with the original, they won't match.  

I'm using a 4.1 database.  

How to repeat:
SQL:
create table test (guid binary(16)) ENGINE=Innodb;

C#, the Assert will fail:
        private const string connectionSpecifier = "Persist Security Info=true;DataSource=localhost;Database=database;charset=latin1;User ID=root;Password=passwd";

        [Test]
        public void GuidInsertTest()
        {
            Guid guid = new Guid("B74C4C85-D103-4266-8BA7-AE3657C1D1E2");

            MySqlConnection connection = new MySqlConnection(connectionSpecifier);
            connection.Open();
            try
            {
                MySqlCommand command = new MySqlCommand("INSERT INTO test VALUES (?guid)", connection);
                command.Parameters.AddWithValue("?guid", guid); //guid.ToByteArray() works fine here
                command.ExecuteNonQuery();

                command = new MySqlCommand("SELECT guid FROM test", connection);
                Guid guid2 = (Guid)command.ExecuteScalar();
                Assert.AreEqual(guid, guid2);
            }
            finally
            {
                connection.Close();
            }
        }

Suggested fix:
One should be able to pass a Guid as a parameter without calling ToByteArray, or if you'd really like the user to call ToByteArray the insert should throw an exception instead of writing bad data.

Hi Olivier and thanks for another report.

I do not believe this is a bug. Please see Wlad's explanation in Bug#48063 for details.

Hi Tonci, 

I read over bug 48063 and I don't think that I'm describing the same issue.  I understand that a guid is, as Vladislav Vaintroub put it, "a thing that is dependend on endianess."  However, in this case here, i'm not doing anything with the guid bytes or making any assumptions about how the bytes are stored in the database.  

I'm passing a guid structure directly as a parameter, and then reading that same value out of the connector as a guid and I'm getting a different guid then the original.  I don't care how the bytes are stored in the database, but I would expect the connector to be consistent in the way it translates to and from whatever binary representation of a guid it chooses to store in the db.  

Thanks,
Olivier

Hello Olivier,
I can't reproduce the behavior with the current 5.2. Test below throws "data too long" exception, which should be fine in your case (you suggested throwing exception). Note that the same tests (changed to use CHAR(36) as guid datatype, or using "old guids=true") passes in 6.2 That is, even the exception must have been fixed somewhere in between 5.2 and 6.2.

       [Test]
        public void OldGuidType()
        {
            execSQL("DROP TABLE IF EXISTS Test");
            execSQL("CREATE TABLE Test (guid BINARY(16))");

            Guid g = Guid.NewGuid();
            MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(?g)", conn);
            cmd.Parameters.AddWithValue("?g", g);
            cmd.ExecuteNonQuery();

            cmd.CommandText = "SELECT guid FROM Test";
            Guid readG = (Guid)cmd.ExecuteScalar();
            Assert.AreEqual(g, readG);
        }

If this is fixed in the latest connector version then that's fine, go ahead and close this.  I don't have time to test the latest version of the connector right now, and calling ToByteArray() on the guid and passing that as the parameter is a reasonable work around for me.  

Thanks!

Olivier, I'll set it to "Won't fix".

I thought I was experiencing this bug, but then determined that I needed to set the OldGuids option in the connection string.

Just informing anyone else who might spend a day trying to figure out why they are experiencing a "bug" that no longer exists.