MySQL Bugs: #47001: Incorrect check for length of primary key

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

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.

Thank you for the problem report. Please, send the results of:

show create table mytable\G

from your environment.

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)

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