Bug #41953 BOOLEAN not recognized any more - REGRESSION
Submitted: 8 Jan 2009 9:42 Modified: 24 Jul 2009 6:26
Reporter: Vincent Courcelle Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:5.2.5 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: boolean, datasets, regression

[8 Jan 2009 9:42] Vincent Courcelle
Description:
Hello,

I was using connector 5.2.3 and BOOLEAN types were correctly recognized when generating datasets (so myDataSet.MyBooleanField was a bool). It's no longer the case with 5.2.5 (recognized as small integers which they are but this cause the old code not to be compatible with the "new" datasets (when we change our database scheme, we need to write again new datasets...)).

I'm using MySQL server 6.0.8-alpha on my dev machine (only because schema regeneration is much (much, much, much) faster than 5.1, the prod machine will be under 5.1 as we don't requires schema regeneration on it).

How to repeat:
Suppose MyTable have a "BOOLEAN" declared field.

MySqlCommand command=new MySqlCommand("SELECT * FROM MyTable LIMIT 1",connection);
DataSet dataSet=new DataSet();
SQLHelper.ExecMySQLQuery(command,dataSet);
dataSet.Tables[0].TableName="MyTable";
dataSet.DataSetName="MyTableDS";
dataSet.WriteXmlSchema("MyTable.xsd");
[12 Jan 2009 14:31] Tonci Grgin
Hi Vincent and thanks for your report.

Assuming things is a problem of many bug reports, that's why I require *complete* test case before proceeding. Here, I'm missing connection string, specifically value for 'Treat Tiny As Boolean' option. Did you test against MySQL server 5.1GA (6.0 is beta)?

So, please attach *full* test case demonstrating unwanted behavior.
[12 Jan 2009 16:02] Vincent Courcelle
Hello,

Here is a table structure, which leads to the bug I described:

CREATE TABLE IF NOT EXISTS `MyTable` (
  `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `CheckedAuthenticity` BOOLEAN NOT NULL DEFAULT 0
  PRIMARY KEY (`Id`) )
ENGINE = InnoDB;

which leads to this schema under 5.2.3:

<?xml version="1.0" standalone="yes"?>
<xs:schema id="MyTableDS" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="MyTableDS" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="DocumentsTypes">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Id" type="xs:unsignedInt" minOccurs="0" />
              <xs:element name="CheckedAuthenticity" type="xs:boolean" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

And this one under 5.2.5:
<?xml version="1.0" standalone="yes"?>
<xs:schema id="MyTableDS" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="MyTableDS" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="DocumentsTypes">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Id" type="xs:unsignedInt" minOccurs="0" />
              <xs:element name="CheckedAuthenticity" type="xs:byte" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

The following piece of code should generate the same on your computer:

public static void WriteDataSet()
{
	MySqlConnection connection=new MySqlConnection("Database=mydatabase;Data Source=localhost;User Id=test;Password=test");
	MySqlCommand command=new MySqlCommand("SELECT * FROM MyTable LIMIT 1",connection);
	DataSet dataSet=new DataSet();
	ExecMySQLQuery(command,dataSet);
	dataSet.Tables[0].TableName="MyTable";
	dataSet.DataSetName="MyTableDS";
	dataSet.WriteXmlSchema("MyTable.xsd");
}

public static int ExecMySQLQuery(MySqlCommand command,DataSet dataSet)
{
	MySqlDataAdapter dataAdapter=new MySqlDataAdapter();
	string genericTableName=null;
	string typedTableName=null;

	for(int i=0;i<dataSet.Tables.Count;i++)
	{
		genericTableName="Table";
		typedTableName=dataSet.Tables[i].TableName;
		if(i!=0)
		{ genericTableName+=i; }
		dataAdapter.TableMappings.Add(genericTableName,typedTableName);
	}

	dataAdapter.SelectCommand=command;
	return dataAdapter.Fill(dataSet);
}

Hope this will help, I can't really provide more!
[12 Jan 2009 16:04] Tonci Grgin
Thanks Vincent.
[20 Jan 2009 9:44] Tonci Grgin
Vincent, what happens if you add 'Treat Tiny As Boolean' option to connection string? Does it work as expected then?
[20 Jan 2009 9:56] Vincent Courcelle
Hello,

Here is my connection string:
Database=GangStake;Data Source=localhost;User Id=myuser;Password=mypassword;Treat Tiny As Boolean=True

This don't help... :

<xs:element name="CheckedAuthenticity" type="xs:byte" minOccurs="0" />
[20 Jan 2009 17:44] Tonci Grgin
Generated xsd

Attachment: bug41953.xsd (application/xml, text), 750 bytes.

[20 Jan 2009 17:47] Tonci Grgin
Hi Vincent. As you can see from attached xsd, I have no such problems.

Test environment:
  o MySQL server 5.0.30GA on Win 2k8 x64 localhost.
  o VS2005Pro, latest sources of c/NET 5.2 from public repository
Test case:
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=****;PORT=3306;Allow Zero Datetime=True;Treat Tiny As Boolean=True";
            conn.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            bool ok = true;

            cmd.CommandText = "DROP TABLE IF EXISTS `bug41953`";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE `bug41953` (`Id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `CheckedAuthenticity` BOOLEAN NOT NULL DEFAULT 0) ENGINE = innodb";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO `bug41953` (`Id`,`CheckedAuthenticity`) VALUES (NULL,0)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO `bug41953` (`Id`,`CheckedAuthenticity`) VALUES (NULL,1)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "SELECT * FROM `bug41953` LIMIT 1";
            DataSet dataSet = new DataSet();

            MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
            string genericTableName = null;
            string typedTableName = null;

            for (int i = 0; i < dataSet.Tables.Count; i++)
            {
                genericTableName = "Table";
                typedTableName = dataSet.Tables[i].TableName;
                if (i != 0)
                { genericTableName += i; }
                dataAdapter.TableMappings.Add(genericTableName, typedTableName);
            }

            dataAdapter.SelectCommand = cmd;
            dataAdapter.Fill(dataSet);

            dataSet.Tables[0].TableName = "bug41953";
            dataSet.DataSetName = "bug41953DS";

            try
            {
                dataSet.WriteXmlSchema("bug41953.xsd");
            }
            catch (Exception ex)
            {
                ok = false;
                Console.Out.WriteLine("Exception " + e.ToString());
            }
            if (ok) Console.Out.WriteLine("succeded");
        }
[20 Jan 2009 18:34] Vincent Courcelle
So strange.
I will try using other versions of mysqld but I don't understand how this can influence the generated xsd.
[21 Jan 2009 7:31] Tonci Grgin
Vincent, I do believe your problem to be real. But you have to find a way to present it to me so I can verify. This includes many variables to check; different MySQL servers, windows boxes ... I will be monitoring this report in spite of my ruling.
For a start, you can try my test on failing machine and draw new conclusions.
[6 May 2009 7:38] Agrinei Sousa
test case for this bug - tinyints are returned as System.SByte

Attachment: mySQLTestTinyAsBoolean.zip (application/x-zip-compressed, text), 236.35 KiB.

[6 May 2009 7:40] Agrinei Sousa
I'm facing the same problem... tinyints are returned as System.SByte

MySQL Version: 5.0.51b-community-nt
MySQL .Net Connector: 5.2.5 or 5.2.6 (it fails with both of them)

I've attached a test case which demonstrates the issue. Just create the test table and run the sample.

create table testTable ( tinyIntColumn bool not null, description varchar(32) null);
insert into testTable ( tinyIntColumn ) values ( false );
[24 Jul 2009 6:26] Tonci Grgin
Seemingly, I was wrong, please see Bug#46205.
[24 Jul 2009 6:26] Tonci Grgin
Duplicate of Bug#46205.