The Ultimate Guide to High Availability with MariaDB

Comments - innodb large_prefix deprecated- resulting key length?

5 years, 6 months ago Marko Mäkelä
Re: innodb large_prefix deprecated- resulting key length? [Answer]

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.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.