| Bug #27282 | TEXT fiels with binary collations are processed like binary fields by myODBC | ||
|---|---|---|---|
| Submitted: | 20 Mar 2007 9:36 | Modified: | 25 Jan 2013 23:13 |
| Reporter: | Ilya Zvyagin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51 & 5.1 latest | OS: | Windows (win32) |
| Assigned to: | Lawrenty Novitsky | CPU Architecture: | Any |
| Tags: | latin1_bin, SQL_LONGVARBINARY, text | ||
[23 Mar 2007 14:52]
Tonci Grgin
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Explanation: Hi Ilya and thanks for your report. This is expected behavior of MyODBC connector.
[25 Mar 2007 15:09]
Ilya Zvyagin
Thank you, Tonci. Can I have a direct link to the part of documentation where this particular behavior is bescribe ? We have a client application using MyODBC Connecor and we will have to find a workaround for this situation.
[26 Mar 2007 7:01]
Tonci Grgin
Ilya, you mean you didn't search or you can't find it? I shouldn't be doing this but: http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html - The BINARY attribute is shorthand for specifying the binary collation of the column character set. In this case, sorting and comparison are based on numeric character values. (Before MySQL 4.1, BINARY caused a column to store binary strings and sorting and comparison were based on numeric byte values. This is the same as using character values for single-byte character sets, but not for multi-byte character sets.)
[3 Apr 2007 14:23]
Ilya Zvyagin
Thank you for reply and excuse me for delaied answer. I could not answer befour. I think you confuse something. First, I have not found in the pointed pages clear indication of this behaviour of ODBC driver. I know an understand what binary collation is and what is an expected behavior of such fields. But the bug is not about sorting or comparing these fields but about getting textual representation. TEXT fields should not be returned as xxBINARY data type fields. This is obvious as otherwise there is no way to get correct textual representation of these fields. Again, we do not compare or sort those fields, we try to receive textual representation of those fields and as field data type is xxTEXT, not xxBINARY, these fields should not be converted to hexadecimal representation. Thank you.
[20 Apr 2007 14:08]
zumba lavache
Hello, I'm having excaly the same problem. I think it's a bug too, and not an expected behavior, because previous versions of odbc/mysql (3.51.6 / 4.1) returned correct textual values...so for me upgarding mysql introduced a regression. There is no way go over this bug by modifying the code as far as I know. It happens on select queries but on "show create table" queries too, the "create table" being returned as binary and in this cas we cannot even change collation of the column on the scheme ! I look forward for any information that can help me ! thanks
[24 Nov 2009 12:04]
Tonci Grgin
Guys, I apologize for overlooking your responses... Is this still relevant?
[24 Nov 2009 12:11]
Tonci Grgin
Apparently not: create table bug27282 (Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Txt TEXT, Blb BLOB); Query OK, 0 rows affected (0.03 sec) select * from bug27282 SQLDescribeCol: In:StatementHandle = 0x00645B48, ColumnNumber = 2, ColumnName = 0x001C3F68, BufferLength = 600, NameLengthPtr = 0x001B1830, DataTypePtr = 0x001B11D8, ColumnSizePtr = 0x001B15C0, DecimalDigits = 0x001B8AF0, NullablePtr = 0x001B8B08 Return: SQL_SUCCESS=0 Out: *ColumnName = "Txt", *NameLengthPtr = 3, *DataTypePtr = SQL_WLONGVARCHAR=-10, *ColumnSizePtr = 65535, *DecimalDigits = 0, *NullablePtr = SQL_NULLABLE=1 SQLDescribeCol: In:StatementHandle = 0x00645B48, ColumnNumber = 3, ColumnName = 0x001C3F68, BufferLength = 600, NameLengthPtr = 0x001B1830, DataTypePtr = 0x001C1F50, ColumnSizePtr = 0x001B15C0, DecimalDigits = 0x001B8AF0, NullablePtr = 0x001B8B08 Return: SQL_SUCCESS=0 Out: *ColumnName = "Blb", *NameLengthPtr = 3, *DataTypePtr = SQL_LONGVARBINARY=-4, *ColumnSizePtr = 65535, *DecimalDigits = 0, *NullablePtr = SQL_NULLABLE=1
[24 Nov 2009 12:24]
Tonci Grgin
Retesting with provided table structure yields wrong behavior...
All fields are correctly mapped while *TEXT fields are still retrieved as sequence of bytes:
select * from t9
SQLExecDirect:
In: hstmt = 0x0323E2A0, szSqlStr = "", cbSqlStr = -3
Return: SQL_SUCCESS=0
Get Data All:
"c23", "c24", "c25", "c26", "c27", "c28", "c29", "c30"
0x74696E79626C6F62, "74696E7974657874", 0x626C6F62, "74657874", 0x6D656469756D626C6F62, "6D656469756D74657874", 0x6C6F6E67626C6F62, "6C6F6E6774657874"
1 row fetched from 8 columns.
However, this could be related to metadata returned by server:
mysql> select * from t9;
Field 1: `c23`
Catalog: `def`
Database: `test`
Table: `t9`
Org_table: `t9`
Type: BLOB
Collation: binary (63)
Length: 255
Max_length: 8
Decimals: 0
Flags: BLOB BINARY
Field 2: `c24`
Catalog: `def`
Database: `test`
Table: `t9`
Org_table: `t9`
Type: BLOB
Collation: latin1_swedish_ci (8)
Length: 255
Max_length: 8
Decimals: 0
Flags: BLOB BINARY
Field 3: `c25`
Catalog: `def`
Database: `test`
Table: `t9`
Org_table: `t9`
Type: BLOB
Collation: binary (63)
Length: 65535
Max_length: 4
Decimals: 0
Flags: BLOB BINARY
Field 4: `c26`
Catalog: `def`
Database: `test`
Table: `t9`
Org_table: `t9`
Type: BLOB
Collation: latin1_swedish_ci (8)
Length: 65535
Max_length: 4
Decimals: 0
Flags: BLOB BINARY
Field 5: `c27`
Catalog: `def`
Database: `test`
Table: `t9`
Org_table: `t9`
Type: BLOB
Collation: binary (63)
Length: 16777215
Max_length: 10
Decimals: 0
Flags: BLOB BINARY
Field 6: `c28`
Catalog: `def`
Database: `test`
Table: `t9`
Org_table: `t9`
Type: BLOB
Collation: latin1_swedish_ci (8)
Length: 16777215
Max_length: 10
Decimals: 0
Flags: BLOB BINARY
Field 7: `c29`
Catalog: `def`
Database: `test`
Table: `t9`
Org_table: `t9`
Type: BLOB
Collation: binary (63)
Length: 4294967295
Max_length: 8
Decimals: 0
Flags: BLOB BINARY
Field 8: `c30`
Catalog: `def`
Database: `test`
Table: `t9`
Org_table: `t9`
Type: BLOB
Collation: latin1_swedish_ci (8)
Length: 4294967295
Max_length: 8
Decimals: 0
Flags: BLOB BINARY
+----------+----------+------+------+------------+------------+----------+------
| c23 | c24 | c25 | c26 | c27 | c28 | c29 | c30
|
+----------+----------+------+------+------------+------------+----------+------
| tinyblob | tinytext | blob | text | mediumblob | mediumtext | longblob | longt
ext |
+----------+----------+------+------+------------+------------+----------+------
1 row in set (0.00 sec)
In any case, it would be nice to return humanly readable output for *TEXT columns.
Workaround:
Use CAST: SELECT CAST(c24 AS CHAR) from t9
SQLExecDirect:
In: hstmt = 0x0323E300, szSqlStr = "", cbSqlStr = -3
Return: SQL_SUCCESS=0
Get Data All:
"CAST(c24 AS CHAR)"
"tinytext"
1 row fetched from 1 column.
[24 Nov 2009 12:30]
Tonci Grgin
c/ODBC should pay attention to collation flag to distinguish between these types. For *TEXT columns, Collation: latin1_swedish_ci (8). For *BLOB columns, Collation: binary (63). Otherwise, they look the same: Type: BLOB Flags: BLOB BINARY So it's not enough to just check on Type and Flags.
[25 Jan 2013 23:13]
John Russell
Added to changelog for 5.2.4: When a column with type TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT was retrieved from a table with a binary collation, the text fields were converted to a hexadecimal representation, even though these values were not really BLOBs. The unnecessary conversion could expand the data, causing overflow problems when storing the result values.
Description: A field with one of types: tinytext, text, mediumtext, longtext are processed and returned by MySQL ODBC Driver as SQL_LONGVARBINARY (-4) fields. SQLDescribeCol returns SQL_LONGVARBINARY (-4) for this field and if the application asks to convert this field to SOLSQL_C_CHAR the result buffer is filled with hexadecimal representation of field value. This can even lead to result buffer overflow error as the application may be not prepared to offer a buffer of enough length (for the hex representation of a binary you should have a buffer two times longer). With mysql the returned values are text strings. How to repeat: ----------------------------------------------------- drop table if exists t9; create table t9 ( c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, c29 longblob, c30 longtext ) engine = innodb charset latin1 collate latin1_bin; insert into t9 set c23= 'tinyblob', c24= 'tinytext', c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', c29= 'longblob', c30= 'longtext'; commit work; select * from t9; drop table if exists t9; ----------------------------------------------------- drop table if exists t9 Command completed successfully, 0 rows affected. create table t9 ( c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, c29 longblob, c30 longtext ) engine = innodb charset latin1 collate latin1_bin Command completed successfully, 0 rows affected. insert into t9 set c23= 'tinyblob', c24= 'tinytext', c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', c29= 'longblob', c30= 'longtext' Command completed successfully, 1 rows affected. commit work Command completed successfully, 0 rows affected. select * from t9 c23 c24 c25 c26 c27 c28 c29 c30 --- --- --- --- --- --- --- --- 74696E79626C6F62 74696E7974657874 626C6F62 74657874 6D656469756D626C6F62 6D656469756D74657874 6C6F6E67626C6F62 6C6F6E6774657874 1 rows fetched. drop table if exists t9 Command completed successfully, 0 rows affected.