#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

Based on the column given below, those 2 variable string columns are using utf8_general_ci collation (utf8 charset is implied).

In MySQL, utf8 charset uses a maximum of 3 bytes for each character. Thus, it would need to allocate 500*3=1500 bytes, which is much greater than the 767 bytes MySQL allows. That's why you are getting this 1071 error.

In other words, you need to calculate the character count based on the charset's byte representation as not every charset is a single byte representation (as you presumed.) I.E. utf8 in MySQL is uses at most 3-byte per character, 767/3≈255 characters, and for utf8mb4, an at most 4-byte representation, 767/4≈191 characters.

It's also known that MySQL

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

Answer 2

If you have changed innodb_log_file_size recently, try to restore the previous value which worked.

Answer 3

MySQL assumes worst case for the number of bytes per character in the string. For the MySQL 'utf8' encoding, that's 3 bytes per character since that encoding doesn't allow characters beyond U+FFFF. For the MySQL 'utf8mb4' encoding, it's 4 bytes per character, since that's what MySQL calls actual UTF-8.

So assuming you're using 'utf8', your first column will take 60 bytes of the index, and your second another 1500.

Answer 4

For laravel 5.7 or 5.6

Steps to followed

1. Go to App\Providers\AppServiceProvider.php.
2. Add this to provider use Illuminate\Support\Facades\Schema; in top.
3. Inside the Boot function Add this Schema::defaultStringLength(191);

that all, Enjoy.

Answer 5

Solution For Laravel Framework

As per Laravel 5.4.* documentation; You have to set the default string length inside the boot method of the app/Providers/AppServiceProvider.php file as follows:

use Illuminate\Support\Facades\Schema;

public function boot() 
{
    Schema::defaultStringLength(191); 
}

Explanation of this fix, given by Laravel 5.4.* documentation:

Laravel uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLength method within your AppServiceProvider.

Alternatively, you may enable the innodb_large_prefix option for your database. Refer to your database's documentation for instructions on how to properly enable this option.

Answer 6

Please check if sql_mode is like

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

if it is, change to

sql_mode=NO_ENGINE_SUBSTITUTION

OR

restart your server changing your my.cnf file (putting following)

innodb_large_prefix=on