[FIXED] How to deal with MySQL Error: 1071 during migration from Oracle to MySQL?

Issue

Our project needs to support both Oracle and MySQL. So we have a table as follows(simplified version):

Table name: FILE_METADATA
Columns: ID VARCHAR(36), FILE_PATH VARCHAR(2000)

And we have an index on this table in Oracle with both columns as follows:

CREATE INDEX IDX_NEXT_FILE_METADATA ON dbuser.FILE_METADATA(ID, FILE_PATH)

We are using the default character set for MySQL DB. I know the cause of the error is the column FILE_PATH which is having size 2000 but this is how it is supposed to be. How can we create such an index in MySQL so that both of our DBs(Oracle and MySQL) become the same or is there any other solution to it? as we cannot change the index in Oracle.

PS: Oracle version = 12c and MySQL version = 8.0.20

Solution

Option 1; reduce the amount of FILE_PATH that's in the index.

by default

CREATE INDEX IDX_NEXT_FILE_METADATA ON FILE_METADATA(ID, FILE_PATH)
Specified key was too long; max key length is 3072 bytes

This allows 768 characters. Remove 36 bytes for the ID and 732 is workable:

CREATE INDEX IDX_NEXT_FILE_METADATA ON FILE_METADATA(ID, FILE_PATH(732))

with utf8mb4, there are 4 bytes per character

ref fiddle

Answered By - danblack

Answer Checked By - Robin (PHPFixing Admin)