MySQL Bugs: #46453: MySql .NET connector strange behaviour

Bug #46453	MySql .NET connector strange behaviour
Submitted:	29 Jul 2009 15:54	Modified:	23 Jun 2010 23:33
Reporter:	Bogdan Patru	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.0.4.0	OS:	Windows (XP 64bit)
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	DataTable.Load, primary key

Description:
MySql version: 5.1.34 on Mac OS X Leopard

        public DataTable ExecuteTable(string sSql)
        {
            IDataReader _dr = ExecuteReader(sSql);
            DataTable SchemaTable = _dr.GetSchemaTable();
            DataTable _dt = new DataTable();
            _dt.Load(_dr);
            return _dt;
        }

Executing the following query does not set on the data table (_dt) any primary key:

SELECT    SP.PartNo, SPC.Description, C.Name AS CompanyName, SP.PKID AS PartNoId, C.PKID AS CompanyId  FROM    aeropartslocator.StockParts SP    INNER JOIN aeropartslocator.StockPartsCompany SPC ON SP.PKID=SPC.PartId    INNER JOIN aeropartslocator.Companies C ON SPC.CompanyId=C.PKID  WHERE    (SP.PartNo LIKE '%an3%' OR  SP.PartNoStripped LIKE '%an3%' OR SPC.Description LIKE '%an3%')    AND (SPC.Spareable=1 OR CompanyId IN (2)) ORDER BY   SP.PartNo LIMIT 0,10

But executing the following one generates a primary key on the wrong column:

SELECT    SP.PartNo, SPC.Description, C.Name AS CompanyName, SP.PKID AS PartNoId, C.PKID AS CompanyId  FROM    aeropartslocator.StockParts SP    INNER JOIN aeropartslocator.StockPartsCompany SPC ON SP.PKID=SPC.PartId    INNER JOIN aeropartslocator.Companies C ON SPC.CompanyId=C.PKID  WHERE    (SP.PartNo LIKE '%an3%' OR  SP.PartNoStripped LIKE '%an3%' OR SPC.Description LIKE '%an3%')    AND CompanyId IN (2) ORDER BY   `PartNo`  LIMIT 0,10

The only difference in the queries is that first one has this extra condition in where:

First query: AND (SPC.Spareable=1 OR CompanyId IN (2))
Seconf Query: CompanyId IN (2)

How to repeat:
1) Execute the script from private comments.
2) Create a .net 2.0 application that use the function on top. ExecuteReader is a function that executes a query and returns the reader. You can write something simple using mysql classes.
3) run the two queries in debug mode watching for SchemaTable variable on row 4.
4) for first query IsKey column is false. For second it is true.

Suggested fix:
Check the adapter code to see why is it doing this.

Hi Bogdan and thanks for your report.

I fail to see anything strange here. With strict condition on one column of course it's declared as PK for your particular query. Please do the same test in mysql command line client and do also DESCRIBE SELECT and see what happens.

I've uploaded two photos of what MySql says when I do The describe. (bug46453_pic1 and 2)
I've also uploaded two photos with query results. (bug46453_queryres1 and 2)

As you can see from the result photos the primary key is set to PartNoId. 

For the first query .Net driver does not set any primary key.
But the .Net driver sets for the second query the 'CompanyId' field as a primary key which is not correct.

What is wrong then?

Should there be for the first query at least PartNoId as the primary key? 

Why the driver says 'CompanyId' is the first query when:
 - the property IsKey is set to true
 - the property IsUnique is set to false

DESCRIBE on first query

Attachment: bug46453_pict1.png (image/png, text), 139.77 KiB.

DESCRIBE on second query

Attachment: bug46453_pict2.png (image/png, text), 136.34 KiB.

Query results first query

Attachment: bug46453_queryres1.png (image/png, text), 167.18 KiB.

Query results second query

Attachment: bug46453_queryres2.png (image/png, text), 167.17 KiB.

One mistake,

The last 3 lines of previous comment are:

Why the driver says 'CompanyId' is the primary key for the second query when:
 - the property IsKey is set to true
 - the property IsUnique is set to false

Bogdan, my guess is c/NET tries to do it's best...
Let's look at first query:
mysql> explain SELECT SP.PartNo, SPC.Description, C.Name AS CompanyName, SP.P
KID AS PartNoId, C.PKID AS CompanyId
FROM aeropartslocator.StockParts SP
INNER JOIN aeropartslocator.StockPartsCompany SPC ON SP.PKID=SPC.PartId
INNER JOIN aeropartslocator.Companies C ON SPC.CompanyId=C.PKID
WHERE (SP.PartNo LIKE '%an3%' OR SP.PartNoStripped LIKE '%an3%' OR PC.Description LIKE '%an3%') AND (SPC.Spareable=1 OR CompanyId IN (2))
ORDER BY SP.PartNo LIMIT 0,10;
+----+-------------+-------+--------+---------------------+---------+---------+-
-------------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys       | key     | key_len |
ref                            | rows | Extra                           |
+----+-------------+-------+--------+---------------------+---------+---------+-
-------------------------------+------+---------------------------------+
|  1 | SIMPLE      | SP    | ALL    | PRIMARY             | NULL    | NULL    |
NULL                           |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | SPC   | ALL    | UQ_CompanyId_PartId | NULL    | NULL    |
NULL                           |    1 | Using where; Using join buffer  |
|  1 | SIMPLE      | C     | eq_ref | PRIMARY             | PRIMARY | 4       |
aeropartslocator.SPC.CompanyId |    1 |                                 |
+----+-------------+-------+--------+---------------------+---------+---------+-
-------------------------------+------+---------------------------------+
So, no keys are used at all, thus c/NET can't use them too.

Second query:
mysql> explain SELECT SP.PartNo, SPC.Description, C.Name AS CompanyName, SP.PKID AS PartNoId, C.PKID AS CompanyId
FROM aeropartslocator.StockParts SP
INNER JOIN aeropartslocator.StockPartsCompany SPC ON SP.PKID=SPC.PartId
INNER JOIN aeropartslocator.Companies C ON SPC.CompanyId=C.PKID
WHERE (SP.PartNo LIKE '%an3%' OR SP.PartNoStripped LIKE '%an3%' OR PC.Description LIKE '%an3%') AND CompanyId IN (2)
ORDER BY `PartNo`  LIMIT 0,10;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL
 | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
So this query is screwed up as I have no data in tables. I guess the only constant c/NET can pick on is "AND CompanyId IN (2)". So I'd expect for CompanyId to be a PK here but that breaks your schema.

So I guess, the real question would be how is c/NET determining PK for such queries, right? To check on that I'll need some data too. Can you attach a dump with relevant portion of data please?

Hello,

Please find in the upload some data.

All the best,
Bogdan

Bogdan, using test case similar to yours, I get proper result for both queries (PK is dt.Column[0]):
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=**;Database=**;UserID=**;Password=**;PORT=**;Allow Zero Datetime=True;allow user variables = true; respect binary flags=false";
            conn.Open();

            //MySqlCommand cmdCreateTable = new MySqlCommand("SELECT SP.PartNo, SPC.Description, C.Name AS CompanyName, SP.PKID AS PartNoId, C.PKID AS CompanyId " + 
            //    "FROM aeropartslocator.StockParts SP "+
            //    "INNER JOIN aeropartslocator.StockPartsCompany SPC ON SP.PKID=SPC.PartId "+
            //    "INNER JOIN aeropartslocator.Companies C ON SPC.CompanyId=C.PKID "+
            //    "WHERE (SP.PartNo LIKE '%an3%' OR SP.PartNoStripped LIKE '%an3%' OR SPC.Description LIKE '%an3%') AND (SPC.Spareable=1 OR CompanyId IN (2)) "+
            //    "ORDER BY SP.PartNo LIMIT 0,10", conn);

            MySqlCommand cmdCreateTable = new MySqlCommand("SELECT SP.PartNo, SPC.Description, C.Name AS CompanyName, SP.PKID AS PartNoId, C.PKID AS CompanyId " +
                "FROM aeropartslocator.StockParts SP " +
                "INNER JOIN aeropartslocator.StockPartsCompany SPC ON SP.PKID=SPC.PartId " +
                "INNER JOIN aeropartslocator.Companies C ON SPC.CompanyId=C.PKID " +
                "WHERE (SP.PartNo LIKE '%an3%' OR SP.PartNoStripped LIKE '%an3%' OR SPC.Description LIKE '%an3%') AND CompanyId IN (2) " +
                "ORDER BY SP.PartNo LIMIT 0,10", conn);

            cmdCreateTable.CommandTimeout = 0;

            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(cmdCreateTable);
            da.Fill(dt);

            DisplayData(dt);
            dt.Clear();
            dt.Dispose();
            da.Dispose();
            cmdCreateTable.Dispose();
            conn.Close();

            Console.WriteLine("Done.");

Now I will check with test case closer to yours.

Test case

Attachment: Bug46453.txt (text/plain), 2.28 KiB.

Hi Bogdan.

Truly, second test case (attached) produces different result than first one I made:
1st query - no keys
2nd query - 
ColumnName = CompanyId
ColumnOrdinal = 5
ColumnSize = 11
NumericPrecision = 0
NumericScale = 0
IsUnique = False
IsKey = True
BaseCatalogName = 
BaseColumnName = PKID
BaseSchemaName = aeropartslocator
BaseTableName = Companies
DataType = System.Int32
AllowDBNull = False
ProviderType = 3
IsAliased = 
IsExpression = 
IsIdentity = 
IsAutoIncrement = True
IsRowVersion = False
IsHidden = 
IsLong = False
IsReadOnly = False
============================

Now I see two problems here:
  o Different behavior of first and second test case:
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(cmdQry);
            da.Fill(dt);
vs.
            MySqlDataReader dr = cmdQry.ExecuteReader();
            DataTable SchemaTable = dr.GetSchemaTable();

  o PK based on CONST which is contradiction in itself since all of the rows in data table have CompanyId of 2...

However, updatability of this type of query is highly doubtful thus I do not see the problem reported here as serious. Can you give me a use case proving this is a major culprit?

Hello Tonci,

You probably need to put in app.config or web.config the following lines

	<system.data>
		<DbProviderFactories>
			<clear/>
			<!-- To get PublicKeyToken run "sn -T my.dll" -->
			<add name="MySql 5 Provider" invariant="MySql.Data" description=".Net Framework Data Provider for Mysql" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.0.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
		</DbProviderFactories>
	</system.data>

This is my code:

===============================
string _Provider = "MySql.Data";
string _ConnectionString = "Server=**;Port=3306;Uid=**;Pwd=**;Connect Timeout=30;";
DbProviderFactory _factory = DbProviderFactories.GetFactory(_Provider);
DBConnection  _conn = _factory.CreateConnection();
_conn.ConnectionString = _ConnectionString;
 _conn.Open();

System.Data.IDataReader _dr = null;

DbCommand _comm = _factory.CreateCommand();
_comm.Connection = _conn;

// please replace here sSql with each query.
_comm.CommandText = sSql;

_dr = _comm.ExecuteReader();
  
// schema table is used to see why I was getting the errors
DataTable SchemaTable = _dr.GetSchemaTable();

DataTable _dt = new DataTable();
_dt.Load(_dr);

// with query 1 I get 10 rows in _dt
// query 2 returns 10 rows but because of the PK set _dt has 1 row (last of the 10)
 =================================

As you can see I have a wrapper on top of database dll which allows me to use it with any database without changing a line of code. So I do not use directly mysql classes.

All the best,
Bogdan

test case

Attachment: test.cs (text/plain), 3.89 KiB.

Could not reproduce using 5.5.5 server and the attached test case.

Closing, based on non-reproducibility. Will reopen if there is any need for clarification or if there is a working test case.