SQL Error #1071 - Specified key was too long; max key length is 767 bytes

Question

CREATE TABLE wp_locations (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `city` VARCHAR(255) NOT NULL,
        `name` VARCHAR(255) NOT NULL,
        CONSTRAINT `city_name` UNIQUE (`city`, `name`)
    ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I got an sql error '#1071 - Specified key was too long; max key length is 767 bytes'

What am I doing wrong?

Answer 1

MySQL always reserves the max amount for a UTF8 field which is 4 bytes so with 255 + 255 with your DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; you are over the 767 max key length limit.

You can only reduce the single varchar length or don't use a composite key.

Answer 2

I had the same exact issue. I've added these lines to a new conifg file in /etc/my.conf.d directory named umb4-support.cnf

[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

After restarting the maria db service, my import scripts ran without this issue. Godspeed!