MySQL Bugs: #32915: InnodB: max key length is no longer long enough

Bug #32915	InnodB: max key length is no longer long enough
Submitted:	2 Dec 2007 22:18	Modified:	9 Oct 2012 21:18
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	6.0.4-alpha-debug, 5.5	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any
Tags:	v6

Description:
The InnodB maximum key length is 767 bytes.

In MySQL 6.0, one utf8 or utf16 or utf32 character
may require 4 bytes. Therefore, creating an index
on a 192-character column causes a warning.

How to repeat:
mysql> create table tj (s1 varchar(192) character set utf8, key(s1))
engine=innodb;
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

Thank you for the bug report. Verified as described:

c:\dev>6.0\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-nt Source distribution

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

mysql> create table tj (s1 varchar(192) character set utf8, key(s1))
    -> engine=innodb;
Query OK, 0 rows affected, 1 warning (0.19 sec)

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

mysql>

Marko is the UTF-8 expert.

This bug has probably already been fixed in the source tree that fixes Bug #27646, or very minor adjustments may be needed. We should shortly ship an InnoDB 6.0 snapshot, once it has passed our own quality assurance testing.

Based on IRC discussion with Timothy, fix won't be available to push until 6.0.5; updated Target Version

Change it to feature request. This affects only new utf8 type (4 bytes). Users can use 3 bytes utf8mb3 as an alternative.

(Below notes slightly edited by Lars)

On Wed, May 28, 2008 at 07:56:34AM -0600, Peter Gulutzan wrote:
> mysql> select version();
> +-------------------+
> | version()         |
> +-------------------+
> | 6.0.6-alpha-debug |
> +-------------------+
> 1 row in set (0.00 sec)
>
> mysql> create table t (s1 char(194) character set utf8) engine=innodb;
> Query OK, 0 rows affected (0.10 sec)
>
> mysql> create index i on t (s1);
> Query OK, 0 rows affected, 3 warnings (0.24 sec)
> Records: 0  Duplicates: 0  Warnings: 0
>
> 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 |
> | Warning | 1071 | Specified key was too long; max key length is 767
> bytes |
> +---------+------+---------------------------------------------------------+
> 3 rows in set (0.00 sec)

On Wed, May 28, 2008 at 08:26:05PM +0300, Sinisa Milivojevic wrote:
> This is something brand new. It worked in 5.0 just fine.

On Wed, May 28, 2008 at 05:34:23PM +0000, Hakan Kuecuekyilmaz wrote:
> This never worked for me. I had to patch 5.0 and later 5.1 to get SAP
> working with InnoDB. Sometime in 6.0 the patching got non-trivial and I
> gave up.

On Thu, May 29, 2008 at 06:13:04PM +0300, Sinisa Milivojevic wrote:
> Back in 4.1 or 5.0 , I have fixed things so that InnoDB was able to
> accept indices up to 3000 bytes long. I have also made a separate
> .test and .result files, but those don't exist anymore.
>
> I remember clearly that most important thing that needed change was
> one of our include files. But, I forgot all of this.

On Thu, May 29, 2008 at 04:10:39PM +0000, Hakan Kuecuekyilmaz wrote:
> As Sinisa already pointed out, it *was* an one liner. In the current
> code base I was not able to patch InnoDB anymore.
>
> I changed
>
> include/dict0mem.h
> #define DICT_MAX_INDEX_COL_LEN  768
>
> to
>
> #define DICT_MAX_INDEX_COL_LEN  1024
>
> But now in
>
> dict/dict0dict.c
> #if DICT_MAX_INDEX_COL_LEN != 768
>          /* The comparison limit above must be constant.  If it were
>          changed, the disk format of some fixed-length columns would
>          change, which would be a disaster. */
> # error "DICT_MAX_INDEX_COL_LEN != 768"
> #endif
>
> I need index size of 1024. How can I achieve that?

Bug#50437 Innodb: Maximum key size limit for 4-byte utf8 is reduced from 255 to 191 chars
is a duplicate of this.

From Bug#50437 comments:

So some dumps created in a pre-WL#1213 version will fail to load.

How to repeat:
This can currently be demonstrated in mysql-6.1-glob tree,
but WL#1213 will also be in 5.5 (Celosia) soon:

In mysql-6.1-glob I get the following:

-- varchar(191) works fine
mysql> drop table if exists t1; create table t1 (a varchar(191) primary key)
engine=innodb character set utf8; show create table t1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

+-------+-----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                   
                          |
+-------+-----------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(191) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- varchar(192) or longer fails
mysql> drop table if exists t1; create table t1 (a varchar(192) primary key)
engine=innodb character set utf8; show create table t1;
Query OK, 0 rows affected (0.00 sec)

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> 

Suggested fix:
Fix this definition in include/rem0types.h:

#define REC_MAX_INDEX_COL_LEN	768

to

#define REC_MAX_INDEX_COL_LEN	1024

to make the above CREATE statement work up to varchar(255).

There has been a change in plan.

In the upcoming MySQL version, the original plan
was that old (maximum 3 byte) UTF8 would be
renamed UTF8MB3, and UTF8 would be maximum 4 byte.

The changed plan is that old (maximum 3 byte) UTF8
will remain the same, and there will be a new
(maximum 4 byte) character set named UTF8MB4.

What shall we do about this one? 

The REDUNDANT and COMPACT ROW_FORMAT always store 768 bytes of prefix in the clustered index record and can't support longer column prefix indexes. That 768-byte prefix length is pretty much hard-coded in the format and can't be changed without breaking file compatibility.

The DYNAMIC and COMPRESSED ROW_FORMAT of the InnoDB Plugin and later can support longer keys (the page size being the only limit).

So for Drizzle what we're doing is just bumping it up to 1024 as we don't have on-disk compatibility to maintain. At least from reading through everything, going to 1024 should be safe (apart from the on disk changes, which aren't a problem for us).

For the newer table formats, it would be nice if we could go up to page size...

then it'd be seamless to switch from latin1 to 4 byte utf8 no matter what crazy indexes you had.

Drupal (for example) had this fairly recently (August 2009): http://drupal.org/node/554820

going above 1024 will need dict_field_t to be changed, and code using it to be audited.

Although until MySQL regains 4 byte utf8... it doesn't really matter :)

I'm tracking the drizzle change on bug https://bugs.launchpad.net/drizzle/dexter/+bug/578842

Hi Marko,

Are there any news about longer keys in  DYNAMIC and COMPRESSED ROW_FORMAT?
It would be nice to have this feature.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This probably shouldn't be closed as No Feedback and instead still be waiting on some feedback.

Re-open it just for tracking the max key length when DYNAMIC or COMPRESSED ROW_FORMAT is used. Due to backward compatibility requirement, we can not change the max key length for old row formats.

There is one limit that is relevant when using the DYNAMIC and COMPRESSED file formats: the undo log records needed for multi-versioning and rollback.

When a prefix index is defined on an externally stored column and the record is updated, a 768-byte prefix and the 20-byte BLOB pointer will be written to the undo log. See trx_undo_page_fetch_ext() and trx_undo_page_report_modify(). We could raise this 768-byte limit somewhat, but an undo log record must fit in the payload area of a single 16384-byte undo page.

In which MySQL version should this be implemented?

So now this bug affects 4-byte utf -utf8mb4, utf16, utf32 in 5.5, where max key length gets restricted to 191 chars.
Also not to forget mentioning, in case of primary key columns throws error (than warning)
create table t1 (a varchar(250) character set utf16 primary key);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Updating the version field as it is not related to debug version, but present in all versions.

We cannot fix this bug for REDUNDANT and COMPACT tables. There, the 768-byte prefix is pretty much set in stone.

For updates of DYNAMIC and COMPRESSED tables, we currently store 768-byte column prefixes of the ‘before’ and ‘after’ images into the undo log. I will have to review the code and see if we can vary the prefix length there.

The current InnoDB undo log format does not allow undo log records to span multiple 16K pages. If many prefix-indexed columns are updated at a time and if we removed the 768-byte limit, we would be hitting this restriction easily.

Fixing the limitations of undo logging would still not fix a fundamental limit: at least two records must fit in an uncompressed secondary index page (16K). This limits the length of a secondary index record to some max_length=8,000 bytes. If your primary key is short (say, a 4-byte integer), you could define a column prefix index of max_length-4 bytes (or two prefixes whose combined length is max_length-4).

Hi Marko,

Any idea when this might be fixed? We'd like to add utf8mb4 support to WordPress (http://core.trac.wordpress.org/ticket/21212), but need this bug fixed before we can.

It was already implemented in MySQL 5.5.14 when we introduced the innodb_large_prefix setting http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix :

"Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes), for InnoDB tables that use the DYNAMIC  and COMPRESSED  row formats. (Creating such tables also requires the option values innodb_file_format=barracuda  and innodb_file_per_table=true.)"

James Day, MySQL Senior Principal Support Engineer, Oracle.

In the Wordpress bug report it's also worth noting that Ryan quoted linked to an old documentation version with the URL http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html to support the view that this is not implemented. That old version correctly says that in 5.1:

"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”."

If he'd instead looked at a more recent version, say 5.5 at http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html he'd have seen:

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

Closing with no changelog entry because innodb_large_prefix is already in place and documented for some time now.