MySQL Bugs: #41953: BOOLEAN not recognized any more

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:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	boolean, datasets, regression

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");

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.

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!

Thanks Vincent.

Vincent, what happens if you add 'Treat Tiny As Boolean' option to connection string? Does it work as expected then?

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" />

Generated xsd

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

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");
        }

So strange.
I will try using other versions of mysqld but I don't understand how this can influence the generated xsd.

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.

test case for this bug - tinyints are returned as System.SByte

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

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 );

Seemingly, I was wrong, please see Bug#46205.

Duplicate of Bug#46205.