MySQL Bugs: #72113: Manual does not explain "silent length reduction" for index prefix

Bug #72113	Manual does not explain "silent length reduction" for index prefix
Submitted:	24 Mar 2014 17:26	Modified:	18 Jun 2014 17:17
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5, 5.6	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	innodb_large_prefix

Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html) says:

"By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length for a nonunique index. For a unique index, exceeding the index prefix limit produces an error."

I do not see any example showing this "length is silently reduced" in the manual. Moreover, I can not create this example no matter how I try (see below).

How to repeat:
It's easy to see how innodb_large_prefix works in simple cases:

mysql> set global innodb_large_prefix=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_file_format='Barracuda';
Query OK, 0 rows affected (0.00 sec)

mysql> create table test1(a varchar(1024), key(a)) engine=InnoDB row_format=compact;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767
bytes.
mysql> create table test1(a varchar(1024), key(a)) engine=InnoDB row_format=dynamic;
Query OK, 0 rows affected (1.40 sec)

But where is that magic "silent reduction" for non-unique index I wonder:

mysql> create table test2(a varchar(2000), key(a)) engine=InnoDB row_format=dynamic;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create table test2(a varchar(2000), b int primary key, key(a)) engine=InnoDB row_format=dynamic;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create table test2(a varchar(2000), b int primary key, key(a(1025))) engine=InnoDB row_format=dynamic;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create table test2(a varchar(2000), b int primary key, key(a(1024),b)) engine=InnoDB row_format=dynamic;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create table test2(a varchar(2000), b int primary key, key(a,b)) engine=InnoDB row_format=dynamic;

So, non-unique keys (I've even added explicit primary key to tests), single column on multiple-column - still errors all the time and no silent length change. It just works when we are under 3072 bytes in total for the key:

mysql> create table test3(id int primary key, a varchar(512), b varchar(512), ke
y(a,b)) engine=InnoDB row_format=dynamic;
Query OK, 0 rows affected (1.00 sec)

and seems no other way:

mysql> create table test4(id int primary key, a varchar(513), b varchar(513), ke
y(a,b)) engine=InnoDB row_format=dynamic;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create table test4(id int primary key, a varchar(513), b varchar(513), ke
y(a(512),b)) engine=InnoDB row_format=dynamic;

Suggested fix:
Add example to the manual that illustrates this:

"When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length for a nonunique index. For a unique index, exceeding the index prefix limit produces an error."

or correct the manual to explain current behavior based on my examples.

Thank you for the bug report.

Index prefix "silent length reduction" is still present in MySQL 5.5 as shown in the example below. This "permissive" behaviour is no longer present in 5.6+ and an error is returned. 

The reference manual has been updated. The changes should appear soon, with the next published documentation build. 

http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html 
http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html 

Thank you for the bug report.

MySQL 5.5:

mysql> create table test1(a varchar(500)) engine=InnoDB row_format=dynamic DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE INDEX i1 ON test1 (a(256));
Query OK, 0 rows affected, 2 warnings (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `a` varchar(500) DEFAULT NULL,
  KEY `i1` (`a`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)