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

[16 Nov 2005 16:59] Marc Batchelor
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)
[16 Nov 2005 17:17] MySQL Verification Team
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)
[16 Nov 2005 18:04] Marc Batchelor
The my.ini

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

[16 Nov 2005 19:58] Marc Batchelor
I've attached the my.ini. Thanks for looking at this.
[30 Mar 2010 11:24] Alok Saldanha
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;
[30 Mar 2010 12:06] MySQL Verification Team
See http://bugs.mysql.com/bug.php?id=4541.