#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

Here is my original answer:

I just drop database and recreate like this, and the error is gone:

drop database if exists rhodes; create database rhodes default CHARACTER set utf8 default COLLATE utf8_general_ci;

However, it doesn't work for all the cases.

It is actually a problem of using indexes on VARCHAR columns with the character set utf8 (or utf8mb4), with VARCHAR columns that have more than a certain length of characters. In the case of utf8mb4, that certain length is 191.

Please refer to the Long Index section in this article for more information how to use long indexes in MySQL database: http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database-character-set-to-utf8mb4

Answer 2

you could add an column of the md5 of long columns

Answer 3

The answer about why you get error message was already answered by many users here. My answer is about how to fix and use it as it be.

Refer from this link.

1. Open MySQL client (or MariaDB client). It is a command line tool.
2. It will ask your password, enter your correct password.
3. Select your database by using this command use my_database_name;

Database changed

4. set global innodb_large_prefix=on;

Query OK, 0 rows affected (0.00 sec)

5. set global innodb_file_format=Barracuda;

Query OK, 0 rows affected (0.02 sec)

6. Go to your database on phpMyAdmin or something like that for easy management. > Select database > View table structure > Go to Operations tab. > Change ROW_FORMAT to DYNAMIC and save changes.
7. Go to table's structure tab > Click on Unique button.
8. Done. Now it should has no errors.

The problem of this fix is if you export db to another server (for example from localhost to real host) and you cannot use MySQL command line in that server. You cannot make it work there.

Answer 4

change your collation. You can use utf8_general_ci that supports almost all

Answer 5

Just changing utf8mb4 to utf8 when creating tables solved my problem. For example: CREATE TABLE ... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; to CREATE TABLE ... DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;.

Answer 6

I did some search on this topic finally got some custom change

For MySQL workbench 6.3.7 Version Graphical inter phase is available

1. Start Workbench and select the connection.
2. Go to management or Instance and select Options File.
3. If Workbench ask you permission to read configuration file and then allow it by pressing OK two times.
4. At center place Administrator options file window comes.
5. Go To InnoDB tab and check the innodb_large_prefix if it not checked in the General section.
6. set innodb_default_row_format option value to DYNAMIC.

For Versions below 6.3.7 direct options are not available so need to go with command prompt

1. Start CMD as administrator.
2. Go To director where mysql server is install Most of cases its at "C:\Program Files\MySQL\MySQL Server 5.7\bin" so command is "cd \" "cd Program Files\MySQL\MySQL Server 5.7\bin".
3. Now Run command mysql -u userName -p databasescheema Now it asked for password of respective user. Provide password and enter into mysql prompt.
4. We have to set some global settings enter the below commands one by one set global innodb_large_prefix=on; set global innodb_file_format=barracuda; set global innodb_file_per_table=true;
5. Now at the last we have to alter the ROW_FORMAT of required table by default its COMPACT we have to set it to DYNAMIC.
6. use following command alter table table_name ROW_FORMAT=DYNAMIC;
7. Done