MySQL Bugs: #16668: "varchar binary" being stripped of "binary" option on MySQL 5

Bug #16668	"varchar binary" being stripped of "binary" option on MySQL 5
Submitted:	20 Jan 2006 12:45	Modified:	30 Jan 2006 9:33
Reporter:	Anders Henke	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.25 and 5.0.18	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 4.0, a field type of "varchar binary" was the way to create variable, yet
case-sensitive and don't-remove-trailing-space-compatible.
In MySQL 5.0 the "binary" option has been removed from the varchar type and
such fields should be created as a varbinary field.

However, when upgrading from MySQL 4.0 using mysqldump (dumping the 
MySQL 4 database to a file, import that file using MySQL 5), the "binary" option
is silently removed, and so its behaviour does change in quite strong way.

Tagged to "serious", as upgrading using dumps is recommend for cross-version updates
and the only workaround is either to alter the table to "varbinary" after the import or
to manually replace any "varchar binary" by "varbinary" in the dump files.

How to repeat:
create table foo (varchar(255) binary);

mysql 4 # mysqldump test > dump4
mysql 5 # mysql test < dump4

mysql 5> show create table foo;
... varchar(255)

Suggested fix:
Maybe it's a good idea to let MySQL 5 translate "varchar(255) binary"-creation/alter
statements into "varbinary(255)", as that's the type which most closely matches what
was intended and worked in MySQL 4.0.

That's also the "same" behaviour in a replication setup, where a 4.0-master exists
with a 5.0-slave: creating a field of type "varbinary(255)" on the 4.0-master does create
a "varchar(255) binary" field on the master and a "varbinary(255)" on the slave.
They do behave identically, so that's fine.

Thanks for your bug report:

CREATE TABLE `foo` (
  `t` varchar(255) character set latin1 collate latin1_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This is actually expected behaviour.

In 5.0 "varchar(N) binary" is as close to 4.0 "varchar(N) binary" as possible - it also results in variable, yet case-sensitive field. "binary" is not ignored, it forces the collation to be binary - see SHOW CREATE TABLE output, in Jorge's reply - the collation is "latin1_bin", which, precisely, means case-sensitive comparison.

varbinary(N) is different, it means the column is just a set of bytes, no character set attached. For example, UPPER() and LOWER() doesn't work on varbinary columns.

(in fact, in the early 4.1 versions, we did map varchar(N) binary to varbinary(N), it was changed later to mean binary collation, after we got numerous complains that UPPER/LOWER stopped working)