#1071 - Specified key was too long; max key length

Question

When I executed the following command:

ALTER TABLE `mytable` ADD UNIQUE (
`column1` ,
`column2`
);

I got this error message:

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

Information about column1 and column2:

column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci

I think varchar(20) only requires 21 bytes while varchar(500) only requires 501 bytes. So the total bytes are 522, less than 767. So why did I get the error message?

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

Answer 1

If you're creating something like:

CREATE TABLE IF NOT EXISTS your_table (
  id int(7) UNSIGNED NOT NULL AUTO_INCREMENT,
  name varchar(256) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY name (name)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ROW_FORMAT=FIXED;

it should be something like

CREATE TABLE IF NOT EXISTS your_table (
      id int(7) UNSIGNED NOT NULL AUTO_INCREMENT,
      name varchar(256) COLLATE utf8mb4_bin NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ROW_FORMAT=FIXED;

but you need to check uniqueness of that column from code or adding a new column as an MD5 or SHA1 of the varchar column

Answer 2

run this query before your query:

SET @@global.innodb_large_prefix = 1;

this will increase limit to 3072 bytes.

Answer 3

In my case, I had this problem when I was backing up a database using the linux redirection output/input characters. Therefore, I change the syntax as described below. PS: using a linux or mac terminal.

Backup (without the > redirect)

# mysqldump -u root -p databasename -r bkp.sql

Restore (without the < redirect )

# mysql -u root -p --default-character-set=utf8 databasename
mysql> SET names 'utf8'
mysql> SOURCE bkp.sql

The error "Specified key was too long; max key length is 767 bytes" simple disappeared.

Answer 4

If anyone is having issues with INNODB / Utf-8 trying to put an UNIQUE index on a VARCHAR(256) field, switch it to VARCHAR(255). It seems 255 is the limitation.

Answer 5

In case that you run Laravel (laravel now defaults to 4 byte Unicode which causes this) you can solve this by changing next lines in config/database.php from

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

 to

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

Answer 6

For me, the issue of "#1071 - Specified key was too long; max key length is 767 bytes" got resolved after changing the primarykey / uniquekey combination by limiting the column size by 200.

ALTER TABLE `mytable` ADD UNIQUE (
`column1` (200) ,
`column2` (200)
);