innodb large_prefix deprecated- resulting key length?
Q: With innodb_large_prefix being deprecated - does the following mySQL dev statement apply to the innodb implementation of mariadb 10.3:
"If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB." * ?
Answer Answered by Marko Mäkelä in this comment.
The code was changed in MariaDB 10.2.17 and MariaDB 10.3.9 by a bug fix related to incorrectly defined length of the InnoDB persistent statistics tables. Another part of the bug MDEV-14637 was that the InnoDB FOREIGN KEY metadata tables define the length of some columns inaccurately. In its internal data modification operations, InnoDB ignores the maximum length, and this could cause InnoDB to deadlock during a page split when a too small record size is predicted.
Because of this bug, in the above mentioned fix we had to slightly relax the constraints so that the semi-internal InnoDB persistent statistics tables can be created with a sufficient size of the table_name column even when the smallest innodb_page_size=4k is being used. The function ha_innobase::max_supported_key_length() was changed to return 1173 instead of 768 bytes for innodb_page_size=4k. For innodb_page_size=8k the limit is 1536, and for larger page sizes, 3500 bytes.
The function handler::max_supported_key_length() is inaccurate, because it is geared towards heap-organized storage engines, such as MyISAM and Aria. In InnoDB, the length of a secondary index record depends not only on the secondary index key columns but also the PRIMARY KEY columns. So, this function is returning a conservative value for InnoDB, to leave some room for the PRIMARY KEY. It would be better to let the storage engine calculate and validate the record size in each index.
For the largest-size index records in InnoDB, define a PRIMARY KEY with 16 columns and a secondary key with a selection of 16 columns that are not part of the primary key.
