Bug #47001 Incorrect check for length of primary key
Submitted: 31 Aug 2009 5:29 Modified: 1 Sep 2009 6:58
Reporter: Bogdan Dz Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:6.0.11-alpha OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[31 Aug 2009 5:29] Bogdan Dz
Description:
A new table can't be created due to incorrect check of the length of primary key

How to repeat:
1.Create a new database with the UT8 character set.
CREATE DATABASE mydatabase CHARACTER SET utf8;

2.Attempt to create in 'mydatabase' a new table:
CREATE TABLE mytable (ID DECIMAL(18,0), MYCOLUMN VARCHAR(255) NOT NULL, CONSTRAINT PK_mytable PRIMARY KEY (MYCOLUMN));

you get the following error:
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

According to http://forums.mysql.com/read.php?10,156585,181455#msg-181455:

Utf8 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value.

Storage for variable-length columns includes length bytes, which are assessed against the row size. For example, a VARCHAR(255) CHARACTER SET utf8 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.
[31 Aug 2009 6:08] Valeriy Kravchuk
Thank you for the problem report. Please, send the results of:

show create table mytable\G

from your environment.
[31 Aug 2009 21:38] Bogdan Dz
The table doesn't get created with help of the above queries. Therefore I can't provide you with output of your test.

In the same time if I create table in the following way:
CREATE TABLE mytable (ID DECIMAL(18,0), MYCOLUMN VARCHAR(255) NOT NULL);

The output of your statement is:
show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `ID` decimal(18,0) DEFAULT NULL,
  `MYCOLUMN` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[1 Sep 2009 6:58] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with current development sources while bug is repeatable with version 6.0.11. Please either wait next 5.4.x release which inherits 6.0 series or use current sources from https://code.launchpad.net/~mysql/mysql-server/mysql-next