Bug #23069 Too long UNIQUE indices allowed in InnoDB

• Submitted: 7 Oct 2006 10:46
• Modified: 6 Dec 2006 14:31
• Reporter: Oli Sennhauser
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0.18, 5.0.24, 5.0.27-BK
• OS: Linux (Linux)
• CPU Architecture: Any

[7 Oct 2006 10:46] Oli Sennhauser

Description:
Creation of an UNIQUE Index on VARCHAR(255), INT (utf8) is allowed but should IMHO  not!

How to repeat:
DROP TABLE test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `data` varchar(260) default NULL,
  PRIMARY KEY  (`id`, data)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--------------------

DROP TABLE test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `data` varchar(255) default NULL,
  PRIMARY KEY  (id, data)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK
--> incorrect

--------------------

DROP TABLE test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `data` varchar(255) default NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE test ADD UNIQUE INDEX data_ui (data);

--> correct

--------------------

ALTER TABLE test ADD UNIQUE INDEX data_id_ui (data, id);
ALTER TABLE test ADD UNIQUE INDEX id_data_ui (id, data);

255 x 3 + 4 = 769

--> incorrect

--------------------

Suggested fix:
Enlarge range to more than 765 bytes (e.g. 800 = 3x255+4+4) . This is also not correctly and properly documented. It is written 767 bytes?

[7 Oct 2006 11:59] Valeriy Kravchuk

Thank you for a bug report. Verified just as described with 5.0.27-BK on Linux. 

mysql> CREATE TABLE `test` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `data` varchar(255) default NULL,
    ->   PRIMARY KEY  (id, data)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into test values(1, rpad('a', 255, 'a'));
Query OK, 1 row affected (0.01 sec)

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

mysql> CREATE TABLE `test` (   `id` int(11) NOT NULL auto_increment,   `data` v
archar(256) default NULL,   PRIMARY KEY  (id, data) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 765 bytes

The reason may be default COMPACT format for InnoDB rows (http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html), where NULL columns are not stored, but as ID is declared as NOT NULL, it is still a bug (or, at least, requires proper documentation).

[9 Oct 2006 13:58] Heikki Tuuri

Oli,

InnoDB does allow keys up to 3500 bytes in length. But the MySQL interpreter restricts the maximum key length.

The question is why MySQL does allow a unique key longer than a primary key.

I am reclassifying this as a Server bug.

Regards,

Heikki

ha_innodb.h:

        uint max_supported_key_length() const { return 3500; }
        uint max_supported_key_part_length() const;

##################################

dict0mem.h in 5.0:

/* DICT_MAX_INDEX_COL_LEN is measured in bytes and is the max index column
length + 1. Starting from 4.1.6, we set it to < 3 * 256, so that one can
create a column prefix index on 255 characters of a TEXT field also in the
UTF-8 charset. In that charset, a character may take at most 3 bytes. */

#define DICT_MAX_INDEX_COL_LEN          768

########################

ha_innodb.cc in 5.0:

uint
ha_innobase::max_supported_key_part_length() const
{
        return(DICT_MAX_INDEX_COL_LEN - 1);
}