MySQL Bugs: #28403: cannot set binary flag on varchar fields in MyISAM tables

Bug #28403	cannot set binary flag on varchar fields in MyISAM tables
Submitted:	13 May 2007 17:21	Modified:	28 Mar 2009 17:32
Reporter:	Wes Morgan	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.41	OS:	Linux (Red Hat Enterprise Linux 4 (amd64))
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Starting with version 5.0.41 (i.e. 5.0.37 and early work OK), setting the binary flag on a varchar field in a MyISAM table does not stick. As soon as you look at the table definition again, it's unset.

This has severe consequences for some applications, as among other things, it makes varchar fields case insensitive. This breaks at least one major application we're using (WebGUI CMS).

How to repeat:
Try to set the binary flag on a varchar field in a MyISAM table. Then look at the table definition again (or refresh the view in your client program) and you'll see that the flag is unset. You can then try a query that relies on case sensitivity in the varchar field and verify that the field is case insensitive.

Thank you for the report.

I get next results with last sources:
mysql> create table t1(f1 varchar(22) binary);
Query OK, 0 rows affected (0.15 sec)

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `f1` varchar(22) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

This is expected result. See also http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

Please confirm or reject if you run different queries/get different result.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Setting the collation to binary is not sufficient.  When trying to get data using the MySQL/Net connector it's impossible to call dataReader.GetBytes on any column, because columns are NEVER flagged as binary.  The exception reported is :

GetBytes can only be called on binary columns

This is a bug.

no no,

thank you for the feedback. But you tell about problems with Connector/NET while this report is about server behavior. Additionally original reporter did not describe problem correctly, so we even don't know how to reproduce it. Please create separate bug report for Connector/NET problem and attach repeatable test case to that new bug.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".