Bug #20374 Creating unique key failed

• Submitted: 10 Jun 2006 12:23
• Modified: 12 Jun 2006 7:35
• Reporter: Andre Timmer
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.18
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[10 Jun 2006 12:23] Andre Timmer

Description:
Creating unique key failed.

How to repeat:
create table melding_details (
  melding_id integer unsigned NOT null
, id             integer unsigned NOT null auto_increment
, pad          varchar(400)     NOT null
, waarde     varchar(200)     NOT null
--
, constraint melding_details_pk         PRIMARY KEY (melding_id, id)
, constraint melding_details_uk         UNIQUE  KEY (melding_id, pad)
)
engine innodb;

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Suggested fix:
It complaining about the uique key.

Length of column melding and pad is 404 bytes not 767!

The message is not correct and maybe creating an unique key should be possible as well.

[11 Jun 2006 3:13] Heikki Tuuri

Andre,

are you using the UTF-8 charset? Then every character takes up to 3 bytes.

Regards,

Heikki

[12 Jun 2006 7:22] Andre Timmer

Yes we use UTF-8. 
3 x 400 + length integer = 1204 still not 767.

[12 Jun 2006 7:22] Andre Timmer

Yes we use UTF-8. 
3 x 400 + length integer = 1204 still not 767.

[12 Jun 2006 7:31] Heikki Tuuri

Andre,

this is the problem in UTF-8: keys become long.

I am marking this as 'Not a bug'.

Regards,

Heikki

[12 Jun 2006 7:35] Andre Timmer

I agree. The text however could be improved upon would have saved us both some time.