Skip to content
  • Jon Olav Hauglid's avatar
    89041ce5
    Bug#22740093: ERROR 1071 (42000): SPECIFIED KEY WAS TOO LONG · 89041ce5
    Jon Olav Hauglid authored
                  ERROR WHILE DROPPING INDEX IN 5.7
    
    This bug concerns the check of index prefix lengths for TEXT
    type columns. These types of columns has a maximum length
    measured in bytes (e.g. 255 bytes for TINYTEXT). However the
    index prefix length specified in CREATE TABLE and ALTER TABLE
    statements is in characters.
    
    This gave separate buggy behavior for CREATE TABLE and ALTER TABLE:
    
    During CREATE TABLE, the index prefix length was only checked
    against storage engine maximum sizes, not the maximum size of
    the column (Create_field::key_length is still 0 when the
    check was attempted). This e.g. meant that you could create a
    prefix index of 768 utf8mb4 characters for TINYTEXT columns
    even if the real maximum is 63 characters (255/4).
    (768 * 4 = 3072 is the InnoDB max index length).
    
    During ALTER TABLE, the check was broken for multibyte character
    sets as we multiplied the maximum size of each column with
    bytes_per_character one time too many. This e.g meant that at you
    could add an index of 252 utf8mb4 characters for TINYTEXT
    columns (63 * 4 = 252).
    
    This patch fixes the problem by comparing the index prefix length
    in bytes against the max column length in bytes. So for TINYTEXT,
    the maximum prefix is now 63 regardless of whether CREATE TABLE or
    ALTER TABLE is used to create the index (assuming a 4 byte
    character set is used).
    89041ce5
    Bug#22740093: ERROR 1071 (42000): SPECIFIED KEY WAS TOO LONG
    Jon Olav Hauglid authored
                  ERROR WHILE DROPPING INDEX IN 5.7
    
    This bug concerns the check of index prefix lengths for TEXT
    type columns. These types of columns has a maximum length
    measured in bytes (e.g. 255 bytes for TINYTEXT). However the
    index prefix length specified in CREATE TABLE and ALTER TABLE
    statements is in characters.
    
    This gave separate buggy behavior for CREATE TABLE and ALTER TABLE:
    
    During CREATE TABLE, the index prefix length was only checked
    against storage engine maximum sizes, not the maximum size of
    the column (Create_field::key_length is still 0 when the
    check was attempted). This e.g. meant that you could create a
    prefix index of 768 utf8mb4 characters for TINYTEXT columns
    even if the real maximum is 63 characters (255/4).
    (768 * 4 = 3072 is the InnoDB max index length).
    
    During ALTER TABLE, the check was broken for multibyte character
    sets as we multiplied the maximum size of each column with
    bytes_per_character one time too many. This e.g meant that at you
    could add an index of 252 utf8mb4 characters for TINYTEXT
    columns (63 * 4 = 252).
    
    This patch fixes the problem by comparing the index prefix length
    in bytes against the max column length in bytes. So for TINYTEXT,
    the maximum prefix is now 63 regardless of whether CREATE TABLE or
    ALTER TABLE is used to create the index (assuming a 4 byte
    character set is used).
Loading