Specified key was too long - max key length is 767 bytes

Question

When I executed the following command:

create table assessment (
    id integer not null auto_increment unique,
    assignment_weight_type tinyint not null,
    description varchar(255),
    end_date datetime not null,
    from_grade tinyint not null,
    f_id tinyint not null,
    name varchar(255) not null,
    start_date datetime not null,
    status tinyint not null,
    weight smallint,
    school_id integer not null,
    school_year_id integer not null,
    s_id integer, primary key (id),
    unique (name, school_id, school_year_id, from_grade, f_id)
) ENGINE=InnoDB;

I got this error message:

Specified key was too long; max key length is 767 bytes

I am using a charset of utf8mb4. So why did I get the error message?

possible duplicate of [#1071 - Specified key was too long; max key length is 767 bytes](http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) — Paul Bellora, May 25 '12 at 03:39

score 6 · Accepted Answer · answered May 25 '12 at 03:39

6

utf8mb4 uses up to four bytes per character, so the name could occupy as much as 1020 bytes all by itself.

answered May 25 '12 at 03:39

Sergey Kalinichenko

714,442
84
1,110
1,523

2

+1 beat me to it. If you want a field to be 255 bytes long, and not 255 characters, use a `varbinary` field. – Niet the Dark Absol May 25 '12 at 03:40

score 2 · Answer 2 · answered Dec 07 '17 at 20:24

2

I was also getting same issue,i SET value for these variable in mysql as below and its fixed the issue.

1.global.innodb_large_prefix = 1

2.innodb_file_format=Barracuda

3.innodb_file_format_max=Barracuda

answered Dec 07 '17 at 20:24

Techie

21
2

score -1 · Answer 3 · answered May 23 '18 at 08:34

-1

Use this command to create the database.

 create database <DATABASE_NAME> character set latin1;

answered May 23 '18 at 08:34

senthalan

1,936
3
10
19

1

MySQL originally used the latin1 character set by default, which stored characters in a 2-byte sequence. If we use this, we can get more length to save the key length. – senthalan May 23 '18 at 09:12
Yeah. But the OP asked about columns containing UTF8 characters. And I don't think that he want to skip that requirement **six years later** – Nico Haase May 23 '18 at 09:12
1

I also faced this issue and I solved using this. So i thought it will be helpful for this also. – senthalan May 23 '18 at 09:19
If you don't want to use UTF8, it's fine. But that does not match the asked use case – Nico Haase May 23 '18 at 09:24

Specified key was too long - max key length is 767 bytes

3 Answers3