Bug #12522 Primary Key of two varchar(255) cannot be created

• Submitted: 11 Aug 2005 13:04
• Modified: 11 Aug 2005 20:23
• Reporter: Sönke Schau
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.10a
• OS: Windows (XP)
• CPU Architecture: Any

[11 Aug 2005 13:04] Sönke Schau

Description:
Trying to create the primary key consisting of two varchar columns results in:
Error 1071: Specified key was too long; max key length is 1024 bytes

Error disappears if total column size drops below 342 characters

How to repeat:
drop table IF EXISTS MQ;
CREATE TABLE MQ
(
	MessageId varchar (340) NOT NULL,
	Recipient varchar (2) NOT NULL
);
ALTER TABLE MQ
ADD PRIMARY KEY
(
	MessageId,
	Recipient
);

[11 Aug 2005 20:22] Jorge del Conde

Hi!

The problem is your default charset is set to UTF8 so

342 * 3 (3 bytes for utf8) > 1000

For example:

mysql> create table f (a varchar(340) not null primary key);
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

mysql> show create table f;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| f     | CREATE TABLE `f` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

but if I set the default charset as Latin1, then we get the following:

mysql> create table x (a varchar(340) not null primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table x;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------+
| x     | CREATE TABLE `x` (
  `a` varchar(340) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Please ensure your take into account how many bytes each charset uses.