MySQL Bugs: #25480: VARCHAR column, of length more than 255, is not accepted as primary key

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:	Windows (WinXP)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

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;

This is related to Bug#9075.

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.

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`) ;

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)

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)

Version 5.1 is affected too

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

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

Heikki, column NAME is not in UTF8, it is BINARY:

`NAME` VARCHAR(256) BINARY NOT NULL,

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

Heikki, you are right. It is not a bug.

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.)