MySQL Bugs: #28306: VARCHAR-Fields with _bin-Collation results in binary

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

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

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.

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.

Both ODBC connectors return result in form 0xHello... just as described in Bug#27633.

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.

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.

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.

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.

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)...