All Products
Search

This Product

    :"Incorrect prefix key; the used key part isn't a string, the used length is longer than..." is reported when an SQL statement is executed in DMS

    Document Center

    :"Incorrect prefix key; the used key part isn't a string, the used length is longer than..." is reported when an SQL statement is executed in DMS

    Last Updated:Dec 08, 2020

    Problem description

    When you create or change Index Prefixes for MySQL instances that use the InnoDB engine, the following error message appears.

    Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

    Possible cause

    The system displays the following error messages when you create or change a prefix index:

    • Reason one
      The Target Field is not of type VARCHAR (character). A prefix index only supports VARCHAR types. This error occurs if the entered fields are not of the VARCHAR type, such as the INT or TIME type.
    • Reason Two
      The length of the prefix index exceeds the length of the field itself. For example, if you define the column_a field as 128 characters, that is, column_a varchar(128). If you set the prefix index to 130 characters in length, that is, the key idx_a(column_a130). then, an error is returned. Prefix indexes have smaller lengths than fields themselves.
      Note: For more information about the VARCHAR type length, see more information.

    Solution

    Make sure that the following requirements are met before you create or modify an index prefix:

    • The Target Field is of type VARCHAR.
    • The prefix index length is smaller than the length of the field itself.

    If you are not sure about the type or length of the Target Field, you can use desc or other SQL statements to query the Target Field.

    References

    The following table describes the Chinese characters, digits, and letters that a VARCHAR type field can store related to the MySQL version.

    • MySQL 5.0 and earlier versions
      In varcharx, the unit of X is a byte. For example, varchar(100) is 100 bytes. If varchar(100) stores UTF-8 characters, it can only store 33 Chinese characters. In the UTF-8 encoding, each Chinese character occupies 3 bytes.
    • MySQL 5.0 and later versions
      In varchar(X), the unit of X is a character. For example, varchar(100) indicates 100 characters, which can be 100 characters in length.

    Documentation

    For more information about prefix indexes, see the official MySQL documentation.

    Application scope

    • Data Management