Bug #25480 VARCHAR column, of length more than 255, is not accepted as primary key
Submitted: 8 Jan 2007 22:07 Modified: 11 Jan 2007 13:47
Reporter: Carlos Sanchez Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.27-community-nt, 5.0, 5.1 BK OS:Microsoft Windows (WinXP)
Assigned to: Heikki Tuuri CPU Architecture:Any

[8 Jan 2007 22:07] Carlos Sanchez
Description:
VARCHAR column of more than 255 is not accepted as primary key

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

How to repeat:
All the CREATE TABLE statements succeed but last one, showing the incorrect behaviour

DROP TABLE IF EXISTS MYTABLE;

CREATE TABLE `MYTABLE`
(
    `NAME` VARCHAR(255) BINARY NOT NULL,
    PRIMARY KEY (`NAME`)
) ENGINE=INNODB;

DROP TABLE MYTABLE;

CREATE TABLE `MYTABLE`
(
    `NAME` VARCHAR(256) BINARY NOT NULL
) ENGINE=INNODB;

DROP TABLE MYTABLE;

CREATE TABLE `MYTABLE`
(
    `NAME` VARCHAR(256) BINARY NOT NULL,
    PRIMARY KEY (`NAME`)
) ENGINE=MYISAM;

DROP TABLE MYTABLE;

CREATE TABLE `MYTABLE`
(
    `NAME` VARCHAR(256) BINARY NOT NULL,
    PRIMARY KEY (`NAME`)
) ENGINE=INNODB;
[8 Jan 2007 22:09] Carlos Sanchez
This is related to Bug#9075.
[8 Jan 2007 22:25] MySQL Verification Team
Thank you for the bug report. Could you please provide the output of:

mysql> show variables like "%char%";
+--------------------------+-----------------------------------------------------------------+
| Variable_name            | Value                                                           |
+--------------------------+-----------------------------------------------------------------+
| character_set_client     | latin1                                                          |
| character_set_connection | latin1                                                          |
| character_set_database   | latin1                                                          |
| character_set_filesystem | binary                                                          |
| character_set_results    | latin1                                                          |
| character_set_server     | latin1                                                          |
| character_set_system     | utf8                                                            |
| character_sets_dir       | C:\Arquivos de programas\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+-----------------------------------------------------------------+
8 rows in set (0.01 sec)
C:\mydb\bin>mysql -uroot -P3307 db3
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.27-community-nt MySQL Community Edition (GPL)

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

mysql> DROP TABLE IF EXISTS MYTABLE;
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> CREATE TABLE `MYTABLE`
    -> (
    ->     `NAME` VARCHAR(255) BINARY NOT NULL,
    ->     PRIMARY KEY (`NAME`)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> DROP TABLE MYTABLE;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> CREATE TABLE `MYTABLE`
    -> (
    ->     `NAME` VARCHAR(256) BINARY NOT NULL
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> DROP TABLE MYTABLE;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE TABLE `MYTABLE`
    -> (
    ->     `NAME` VARCHAR(256) BINARY NOT NULL,
    ->     PRIMARY KEY (`NAME`)
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> DROP TABLE MYTABLE;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `MYTABLE`
    -> (
    ->     `NAME` VARCHAR(256) BINARY NOT NULL,
    ->     PRIMARY KEY (`NAME`)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)

mysql>

Thanks in advance.
[8 Jan 2007 22:38] Carlos Sanchez
Another issue related is with foreign keys pointing to those VARCHAR > 255 columns

The following commands fail

DROP TABLE IF EXISTS `MYTABLE`;

CREATE TABLE `MYTABLE`
(
    `NAME` VARCHAR(256) BINARY NOT NULL
) ENGINE=INNODB;

DROP TABLE IF EXISTS `MYTABLE2`;

CREATE TABLE `MYTABLE2`
(
    `NAME2` VARCHAR(10) BINARY NOT NULL
) ENGINE=INNODB;

ALTER TABLE `MYTABLE2` ADD CONSTRAINT `MYTABLE2_FK1` FOREIGN KEY (`NAME2`) REFERENCES `MYTABLE` (`NAME`) ;
[8 Jan 2007 22:45] Carlos Sanchez
Miguel, here is the output

$ mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 170 to server version: 5.0.27-community-nt

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

mysql> show variables like "%char%";
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | latin1                                                  |
| character_set_connection | latin1                                                  |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | latin1                                                  |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)
[9 Jan 2007 10:49] Sveta Smirnova
Thank you for the report.

Verified on Linux as described. MyISAM storage engine is not affected:

mysql>  show variables like "%char%";
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | latin1                                                 | 
| character_set_connection | latin1                                                 | 
| character_set_database   | utf8                                                   | 
| character_set_filesystem | binary                                                 | 
| character_set_results    | latin1                                                 | 
| character_set_server     | utf8                                                   | 
| character_set_system     | utf8                                                   | 
| character_sets_dir       | /users/ssmirnova/build/mysql-5.0/share/mysql/charsets/ | 
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> CREATE TABLE `MYTABLE`
    -> (
    ->     `NAME` VARCHAR(256) BINARY NOT NULL,
    ->     PRIMARY KEY (`NAME`)
    -> ) ENGINE=INNODB;
ERROR 1071 (42000): Specified key was too long; max key length is 765 bytes

mysql> CREATE TABLE `MYTABLE`
    -> (
    ->     `NAME` VARCHAR(256) BINARY NOT NULL,
    ->     PRIMARY KEY (`NAME`)
    -> );
Query OK, 0 rows affected (0.11 sec)
[9 Jan 2007 16:48] Sveta Smirnova
Version 5.1 is affected too
[10 Jan 2007 17:54] Heikki Tuuri
Hi!

Is this a bug? If you are using the UTF-8 charsetm then each character potentially takes 3 bytes. I think this is the intended upper limit in the current MySQL.

Regards,

Heikki
[10 Jan 2007 18:14] Carlos Sanchez
why is it working differently in MyIsam and InnoDB?
you can have a 256 size column but it can't be a primary key or be part of an index ???

If Bug#9075 was fixed this needs to be fixed for consistency
[10 Jan 2007 22:35] Sveta Smirnova
Heikki, column NAME is not in UTF8, it is BINARY:

`NAME` VARCHAR(256) BINARY NOT NULL,
[11 Jan 2007 12:54] Heikki Tuuri
Sveta,

I think the charset is UTF-8. It is just collated (alphabetically ordered) using the binary comparison method.

You could test the VARBINARY type.

Regards,

Heikki
[11 Jan 2007 13:47] Sveta Smirnova
Heikki, you are right. It is not a bug.
[11 Jan 2007 13:48] Sveta Smirnova
Link to manual page:

http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

QUOTE:

The BINARY attribute is shorthand for specifying the binary collation of the column character set. In this case, sorting and comparison are based on numeric character values. (Before MySQL 4.1, BINARY caused a column to store binary strings and sorting and comparison were based on numeric byte values. This is the same as using character values for single-byte character sets, but not for multi-byte character sets.)