Bug #30233 incorrect column type mapping for columns derived 5.1 XML functions
| Submitted: | 3 Aug 2007 18:18 | Modified: | 6 Sep 2007 10:37 |
|---|---|---|---|
| Reporter: | Lynn Eriksen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 5.07, 5.0.8GA | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[19 Aug 2007 16:11]
Tonci Grgin
Hi Lynn and thanks for another report. Although I agree with your analysis, I would ask two things from you: 1) Boil down test case to something *much* simpler 2) Test the behavior of server, as I think this might actually be a server bug/inconsistency (if TYPE is LONG_BLOB then FLAG should have contained BLOB, right?): Server started with mysqld --stanalone --console on WinXP Pro SP2 localhost. C:\mysql507\mysql-5-1-22-beta-pb1555\bin>mysql -uroot -p --column-type-info bug3 0233 Server version: 5.1.22-beta-pb1555 MySQL Pushbuild Edition, build 1555 mysql> SELECT * FROM `test`.`contents_catalog_info` LIMIT 1; Field 1: `CatalogID` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: LONG Collation: binary (63) Length: 10 Max_length: 0 Decimals: 0 Flags: NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY Field 2: `WebSiteGuid` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 120 Max_length: 0 Decimals: 0 Flags: NOT_NULL MULTIPLE_KEY PART_KEY Field 3: `SectionGuid` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 60 Max_length: 0 Decimals: 0 Flags: NOT_NULL Field 4: `CatalogGuid` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 60 Max_length: 0 Decimals: 0 Flags: NOT_NULL UNIQUE_KEY PART_KEY Field 5: `CatalogType` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 512 Max_length: 0 Decimals: 0 Flags: NOT_NULL MULTIPLE_KEY NO_DEFAULT_VALUE PART_KEY Field 6: `ListingType` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 60 Max_length: 0 Decimals: 0 Flags: NOT_NULL Field 7: `IsPublished` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: LONG Collation: binary (63) Length: 11 Max_length: 0 Decimals: 0 Flags: NOT_NULL NUM Field 8: `CreateDate` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: DATETIME Collation: binary (63) Length: 19 Max_length: 0 Decimals: 0 Flags: NOT_NULL BINARY Field 9: `ModifyDate` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: TIMESTAMP Collation: binary (63) Length: 19 Max_length: 0 Decimals: 0 Flags: NOT_NULL UNSIGNED ZEROFILL BINARY TIMESTAMP Field 10: `CatalogMeta` Catalog: `def` Database: `test` Table: `contents_catalog_info` Org_table: `contents_catalog` Type: BLOB Collation: latin1_swedish_ci (8) Length: 16777215 Max_length: 0 Decimals: 0 Flags: NOT_NULL BLOB NO_DEFAULT_VALUE Field 11: `Name` Catalog: `def` Database: `` Table: `contents_catalog_info` Org_table: `` Type: LONG_BLOB Collation: latin1_swedish_ci (8) Length: 50331648 Max_length: 0 Decimals: 31 Flags: NOT_NULL Field 12: `Summary` Catalog: `def` Database: `` Table: `contents_catalog_info` Org_table: `` Type: LONG_BLOB Collation: latin1_swedish_ci (8) Length: 50331648 Max_length: 0 Decimals: 31 Flags: NOT_NULL Field 13: `Comments` Catalog: `def` Database: `` Table: `contents_catalog_info` Org_table: `` Type: LONG_BLOB Collation: latin1_swedish_ci (8) Length: 50331648 Max_length: 0 Decimals: 31 Flags: NOT_NULL Field 14: `Security` Catalog: `def` Database: `` Table: `contents_catalog_info` Org_table: `` Type: LONG_BLOB Collation: latin1_swedish_ci (8) Length: 50331648 Max_length: 0 Decimals: 31 Flags: NOT_NULL 0 rows in set (0.00 sec) mysql>
[24 Aug 2007 8:18]
Tonci Grgin
Lynn, thanks for info provided, rechecking.
[24 Aug 2007 9:16]
Tonci Grgin
Hmm, I rushed to conclusion... Manual gives explanation of behavior observed here: http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html Use of the BLOB_FLAG, ENUM_FLAG, SET_FLAG, and TIMESTAMP_FLAG flags is deprecated because they indicate the type of a field rather than an attribute of its type. It is preferable to test field->type against MYSQL_TYPE_BLOB, MYSQL_TYPE_ENUM, MYSQL_TYPE_SET, or MYSQL_TYPE_TIMESTAMP instead. Simplified query showing the difference in metadata output from 5.0 to 5.1: mysql> select c.*, CONCAT(c.CatalogMeta, " ", c.ListingType) AS AddFld from contents_catalog AS c LIMIT 1; +--------------------+ | @@version | +--------------------+ | 5.1.22-beta-pb1555 | +--------------------+ Field 10: `CatalogMeta` Catalog: `def` Database: `test` Table: `c` Org_table: `contents_catalog` Type: BLOB Collation: latin1_swedish_ci (8) Length: 16777215 Max_length: 476 Decimals: 0 Flags: NOT_NULL BLOB NO_DEFAULT_VALUE Field 11: `AddFld` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONG_BLOB Collation: latin1_swedish_ci (8) Length: 16777216 Max_length: 484 Decimals: 31 Flags: +-------------------+ | @@version | +-------------------+ | 5.0.48-pb1038-log | +-------------------+ Field 10: `CatalogMeta` Catalog: `def` Database: `test` Table: `c` Org_table: `contents_catalog` Type: BLOB Collation: latin1_swedish_ci (8) Length: 16777215 Max_length: 476 Decimals: 0 Flags: NOT_NULL BLOB NO_DEFAULT_VALUE Field 11: `AddFld` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 8192 Max_length: 484 Decimals: 31 Flags: I guess you're right again, thanks. Verified as described with test case provided. According to manual, we should test field->type instead of flags for servers 5.0 and up.
[24 Aug 2007 15:26]
Lynn Eriksen
Good deal. Glad to help.
[24 Aug 2007 20:56]
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/33057
[24 Aug 2007 21:05]
Reggie Burnett
Fixed in 1.0.11, 5.0.9, 5.1.3, and 5.2+
[24 Aug 2007 21:09]
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/33061
[6 Sep 2007 10:37]
MC Brown
A note has been added to the 5.1.3, 5.0.8 and 1.0.11 changelogs: Extracting data through XML functions within a query returns the data as System.Byte[]. This was due to Connector/NET incorrectly identifying BLOB fields as binary, rather than text.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.063 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: For queries that have data extracted from Xml using the XML functions in 5.1 the column type is being mapped as System.Byte[] instead of System.String. I have tracked this issue to what I belive to be an error in the MySqlField in the 'SetTypeAndFlags' method/ How to repeat: Here is the breakdown for the scenario: 1) The type is determined to 'LongBlob' 2) The MySqlField property 'IsBlob' is only returning 'true' when 'colFlags & ColumnFlags.BLOB > 0'. The column flags for the xml-function derived column are only showing as 'Not_Null', or 1. The 'mySqlDbType' is never checked. 3) The 'IsBinary' funcion seems to be reporting correctly that the Xml-function derived column is not null. Since the 'IsBlob' property is not returning correctly the section of the routine that changes non-binary blob types to corresponding text types ('if (IsBlob && !IsBinary)') is not called. Suggested fix: I would suggest testing the mySqlDbType additionaly in the 'IsBlob' method: Here is the old code: public bool IsBlob { get { return (colFlags & ColumnFlags.BLOB) > 0; } } Here is what I propose: public bool IsBlob { get { //primary - determine from colFlags //---------------------------------- //determine if blob from colFlags if ((colFlags & ColumnFlags.BLOB) > 0) { return true; } //secondary - determine from mySqlDbType //---------------------------------- switch (mySqlDbType) { case MySqlDbType.TinyBlob: return true; case MySqlDbType.MediumBlob: return true; case MySqlDbType.LongBlob: return true; case MySqlDbType.Blob: return true; } //default return return false; } }