Bug #46205 MySQL TINYINT(1) boolean conversion fails
Submitted: 15 Jul 2009 13:52 Modified: 10 Aug 2009 16:13
Reporter: Attila Farago Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.0.4, 6.1.1 OS:Microsoft Windows (Vista 32)
Assigned to: Vladislav Vaintroub CPU Architecture:Any
Tags: TINYINT boolean
Triage: D2 (Serious)

[15 Jul 2009 13:52] Attila Farago
Description:
After long hours of debugging it seems to me that MySQL TINYINT(1) boolean conversion fails in 6.0.4 version.

Ths source of the problem is that MySQL default charset is UTF8.
default-character-set=utf8
(I suspect that connection charset would yield the same result)

CharacterSetIndex is returned as 63, however it is overwritten to connection.driver.ConnectionCharSetIndex that was 33.

NativeDriver sets field maxlength to 3.
Therefore MaxLength does not equal to 1, that is the criteria for auto-bool conversion in Field.GetValueObject.

ps: explicit connectionstring setting does not help

---
NativeDriver.cs
field.MaxLength = cs.byteCount;

Field.cs
sets 
            if (CharacterSetIndex == 63)
                CharacterSetIndex = connection.driver.ConnectionCharSetIndex;
in
     public void SetTypeAndFlags(MySqlDbType type, ColumnFlags flags)

therefore
binary fields (such as a TINYINT(1))
will be defaulted to UTF8 (connection default) and will yield as MaxLength==3 in this code

        public IMySqlValue GetValueObject()
        {
            IMySqlValue v = GetIMySqlValue(Type);
            if (v is MySqlByte && ColumnLength == 1 && MaxLength == 1 && connection.Settings.TreatTinyAsBoolean)
            {
                MySqlByte b = (MySqlByte)v;
                b.TreatAsBoolean = true;
                v = b;
            }

result: TINYINT(1) fields will be mapped to SByte instead of boolean

introduced in 5.2.4

How to repeat:
- create an utf8 database
- create a table with a TINYINT(1) field
try to read it

Suggested fix:
? do not see the internals of the connector, for first sight I would change the Field.cs implementation to

        public IMySqlValue GetValueObject()
        {
            IMySqlValue v = GetIMySqlValue(Type);
            if (v is MySqlByte && ColumnLength == 1 && 
                /*MaxLength == 1 &&*/ connection.Settings.TreatTinyAsBoolean)
            {
[15 Jul 2009 15:00] Tonci Grgin
Hi Attila and thanks for interesting report. Looking into this.
[17 Jul 2009 8:22] Tonci Grgin
Verified as described by looking into latest sources.

To my opinion, IMySqlValue GetValueObject should also respect same logic as described in 5.2.7 change-log entry:
- fixed problem where the connector would incorrectly report the length of utf8 columns on servers 6.0 and later.  This was caused by 6.0 now using 4 bytes for utf8 columns.
[17 Jul 2009 8:36] Tonci Grgin
Test case:
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=**;Database=**;UserID=**;Password=**;PORT=**;Treat Tiny As Boolean = true;logging=True;charset=utf8";
            conn.Open();

            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 50;
            cmd.CommandText = "DROP TABLE IF EXISTS bug46205;";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "CREATE TABLE bug46205 (Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, BCol TINYINT(1));";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "INSERT INTO bug46205 VALUES (NULL, 0);";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "INSERT INTO bug46205 VALUES (NULL, 1);";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "SELECT * FROM bug46205;";
            try
            {
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine(dr.GetDataTypeName(1));
                    Console.WriteLine(dr.GetFieldType(1));
                    Console.WriteLine(dr.GetValue(1).GetType().FullName);
                    Console.WriteLine(dr.GetString(1));
                }
                dr.Close();
                cmd.CommandText = "DROP TABLE IF EXISTS bug46205;";
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + "  " + "Exception: " + ex.Message);
                throw;
            }
yields following output:
TINYINT
System.SByte
System.SByte
0
TINYINT
System.SByte
System.SByte
1

"respect binary flags" makes no difference.
[20 Jul 2009 14:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/79014
[24 Jul 2009 6:25] Tonci Grgin
Bug#46359 was marked as duplicate of this report.
[24 Jul 2009 6:26] Tonci Grgin
Bug#41953 is a duplicate of this report.
[10 Aug 2009 16:13] Tony Bedford
An entry was added to the 5.2.8, 6.0.5, 6.1.1 changelogs:

Conversion of MySQL TINYINT(1) to boolean failed.