Bug #49102 Exception retrieving guids ending in 0x20 from 4.1 databases
Submitted: 25 Nov 2009 15:15 Modified: 3 Dec 2009 11:37
Reporter: Olivier Poquet Email Updates:
Status: Won't fix Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:5.2.7 OS:Any
Assigned to: CPU Architecture:Any

[25 Nov 2009 15:15] Olivier Poquet
Mysql 4.1 strips trailing space characters (0x20) from binary columns on retrieval.  As a result of this behavior, guids which end in 0x20 will appear to be shorter then 16 bytes when retrieved from the database.  The Connector is not smart about this, and when trying to read a guid from the database that ended in 0x20, the connector throws an exception:

System.ArgumentException: Byte array for GUID must be exactly 16 bytes long.
at System.Guid..ctor(Byte[] b)
at MySql.Data.Types.MySqlBinary.MySql.Data.Types.IMySqlValue.get_Value()
at MySql.Data.MySqlClient.MySqlDataReader.GetValue(Int32 i)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()

I'm working around this by calling GetBytes directly and checking the length to see if it needs to be padded.  

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

C#, the ExecuteScalar will fail:
        private const string connectionSpecifier = "Persist Security

        public void GuidEndingIn20Test()
            Guid guid = new Guid("B74C4C85-D103-4266-8BA7-AE3657C1D120");

            MySqlConnection connection = new MySqlConnection(connectionSpecifier);
                MySqlCommand command = new MySqlCommand("INSERT INTO test VALUES (?guid)", connection);
                command.Parameters.AddWithValue("?guid", guid.ToByteArray());

                command = new MySqlCommand("SELECT guid FROM test", connection);
                Guid guid2 = (Guid)command.ExecuteScalar();

Suggested fix:
If we're reading a binary(16) column in a 4.1 database as a guid, the connector should pad the guid with 0x20 up to 16 bytes.
[26 Nov 2009 13:31] Tonci Grgin
Olivier, Wlad confirmed your test works against MySQL server 6.0 so it really might be server issue.

What happens if you append something after 0x20? Does then this new character gets written to database (ie. instead of 0x20 put 0x20 0x10 or whatever)?
[26 Nov 2009 13:46] Tonci Grgin
One more note... *Both* MySQL server 4.1 and c/NET 5.2 (almost) are EOL-ed so it might be a good thing to upgrade.
[30 Nov 2009 17:20] Olivier Poquet
The behavior of stripping trailing space characters from binary columns is a server issue, see http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html and see the bulleted list midway down.  

Unfortunately upgrading the server isn't an option for me at the moment.  I'm quite content if you guys decide not to fix this in the connector given that it only affects ancient mysql server versions and I can work around it.  It would, however, be nice if this issue were documented given that the connector is still supposed to support mysql versions before 5.0.15, old as they are.
[3 Dec 2009 11:37] Tonci Grgin
Olivier, as there seem not to be any valid reason for complicating connector code cause of bugs in EOL-ed server, I'm closing this one.