Bug #28306 VARCHAR-Fields with _bin-Collation results in binary
Submitted: 8 May 2007 14:54 Modified: 9 May 2007 14:43
Reporter: Roland Volkmann Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.14, 3.51.15, 5.0.11 OS:Windows
Assigned to: CPU Architecture:Any
Tags: BINARY, collation, varchar

[8 May 2007 14:54] Roland Volkmann
Description:
If you have table with varchar column using _bin collation e.g. latin1/latin1_bin ODBC-connecter maps them to binary fields instead of text. This behaviour starts with version 3.51.14, last correct working version is 3.51.12-2. So you can't access such fields to select correct contents.

How to repeat:
mysql> create table test (info varchar(20)
    -> character set latin1 collate latin1_bin);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test (info) values ('Hello world');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-------------+
| info        |
+-------------+
| Hello world |
+-------------+
1 row in set (0.00 sec)

mysql>

Now connect to this table using OpenOffice.org Base or Microsoft Access with ODBC-Connector 3.51.15, and try to select the above info. OpenOffice.org Base will show you "OBJECT" instead of "Hello world", and Microsoft Access will show you some binary garbage.

Suggested fix:
handling as it was until version 3.51.12-2
[9 May 2007 9:56] Tonci Grgin
Hi Roland and thanks for your report.

Hmm, where to start, this is in fact duplicate even if on newer version. Take you pick: Bug#27030, Bug#26993, Bug#27409, Bug#27633, Bug#27790 ... but the main problem is in Bug#10491. Seems that perfectly correct MyODBC patch will have to be rolled out.

In any case, if you start mysql cl client with -T option you'll see that your field has BINARY flag set and Access "garbage" should in fact be 0x + your text.
[9 May 2007 10:06] Tonci Grgin
Using generic MS client on your example I get correct result:

	Full Connect(Use Driver)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'myodbc1'.
SQLExecDirect:
				In:				hstmt = 0x008B1960, szSqlStr = "SELECT * FROM bug28306", 
										cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"info"
0xHello world
1 row fetched from 1 column.
[9 May 2007 11:18] Tonci Grgin
Both ODBC connectors return result in form 0xHello... just as described in Bug#27633.
[9 May 2007 14:43] Roland Volkmann
Hello Tonci,

thank you for reply. After having read the related bug reports, I agree that this one might be similar.

Possible solution:

If you get Binary-Flag *and* collation 63 (binary) then it's correct to map it as binary field to odbc application. But if you only get the binary flag with regular collation (with my example on my machine I get collation = 8 = latin1), then this field must be mapped to "text". I checked many charset/collation combinations of VARCHAR field in Innodb Table using a test program with MySQL C API, and every time the mentioned solution would be correct.

With best regards,
Roland.
[9 May 2007 15:01] Tonci Grgin
Yes Roland and I couldn't have put it better myself... But there's always some small *but*. This should and will be solved on server level rather than play this game of "Oh, the server says it's a blob, but the charset isn't 63 so it's a TEXT" in every connector. Maybe once we will rename "binary" collation too so that it's more apparent to all what it means.
[4 Dec 2007 15:35] Maarten Jansonius
Also, for those of us who really *need* the latin1_bin collation, the suggested work-around is not an option.
Our project is one such project. 
But we are in the process of migrating to utf (where we will use, guess what: utf8_bin collation ) -- so hopefully our woes will be gone soon.
[14 Feb 2008 8:20] Maarten Jansonius
Maarten Jansonius

Reporting on the utf8_bin : it's broken, and in exactly the same way as latin1_bin. 

Just for the record, because some ppl seem to suggest that there should be a workaround or something:  other than the sorting order,
a TEXT or VARCHAR field with charset latin1 and collation latin1_bin, should return exactly the same result as a field with charset latin1 and collation latin1_swedish_ci -- or any other colaltion for that matter. Ditto for utf8 in various collations.

It just so happens that for our (multilingual) application we need to distinguish all strings from eachother, so we really need the "_bin" collation...
 We've tried other collations, but with those, the unique key constraint on the string field (which we really want to keep) prevents us from inserting, for example, both "él" and "el" as separate entries.
[3 Mar 2008 11:56] Tonci Grgin
Maarten, are you sure this is not a server bug? Have you tried 5.1.3? Is there a bug report you posted on this, preferably with test case? If you're using *nix platform it could be problem with DM too (iODBC / UnixODBC)...