MySQL Bugs: #14990: Cannot create unique index on varchar column longer than 255 characters

Bug #14990	Cannot create unique index on varchar column longer than 255 characters
Submitted:	16 Nov 2005 16:59	Modified:	16 Nov 2005 19:58
Reporter:	Marc Batchelor	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I get a series of errors trying to create a unique index on a varchar column larger than 255 characters. Depending upon the length of the column, I get either an Error 1071: Specified key was too long, or an ERROR 1005.

It doesn't matter whether I specify the index creation as part of the DDL, or whether a CREATE UNIQUE INDEX statement is issued after the table has been created.

How to repeat:
mysql> create table foo ( SOMEKEYCOL varchar(100), SOMEUNIQUECOL varchar(500), primary key(SOMEKEYCOL), unique(SOMEUNIQUECOL) ) ENGINE=InnoDB;

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

mysql> create table foo ( SOMEKEYCOL varchar(100), SOMEUNIQUECOL varchar(256), primary key(SOMEKEYCOL), unique(SOMEUNIQUECOL) ) ENGINE=InnoDB;

ERROR 1005 (HY000): Can't create table '.\db\foo.frm' (errno: 139)

mysql> create table foo ( SOMEKEYCOL varchar(100), SOMEUNIQUECOL varchar(255), primary key(SOMEKEYCOL), unique(SOMEUNIQUECOL) ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.09 sec)

I was unable to repeat this issue. can you please provide your my.ini?

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table foo ( SOMEKEYCOL varchar(100), SOMEUNIQUECOL varchar(500),
    -> primary key(SOMEKEYCOL), unique(SOMEUNIQUECOL) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.27 sec)

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `SOMEKEYCOL` varchar(100) NOT NULL default '',
  `SOMEUNIQUECOL` varchar(500) default NULL,
  PRIMARY KEY  (`SOMEKEYCOL`),
  UNIQUE KEY `SOMEUNIQUECOL` (`SOMEUNIQUECOL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

The my.ini

Attachment: my.ini (application/octet-stream, text), 9.08 KiB.

I've attached the my.ini. Thanks for looking at this.

I think this is an issue only if the charset is utf8, latin1 can go up to around varchar(767). Try changing your create table to

CREATE TABLE `foo` (
  `SOMEKEYCOL` varchar(100) NOT NULL default '',
  `SOMEUNIQUECOL` varchar(500) default NULL,
  PRIMARY KEY  (`SOMEKEYCOL`),
  UNIQUE KEY `SOMEUNIQUECOL` (`SOMEUNIQUECOL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

See http://bugs.mysql.com/bug.php?id=4541.