Re: unique key and utf8

Hi!

On Jun 30, Linus Upson wrote:
> The following was tested with MySQL 4.1.1 running on RedHat ES 3. This
> message was sent in UTF-8.
> 
> The unique key index prefix length is misleading when you use the utf8
> character set. If you specify a prefix of N characters the uniqueness
> constraint appears to be enforced for the first 3*N bytes of column,
> not the first N characters.

Oops.
I agree, it's confusing.
We'll fix it.
 
> I discovered this problem because mysql silently shortened the unique
> index prefix on one of my utf8 varchar columns. When I execute the
> following statement:
> 
> CREATE TABLE `t` (
>   `c` varchar(150) NOT NULL default '',
>   UNIQUE KEY `c` (`c`(150))
> ) TYPE=MyISAM DEFAULT CHARSET=utf8
> 
> there are no errors or warnings, but the result is:
> 
> mysql> show create table t \G
> *************************** 1. row ***************************
>        Table: t
> Create Table: CREATE TABLE `t` (
>   `c` varchar(150) NOT NULL default '',
>   UNIQUE KEY `c` (`c`(64))
> ) TYPE=MyISAM DEFAULT CHARSET=utf8
> 1 row in set (0.00 sec)
> 
> The 150 character prefix should still fit within the 500 byte index
> limit of 4.1.1 since 3 * 150 = 450, but for some reason the prefix has
> been reduced to 64.

I wasn't able to repeat in in MySQL 4.1.3 - index was created as
expected. MySQL *never* silently truncates a UNIQUE index (if it does,
it's a bug).
 
Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]