Help Center/ Data Replication Service/ FAQs/ Real-Time Migration/ What Do I Do If the Maximum Index Length Has Been Reached During Migration from Oracle to MySQL?
Updated on 2022-09-21 GMT+08:00
View PDF

What Do I Do If the Maximum Index Length Has Been Reached During Migration from Oracle to MySQL?

Index Length

The maximum length of each MySQL index is limited, which depends on the type of DB engine and character set. For example, each UTF8 character set contains a maximum of three bytes, and each UTF8MB4 character set contains a maximum of 4 bytes.
  • For a column index, the length of each column cannot exceed the value given in Max. Characters of a Column Index in Table 1. For example, in MySQL 5.7.6 that uses the InnoDB storage engine, the length of a column index cannot exceed 767 bytes (Max. Characters of a Column Index = 767 / Max. Bytes).
  • For a multiple-column index, the length of each column cannot exceed the value given in Max. Characters of a Column Index in Table 1, and the total length of columns cannot exceed the value given in Max. Characters of a Multiple-Column Index. For example, in MySQL 5.7.6 that uses the InnoDB storage engine, each column index length cannot exceed 767 bytes (Max. Characters of a Column Index = 767 / Max. Bytes), and the total column index length cannot exceed 3072 bytes (Max. Characters of a Column Index = 3072 / Max. Bytes)
Table 1 Index length description

Storage Engine

MySQL Version

Character Set

Max. Bytes

Max. Characters of a Column Index

Max. Characters of a Multiple-Column Index

InnoDB

MySQL 5.7.6 or earlier

UTF8MB4

4

191

768

MySQL 5.7.7 or later

UTF8MB4

4

768

768

Solution

  • Method 1

    Do not migrate tables that contain indexes of which the maximum length has been reached.

  • Method 2

    Changes to index length may cause data consistency problems. Exercise caution when performing this operation. For example, if the destination database version is MySQL 5.7.6 or earlier and uses UTF8MB4 character set, run the following command to change the index length:

    alter table tablename modify columnname varchar2 (768) ;

Replace tablename with the actual table name and columnname with the actual column name.

  • Method 3

    Delete the index and its constraints from the source database. For example, if the destination database version is MySQL 5.7.6 or earlier and uses UTF8MB4 character set, run the following commands to delete the index and its constraints:

    drop index indexname;
alter table tablename drop constraint constraintname;

    Replace indexname with the actual index name, tablename with the actual table name, and constraintname with the actual constraint name.

Parent topic: Real-Time Migration