| Bug #35211 | text fields returned in hex encoding when using utf8_bin collation | ||
|---|---|---|---|
| Submitted: | 11 Mar 2008 13:30 | Modified: | 18 Mar 2008 10:22 |
| Reporter: | Zoltán Papp | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 3.51.23, 5.1.3 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[11 Mar 2008 13:39]
Tonci Grgin
Hi Zoltán and thanks for your report. 3.51.17 is rather old version, please upgrade and inform us of results.
[11 Mar 2008 14:04]
Zoltán Papp
Tested with mysql-connector-odbc-3.51.23-linux-x86-64bit package with the exact same results. Sorry, I forgot to mention that I'm testing on 64bit architecture.
[14 Mar 2008 18:08]
Tonci Grgin
Zoltán, no problem, processor architecture shouldn't matter in this... This actually seems to be a bug. Let me check more.
[14 Mar 2008 19:16]
Tonci Grgin
Zoltán, there is no bug here. You got expected result using "bin" collation. Please correct your database/table definition as described below. Let me try to explain. How ever we look at it, c/ODBC 5.1 discovers field type correctly based on server's metadata. This is trace when using "bin" collation like you did (and getting "wrong" result): odbct32w 1084-1318 EXIT SQLDescribeColW with return code 0 (SQL_SUCCESS) HSTMT 00852280 UWORD 2 WCHAR * 0x00096F2C SWORD 100 SWORD * 0x00000000 SWORD * 0x00096FF4 (-10) SQL_WLONGVARCHAR - correct SQLULEN * 0x00096FF8 (65535) SWORD * 0x00000000 SWORD * 0x00000000 and due to "bin" collation result looks wrong: odbct32w 1084-1318 EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 00852280 UWORD 2 SWORD 1 <SQL_C_CHAR> PTR 0x00097B68 [ 20] "746578742076616C7565" SQLLEN 65535 SQLLEN * 0x0006EFE4 (20) A proper thing to do would be *not* to use "bin" collation as it means "no charset" but, like in this example, use general collation: mysql> create table bug35211(v varchar(255), t text) default character set utf8 collate utf8_general_ci; Query OK, 0 rows affected (0.02 sec) mysql> insert into bug35211 values ('varchar value', 'text value'); Query OK, 1 row affected (0.00 sec) which will, in turn, produce correct result: Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN 'test51-utf'. SELECT * FROM bug35211 SQLExecDirect: In: hstmt = 0x00851FE0, szSqlStr = "", cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "v", "t" "varchar value", "text value" 1 row fetched from 2 columns. odbct32w 11a0-1114 EXIT SQLDescribeColW with return code 0 (SQL_SUCCESS) HSTMT 00852280 UWORD 2 WCHAR * 0x00096F2C SWORD 100 SWORD * 0x00000000 SWORD * 0x00096FF4 (-1) SQL_LONGVARCHAR SQLULEN * 0x00096FF8 (65535) SWORD * 0x00000000 SWORD * 0x00000000 and odbct32w 11a0-1114 EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 00852280 UWORD 2 SWORD 1 <SQL_C_CHAR> PTR 0x00097B68 [ 10] "text value" SQLLEN 65535 SQLLEN * 0x0006EFE4 (10) Although in this example 3.51 returns correct result, you may get in trouble using it with MB c-sets, please try 5.1. Be careful of "character_set_results" variable too!
[15 May 2008 10:09]
Tonci Grgin
Zoltán, I'm sorry I missed your private comment. Please see discussion in Bug#36517 for details on server side implementation.
Description: When creating a table with utf8 encoding and utf8_bin collation MyODBC treats text fields as binary and returns the hex encoding of the contents. When using the mysql client everything seems fine. varchar fields are handled properly. Note that if you alter the collation _after_ creating the table the problem doesn't appear. However this is not a suitable workaround because between the two steps the collation will be case insensitive which can cause unique constraint violations when you are converting a table to utf8. OS: Linux, Fedora Core 5 MySQL version: tested with 5.0.27 and 5.0.51a. MyODBC: 3.51.17r581 (from Fedora Core 5 package) unixODBC: 2.2.11 How to repeat: create database d default character set utf8 collate utf8_bin; use d; create table t (v varchar(255), t text); insert into t values ('varchar value', 'text value'); edit your odbc.ini accordingly and then: echo "select * from t" | isql -v d root EXPECTED the values "varchar value" and "text value". GOT the values "varchar value" and "746578742076616C7565", which equals hex("text value").