6
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?

Paul Roefs
  • 368
  • 2
  • 3
  • 13
  • 1
    Your combined key is too long. Make seperate keys or reduce column lengths – juergen d Nov 17 '16 at 16:42
  • Create a new column `unique_id binary(16)`, concatenate two columns, hash them and save **RAW hash** to `unique_id`, make `unique_id` unique. Job done. – N.B. Nov 17 '16 at 19:53

2 Answers2

11

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.

CloudJake
  • 156
  • 4
  • 22
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
8

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!

Ivan Loreto
  • 91
  • 2
  • 6
  • 3
    This is the solution - gets the database engine to accept wider indexes. The accepted answer gives the reason for the problem but is not a solution if you need the composite key. These settings worked for me. `innodb_file_format=Barracuda innodb_file_per_table=on innodb_default_row_format=dynamic innodb_large_prefix=1 innodb_file_format_max=Barracuda` – Peter Jun 24 '20 at 10:12