| Bug #34328 | Connector Net fails when i doing a Max/Min sentence with MySQL 4.1.9 | ||
|---|---|---|---|
| Submitted: | 5 Feb 2008 19:35 | Modified: | 12 Feb 2008 9:09 |
| Reporter: | Iván Méndez | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 5.1.4 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[5 Feb 2008 19:35]
Iván Méndez
[6 Feb 2008 14:10]
Tonci Grgin
Hi Iván and thanks for your report. I will check this although 4.1.9 is rather old, but what's puzzling is that http://dev.mysql.com/doc/refman/5.1/en/connector-net-versions.html does not state MySQL server 4.1 should work with c/NET 5.1 branch...
[11 Feb 2008 18:03]
Tonci Grgin
Iván, as a matter of fact, you can. Please attach complete C# test case (ignore data, I think I have one table suitable for this test, but do post table structure) and an output from mysql command line client + output from same table/queries when run with c/NET.
[11 Feb 2008 20:15]
Iván Méndez
Project Error Sample
Attachment: MySQL Tester.zip (application/x-zip-compressed, text), 144.79 KiB.
[11 Feb 2008 20:22]
Iván Méndez
Previously, i'll send you a project example. In the same ZIP file, i'm attaching two screenshots result for the same SQL query. The VC# Project screenshot is when assigns the Dataset without check if have System.Byte arrays. Let me know if this samples can help you. Regards
[12 Feb 2008 5:34]
Tonci Grgin
Thanks Iván, looking into this.
[12 Feb 2008 9:09]
Tonci Grgin
Iván, I see now what could have caused the problem:
C:\mysql\bin>mysql -uroot -T test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.22-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT name As Month, Max(date1) As 'Max Date', Min(date2) As 'Min Date'
FROM times GROUP BY name ORDER BY 'Min Date';
Catalog: 'def'
Database: 'test'
Table: 'times'
Name: 'Month'
Type: 254 - MYSQL_TYPE_STRING
Length: 25
Max length: 8
Is_null: 1
Flags: 32768 - GROUP_FLAG
Decimals: 0
Catalog: 'def'
Database: 'test'
Table: 'times'
Name: 'Max Date'
Type: 12 - MYSQL_TYPE_DATETIME
Length: 19
Max length: 19
Is_null: 1
Flags: 128 - BINARY_FLAG
Decimals: 0
Catalog: 'def'
Database: 'test'
Table: 'times'
Name: 'Min Date'
Type: 7 - MYSQL_TYPE_TIMESTAMP
Length: 19
Max length: 19
Is_null: 0
Flags: 225 - BINARY_FLAG + NOT_NULL_FLAG + 96
Decimals: 0
+----------+---------------------+---------------------+
| Month | Max Date | Min Date |
+----------+---------------------+---------------------+
| January | 2008-01-31 12:00:00 | 2008-01-01 12:00:00 |
| February | 2008-02-29 12:00:00 | 2008-02-01 12:00:00 |
| March | 2008-03-31 12:00:00 | 2008-02-01 12:00:00 |
+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql>
Notice that both fields have BINARY flag set. Even so I was not able to repeat the problem you describe with test case attached on:
MySQL server 4.1.22BK on WinXP Pro SP2 localhost
.NET FW v2.0.50727 (SP1) and c/NET 5.1.4 GA
There might be a change in server behavior from 4.1.9 -> 4.1.22 fixing this as this is not a connector bug due to insufficient metadata returned by server. Until metadata is fully and properly returned by server I'd advise use of CAST().
I'm also attaching resulting fields description from VS debugger so you might see types are correct.
[12 Feb 2008 9:09]
Tonci Grgin
SShot
Attachment: bug34328.JPG (image/jpeg, text), 37.58 KiB.
[12 Feb 2008 9:11]
Tonci Grgin
Dbg info on column types
Attachment: bug34328.txt (text/plain), 9.31 KiB.
[1 Jul 2009 15:55]
Neil McLaughlin
After investigation of an incidence of this problem on version 4.0.30-standard-log We discovered the following information. Our query was joining across 3 tables and using an IN clause. If the IN clause grew above a certain size then the returned type of MIN and MAX values on datetime columns switched from being: Field 6: `MissionFirstTimeDrive` Catalog: `` Database: `` Table: `` Org_table: `` Type: DATETIME Collation: ? (0) Length: 19 Max_length: 19 Decimals: 0 Flags: PART_KEY To the following: Field 6: `MissionFirstTimeDrive` Catalog: `` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: ? (0) Length: 19 Max_length: 19 Decimals: 0 Flags: BINARY A DESCRIBE on the query also revealed that at the point where the returned type switched the query started to use a temporary table: ----------+------+----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+--------+----------------+---------+---------+--------------------------+------+----------------------------------------------+ | T_Driver | ALL | PRIMARY | NULL | NULL | NULL | 189 | Using where; Using temporary; Using filesort | | T_Mission | ref | idx1,idx2,idx4 | idx1 | 4 | T_Driver.DriverCode | 1869 | Using where | | T_MCU | eq_ref | PRIMARY | PRIMARY | 4 | T_Mission.MissionMCUCode | 1 | Using where | +-----------+--------+----------------+---------+---------+--------------------------+------+----------------------------------------------+ versus the query plan on the query which returns a datetime: +-----------+--------+----------------+---------+---------+--------------------------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+--------+----------------+---------+---------+--------------------------+------+-------------+ | T_Driver | range | PRIMARY | PRIMARY | 2 | NULL | 12 | Using where | | T_Mission | ref | idx1,idx2,idx4 | idx1 | 4 | T_Driver.DriverCode | 1869 | Using where | | T_MCU | eq_ref | PRIMARY | PRIMARY | 4 | T_Mission.MissionMCUCode | 1 | Using where | +-----------+--------+----------------+---------+---------+--------------------------+------+-------------+ In the end we had to add CASTs to the MIN and MAX values Hope this information is of help to others.
