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

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

User · Answer

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.

User · Answer

Index Lengths  amp  MySQL   MariaDB    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     use Illuminate Support Facades Schema          Bootstrap any application services         return void     public function boot         Schema  defaultStringLength 191       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   Reference from blog   https   www scratchcode io specified-key-too-long-error-in-laravel   Reference from Official laravel documentation   https   laravel com docs 5 7 migrations

User · Answer

If you have changed innodb log file size recently  try to restore the previous value which worked

User · Answer

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

User · Answer

For laravel 5 7 to 6 0  Steps to followed   Go to App Providers AppServiceProvider php  Add this to provider   use Illuminate Support Facades Schema  in top  Inside the Boot function Add this Schema  defaultStringLength 191     that all  Enjoy

User · Answer

I found this query useful in detecting which columns had an index violating the max length   SELECT   c TABLE NAME As TableName    c COLUMN NAME AS ColumnName    c DATA TYPE AS DataType    c CHARACTER MAXIMUM LENGTH AS ColumnLength    s INDEX NAME AS IndexName FROM information schema COLUMNS AS c INNER JOIN information schema statistics AS s   ON s table name   c TABLE NAME  AND s COLUMN NAME   c COLUMN NAME  WHERE c TABLE SCHEMA   DATABASE     AND c CHARACTER MAXIMUM LENGTH  gt  191    AND c DATA TYPE IN   char    varchar    text

User · Answer

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  lt  redirect       mysql -u root -p --default-character-set utf8 databasename mysql gt  SET names  utf8  mysql gt  SOURCE bkp sql   The error  Specified key was too long  max key length is 767 bytes  simple disappeared

User · Answer

When you hit the limit  Set the following    INNODB utf8 VARCHAR 255  INNODB utf8mb4 VARCHAR 191

User · Answer

What character encoding are you using  Some character sets  like UTF-16  et cetera  use more than one byte per character

User · Answer

767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5 6  and prior versions   It s 1 000 bytes long for MyISAM tables  In MySQL version 5 7 and upwards this limit has been increased to 3072 bytes   You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded  you have to divide the max index prefix length of 767 bytes  or 3072 bytes  by 4 resulting in 191  This is because the maximum length of a utf8mb4 character is four bytes  For a utf8 character it would be three bytes resulting in max index prefix length of 254   One option you have is to just place lower limit on your VARCHAR fields   Another option  according to the response to this issue  is to get the subset of the column rather than the entire amount  i e    ALTER TABLE  mytable  ADD UNIQUE   column1 15   column2 200       Tweak as you need to get the key to apply  but I wonder if it would be worth it to review your data model regarding this entity to see if there s improvements that would allow you to implement the intended business rules without hitting the MySQL limitation

User · Answer

I fixed this issue with   varchar 200    replaced with varchar 191   all the unique or primary varchar keys which have more than 200 replace them with 191 or set them as text

User · Answer

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

User · Answer

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    Open MySQL client  or MariaDB client   It is a command line tool  It will ask your password  enter your correct password  Select your database by using this command use my database name        Database changed    set global innodb large prefix on       Query OK  0 rows affected  0 00 sec     set global innodb file format Barracuda       Query OK  0 rows affected  0 02 sec     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  Go to table s structure tab   Click on Unique button  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

User · Answer

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

User · Answer

Due to prefix limitations this error will occur  767 bytes is the stated prefix limitation for InnoDB tables in MySQL versions before 5 7   It s 1 000 bytes long for MyISAM tables  In MySQL version 5 7 and upwards this limit has been increased to 3072 bytes   Running the following on the service giving you the error should resolve your issue  This has to be run in the MYSQL CLI   SET GLOBAL innodb file format Barracuda  SET GLOBAL innodb file per table on  SET GLOBAL innodb large prefix on

User · Answer

I have changes from varchar to nvarchar  works for me

User · Answer

Specified key was too long  max key length is 767 bytes   You got that message because 1 byte equals 1 character only if you use the latin-1 character set  If you use utf8  each character will be considered 3 bytes when defining your key column  If you use utf8mb4  each character will be considered to be 4 bytes when defining your key column  Thus  you need to multiply your key field s character limit by  1  3  or 4  in my example  to determine the number of bytes the key field is trying to allow  If you are using uft8mb4  you can only define 191 characters for a native  InnoDB  primary key field  Just don t breach 767 bytes

User · Answer

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

User · Answer

I did some search on this topic finally got some custom change  For MySQL workbench 6 3 7 Version Graphical inter phase is available   Start Workbench and select the connection  Go to management or Instance and select Options File  If Workbench ask you permission to read configuration file and then allow it by pressing OK two times  At center place Administrator options file window comes  Go To InnoDB tab and check the innodb large prefix if it not checked in the General section  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   Start CMD as administrator  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   Now Run command mysql -u userName -p databasescheema Now it asked for password of respective user  Provide password and enter into mysql prompt  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  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  use following command alter table table name ROW FORMAT DYNAMIC  Done

User · Answer

you could add an column of the md5 of long columns

User · Answer

run this query before your query   SET   global innodb large prefix   1    this will increase limit to 3072 bytes

User · Answer

change your collation  You can use utf8 general ci that supports almost all

User · Answer

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

User · Answer

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    UTF8 requires 3 bytes per character to store the string  so in your case 20   500 characters   20 3 500 3   1560 bytes which is more than allowed 767 bytes   The limit for UTF8 is 767 3   255 characters  for UTF8mb4 which uses 4 bytes per character it is 767 4   191 characters     There are two solutions to this problem if you need to use longer column than the limit    Use  cheaper  encoding  the one that requires less bytes per character  In my case  I needed to add Unique index on column containing SEO string of article  as I use only  A-z0-9 -  characters for SEO  I used latin1 general ci which uses only one byte per character and so column can have 767 bytes length   Create hash from your column and use unique index only on that The other option for me was to create another column which would store hash of SEO  this column would have UNIQUE key to ensure SEO values are unique  I would also add KEY index to original SEO column to speed up look up

User · Answer

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

User · Answer

Replace utf8mb4 with utf8 in your import file

User · Answer

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

User · Answer

To fix that  this works for me like a charm   ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8 general ci

User · Answer

My fix to this very same issue was to add an option as a 3rd argument  charset  queryInterface createTable    tableName            columns          charset   utf8         Otherwise sequelize would create tables as utf8mb4

User · Answer

This solved my issue ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8 general ci

User · Answer

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

User · Answer

Change CHARSET of the complaining index field to  latin1  i e  ALTER TABLE tbl CHANGE myfield myfield varchar 600  CHARACTER SET latin1 DEFAULT NULL  latin1 takes one byte for one character instead of four

User · Answer

We encountered this issue when trying to add a UNIQUE index to a VARCHAR 255  field using utf8mb4  While the problem is outlined well here already  I wanted to add some practical advice for how we figured this out and solved it   When using utf8mb4  characters count as 4 bytes  whereas under utf8  they could as 3 bytes  InnoDB databases have a limit that indexes can only contain 767 bytes  So when using utf8  you can store 255 characters  767 3   255   but using utf8mb4  you can only store 191 characters  767 4   191    You re absolutely able to add regular indexes for VARCHAR 255  fields using utf8mb4  but what happens is the index size is truncated at 191 characters automatically - like unique key here     This is fine  because regular indexes are just used to help MySQL search through your data more quickly  The whole field doesn t need to be indexed   So  why does MySQL truncate the index automatically for regular indexes  but throw an explicit error when trying to do it for unique indexes  Well  for MySQL to be able to figure out if the value being inserted or updated already exists  it needs to actually index the whole value and not just part of it   At the end of the day  if you want to have a unique index on a field  the entire contents of the field must fit into the index  For utf8mb4  this means reducing your VARCHAR field lengths to 191 characters or less  If you don t need utf8mb4 for that table or field  you can drop it back to utf8 and be able to keep your 255 length fields

User · Answer

5 workarounds   The limit was raised in 5 7 7  MariaDB 10 2 2    And it can be increased with some work in 5 6  10 1    If you are hitting the limit because of trying to use CHARACTER SET utf8mb4  Then do one of the following  each has a drawback  to avoid the error      Upgrade to 5 7 7 for 3072 byte limit -- your cloud may not provide this     Change 255 to 191 on the VARCHAR -- you lose any values longer than 191 characters  unlikely       ALTER    CONVERT TO utf8 -- you lose Emoji and some of Chinese     Use a  prefix  index -- you lose some of the performance benefits     Or    Stay with older version but perform 4 steps to raise the limit to 3072 bytes   SET GLOBAL innodb file format Barracuda  SET GLOBAL innodb file per table 1  SET GLOBAL innodb large prefix 1  logout  amp  login  to get the global values   ALTER TABLE tbl ROW FORMAT DYNAMIC   --  or COMPRESSED    -- http   mysql rjweb org doc php limits 767 limit in innodb indexes

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

Examples related to mysql

Examples related to byte

Examples related to varchar

Examples related to mysql-error-1071