MySQL Bugs: #40093: CREATE table fails on varchar greater than 255 and unique index

Bug #40093	CREATE table fails on varchar greater than 255 and unique index
Submitted:	16 Oct 2008 22:38	Modified:	17 Oct 2008 18:18
Reporter:	Adam Nelson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux (Fedora 8)
Assigned to:		CPU Architecture:	Any
Tags:	maxlength, unique, varchar

Description:
When trying to create a table with a varchar column with a unique index that is greater than 255 in length, the statement fails.  The documentation says that as long as the max-row-length (64k) is not hit, varchar should be able to use all of it.

How to repeat:
On InnoDB:

mysql> CREATE TABLE `tester` (`tester_column` varchar(256) NOT NULL UNIQUE);
ERROR 1071 (42000): Specified key was too long; max key length is 765 bytes
mysql> CREATE TABLE `tester` (`tester_column` varchar(255) NOT NULL UNIQUE);
Query OK, 0 rows affected (0.00 sec)

On MyISAM:

mysql> CREATE TABLE `tester` (`tester_column` varchar(256) NOT NULL UNIQUE);
ERROR 1071 (42000): Specified key was too long; max key length is 999 bytes
mysql> CREATE TABLE `tester` (`tester_column` varchar(255) NOT NULL UNIQUE);
Query OK, 0 rows affected (0.00 sec)

mysql> 

Suggested fix:
Don't support varchar(256) and higher at all or allow limits more inline with the other mainstream databases.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

These limitations described in the user manual at http://dev.mysql.com/doc/refman/5.0/en/create-index.html ("Prefix lengths are storage engine-dependent (for example, a prefix can be up to 1000 bytes long for MyISAM tables, 767 bytes for InnoDB tables). ") and at http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html ("Index key prefixes can be up to 767 bytes.")

Also I can not repeat error with MyISAM and varchar(256)

Just to clarify, the workaround for this issue is to use a 1 byte character such as latin1/latin2:

mysql> drop table interest;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE interest (`l` varchar(512) NOT NULL UNIQUE ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)