Description:
The storage engine API method handler::max_supported_key_part_length() needs some cleanup.
In InnoDB, the maximum supported column prefix length in indexes depends on a number of things:
* The actual ROW_FORMAT of the table that is part of the DDL operation
* The page size of the tablespace (innodb_page_size)
* The setting of innodb_large_prefix.
Currently, depending on the innodb_large_prefix setting, InnoDB is returning 3072 or 767.
The SQL layer will treat the return value as a hard limit.
In MySQL 5.7, SQL_MODE=STRICT_TRANS_TABLES by default. So, by default, the SQL layer would return an error if an attempt is made to create a too long column prefix index. In a non-strict SQL_MODE, this would result in a warning, or an error if the too-long prefix is part of a UNIQUE index or the PRIMARY KEY.
If InnoDB leads the SQL layer to believe that it can support prefixes up to 3072 bytes, and the SQL layer tries to create a table that has a longer prefix that InnoDB actually supports for the chosen ROW_FORMAT and page size, then InnoDB will flag a hard error.
How to repeat:
Look at tests that depend on innodb_large_prefix=OFF
such as the test in main.alter_table for
--echo # BUG#16886196 - ALTER TABLE FAILS TO CONVERT TO PREFIX INDEX IN
--echo # ALTER_COLUMN_EQUAL_PACK_LENGTH
Try to run such tests with innodb_large_prefix=ON.
Suggested fix:
Change the handler::max_supported_key_part_length() API in such a way that InnoDB can return the actual maximum length. This would seem to require at least the ROW_FORMAT as a parameter. We might as well pass all the information that would be passed to create() or prepare_inplace_alter_table().
The method could also require extra information about the tablespace where the table is (going to be) located, in case InnoDB starts to support different page sizes within a single instance. With innodb_page_size=4k, the limit is somewhere between 767 and 3072 bytes.
