| Bug #6802 | MySQL 4.0's VARCHAR(NN) BINARY is interpreted as VARBINARY(NN) in 4.1 | ||
|---|---|---|---|
| Submitted: | 24 Nov 2004 15:24 | Modified: | 25 Nov 2004 11:59 |
| Reporter: | Timothy Smith | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 4.1 | OS: | Any (all) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[24 Nov 2004 16:21]
Paul DuBois
Just some additional info with version notes: If you create a table with varchar(n) binary, then SHOW CREATE TABLE shows it as varchar(n) binary up through 4.1.1. From 4.1.2 on, it shows the column as varchar(n) character set latin1 collate latin1_bin (assuming a default character set of latin1). If you create a table with varchar(n) binary in 4.0 and then upgrade to 4.1.x, SHOW CREATE TABLE shows the column as: 4.1.0: varchar(n) character set latin1 4.1.1 and up: varchar(n) binary One could say there's been a distinct difference in handling of the BINARY column attribute for some time.
[24 Nov 2004 16:31]
Paul DuBois
Sorry, I have a correction to my previous comment. The last part should say: If you create a table with varchar(n) binary in 4.0 and then upgrade to 4.1.x, SHOW CREATE TABLE shows the column as: 4.1.0: varchar(n) character set latin1 4.1.1: varchar(n) binary 4.1.2 and up: varbinary(n)
[25 Nov 2004 11:59]
Alexander Barkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
fixed in 4.1.8
It now reports as {VAR}CHAR(N) CHARACTER SET latin1 COLLATE latin1_bin
Description: When a table is created in 4.0 with a VARCHAR() BINARY field, and then that same table is opened in 4.1, the field shows up as a VARBINARY(), instead of as VARCHAR() ... collate xxx_bin. This causes problems because functions like UPPER() don't work on a VARBINARY. How to repeat: create table x (v varchar(10) binary); -- under MySQL 4.0 insert into table x values ('abc'); -- under MySQL 4.0 show create table x\G -- under 4.1 select upper(v) from x; -- under 4.1 Suggested fix: This problem comes from not wanting to convert BLOB fields into TEXT. However, it seems that since there are no TEXT BINARY fields, then we can handle the two cases separately. The BINARY flag can be treated as both BLOB and as collate ..._bin at the same time.