MySQL Error #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

Solution 1

767 bytes in MySQL version 5.6 (and prior versions), is the stated prefix limitation for InnoDB tables. It's 1,000 bytes long for MyISAM tables. This limit has been increased to 3072 bytes In MySQL version 5.7 (and upwards).

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 255 (or minus null-terminator, 254 characters).

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 possible, which would allow you to implement the intended business rules without hitting the MySQL limitation.

Solution 2

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.

Solution 3

When you hit the limit. Set the following.

• INNODB utf8 VARCHAR(255)
• INNODB utf8mb4 VARCHAR(191)

Solution 4

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.

Solution 5

run this query before your query:

SET @@global.innodb_large_prefix = 1;

this will increase limit to 3072 bytes.

View more solutions

Comments

[Thanatos]

If it's UTF8, a character can use up to 4 bytes, so that 20 character column is 20 * 4 + 1 bytes, and the 500 char column is 500 * 4 + 2 bytes

[Steven]

To apply by specifying a subset of the column rather than the entire amount. A good solution.

[dma_k]

@OMGPonies: Do you happen to know, if DB2/MSSQL/Oracle have the same limitation on index size? For example HSQL does not have such limitation...

[OMG Ponies]

@dma_k: No experience with DB2, but I haven't experienced the issue for SQL Server or Oracle.

[Andresch Serj]

For what it's worth, i just had the same problem and switching from utf8_general_ci to utf8_unicode_ci solved the problem for me. I do not know why though :(

[Arjan]

For a VARCHAR(256) column with a UNIQUE index, changing collation had no effect for me, like it did for @Andresch. However, reducing the length from 256 to 255 did solve it. I don't understand why, as 767 / max 4 bytes per character would yield a maximum of 191?

[Amber]

255*3 = 765; 256*3 = 768. It appears your server was asssuming 3 bytes per character, @Arjan

[Greg]

@Thanatos: utf8 uses up to 3. SELECT MAXLEN FROM information_schema.CHARACTER_SETS WHERE CHARACTER_SET_NAME = 'utf8';

[Thanatos]

@Greg: You are correct, but this should be elaborated: UTF-8 itself uses 1–4 bytes per code point. MySQL's "character sets" (really encodings) has a character set called "utf8" that is able to encode some of UTF-8, and uses 1–3 bytes per code point, and is incapable of encoding code points outside the BMP. It also includes another character set called "utf8mb4", which uses 1–4 bytes per code point, and is capable of encoding all Unicode code points. (utf8mb4 is UTF-8, utf8 is a weird version of UTF-8.)

[Thanatos]

Note that this will not allow you to do range scans over these columns. Prefix lengths on VARCHARs will allow you to keep this trait, while causing possibly spurious matches in the index (and scanning and row lookup to eliminate them) (see the accepted answer). (This is essentially a manually implemented hash index, which sadly MysQL doesn't support with InnoDB tables.)

[Cerin]

This doesn't explain why fields well below the length limit are exceeding the length limit...

[SciPhi]

Is there any downside to changing to innodb_large_prefix globally? And is that DB "global" or all DBs TOTALLY GLOBAL?

[Chris Lear]

Only applies when using non-standard row formats. See dev.mysql.com/doc/refman/5.5/en/…. Specifically, 'Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes), for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.' The default row format is unaffected.

[Stefan Endrullis]

The number of allowed characters just depends on your character set. UTF8 may use up to 3 bytes per character, utf8mb4 up to 4 bytes, and latin1 only 1 byte. Thus for utf8 your key length is limited to 255 characters, since 3*255 = 765 < 767.

[Letharion]

I've tried editing in the information @Cerin is missing above, which clearly considered missing by others as well, but it gets rejected as being more suitable as a comment. For those trying to understand why 500 + 20 > 767 see Stefan Endrullis' comment on Julien's answer.

[haudoing]

Then your unique key lost. I think the better way is simply reduce the length of name to 191.

[cwd]

This worked well for me - more details and a guide can be found here: mechanics.flite.com/blog/2014/07/29/…

[Paweł Tomkiel]

Why to check for uniqueness programmatically if it's native DB feature?

[Paweł Tomkiel]

And what if he will try to insert non-latin1 characters?

[Sebas]

This is the worst thing to do. Never do that.

[TheCarver]

This saved me a lot of frustration - thank you. It should be the accepted answer IMO, considering the user is using InnoDB by claiming they hit a 767b limitation.

[Isaac]

— Which presumably means that when using utf8mb4, I need to set them to (at most) 191 as 191*4 = 764 < 767.

[morganwahl]

@Isaac Yes, exactly,

[Akhorus]

This reply is great. Still, the information in the response by @anthony-rutledge should be included here (or mentioned, or linked). The encoding-to-char-size issue is very important to dimension the column sizes.

[bernhardh]

AS Stefan Endrullis stated, it depends on the charset. If you use UTF8 which uses 3 bytes: 255x3=765 which is lower than the 767 limit, while 256x3=768 which is higher. But if you use UTF8mb4 its 255*4=1020, so its not a realy solution

[Adam Grant]

I think this might be the right answer, but could you elaborate on what one would need to do to correct such an issue? At least for MySQL noobs like me?

[morganwahl]

There's no one way to get around this index limit. The question here is about unique constraints; for those, you can have one column of unlimited length text, and another where you store a hash (like MD5) of that text, and use the hash column for your unique constraint. You'll have to ensure your program keeps the hashes up-to-date when it alters the text, but there's various ways to handle that without too much trouble. Quite frankly, MySQL should implement such a thing itself so you don't have to; I wouldn't be surprised if MariaDB has something like that built-in.

[Arsha]

This can be a problem. For example: I have field name (255) and add unique to this field at name(191) as I'm using utf8mb4. If I have my user add their name with 'IJUE3ump5fiUuCi16jSofYS234MLschW4wsIktKiBrTPOTKBK6Vteh5pNuz‌​1tKjy...aO500mlJs' And the other user add their name with this 'IJUE3ump5fiUuCi16jSofYS234MLschW4wsIktKiBrTPOTKBK6Vteh5pNuz‌​1tKjy...aO500mlJa' The different is the last character. It should pass validation not stuck at duplicate entry.

[Anthony Rutledge]

One wonders about the hit on indexing and lookup if a key field is too large.

[Claudio Holanda]

This is the best answer. Simple, straight to the point and also includes utf8mb4 limit (which is the most used encoding for newer databases, as it accepts emojis/etc).

[codewandler]

in my case its on a column which stores path names with only hex characters ... so this might be a solution for someone. it really depends on what you want to store...

[Bet Lamed]

I had to downvote this, since using latin1 is not a solution in 2016AD. I still have horrible nightmares about the time we had to convert a database from latin1 to utf8 back in 2007. Ugh. Not pretty. At all.

[Markus Köhler]

for utf8mb4 i just set all my string indexes to length 190, helped

[Stijn de Witt]

The index limit is 767 bytes, not characters. And since Mysql's utf8mb4 character set (which the rest of the world calls utf8) needs (at most) 4 bytes per character you can only index up to VARCHAR(191). Mysql's utf8 character set (which the rest of the world calls broken) needs at most 3 bytes per character so if you are using that (which you shouldn't), you can index up to VARCHAR(255)

[Stijn de Witt]

This answer is correct. However if 255 is indeed working for you, it means you are using Mysql utf8, which unfortunately is broken. It can only encode characters in the basic multilingual plane. You will get issues with characters falling outside of that. For example those Emoji characters they have been adding fall outside of it I think. So instead of switching to VARCHAR(255), switch to VARCHAR(191) and switch the encoding to utf8mb4 (which is actually just utf8, but MySql wanted to keep back. compat).

[Stijn de Witt]

A unique index on a very long varchar column is rare. Think about why you need it because it might be a design issue. If you just want an index on it for search, consider some 'keywords' field which fits within 191 characters, or split text into short description and long/complete text etc. Or if you really need full text search, consider using specialized software for it, such as Apache Lucene.

[morganwahl]

I'm not sure I'd call 191 characters "very long". I'm pretty sure I've got a few files with absolute paths longer than that. Also, note that this applies to any index of the column, not just one for a uniqueness constraint.

[alexg]

I had the same problem on a unique index on two columns where I store bitcoin addresses and transaction IDs. These will always be ASCII characters, so I will use latin1 on these columns. Upvoting.

[Adrian Cid Almaguer]

I can't found this: 6. set innodb_default_row_format option value to DYNAMIC.

[Adrian Cid Almaguer]

if I use set global innodb_default_row_format = DYNAMIC; I see this message: ERROR 1193 (HY000): Unknown system variable 'innodb_default_row_format'

[Abhishek]

how you got error from workbench to cmd? I did it from workbench it has option directly.

[Adrian Cid Almaguer]

I do it from CMD because I don't see the option in Workbench

[Abhishek]

Is you Launched CMD as Admin?

[Adrian Cid Almaguer]

I see the problem this var was introduced in v5.7.9 and I have v5.6.33 version, thanks

[Barett]

Not helpful for my multicolumn unique constraint. It also would not work for the OP's multicolumn unique constraint. (would give it a total size of 825 bytes)

[MaRmAR]

This did the trick. I've had varchar(256) and I had to change it to varchar(250).

[Anthony Kal]

if you are still error after enter above query, try to go to "phpmyadmin" > set the "collation" to your preference (for me i use "utf8_general_ci") > click apply (even if its already utf8)

[Anthony Rutledge]

This answer does not hit at the root of the matter.

[mveroone]

You may want to update your answer to state that InnoDB allows for 3072b key length if using Barracuda format with innodb_file_per_table and innodb_large_prefix

[Accountant م]

because 767 / 4 ~= 191 , and 767 / 3 ~= 255

[andres.gtz]

@BojanPetkovic well I just came from a Laravel issue, and this answer actually solved my problem.

[Bimal Poudel]

In some cases, where you are sure about storing short data, you can reduce the VARCHAR's size. eg. 100 (max 191). I would prefer this instead of Indexing on first 100 characters of the source data from of length 255.

[SimpleGuy]

do we need to restart mysql server after this ?

[Ludo]

Solved the problem for openmeetings setup (BTW you saved my night :-)

[Uriel Bertoche]

Depending on what is going to be stored on the columns, I see no problem with this solution. My database was migrated from latin1 to utf8, also had that nightmare, but considering this column would only store url or file paths for examble, I would consider this, if MySQL 5.7 wasn't an option. Upvoting

[Abhi]

This worked for me, I had a VARCHAR(500) on my UNIQUE, changed to VARCHAR(255) and it worked.

[UeliDeSchwert]

This answer is great. But does anyone know, why exactly this works?

[Ali Shaukat]

@Bobby Laravel documentation gives its explanation in the heading "Index Lengths & MySQL / MariaDB" laravel.com/docs/5.4/migrations#creating-indexes

[Ali Shaukat]

@Bobby I have updated the answer. I have added the explanation given by laravel documentation for this fix.

[q0rban]

Important missing details from this answer. innodb_file_format must be BARRACUDA and At the table level you have to use ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. See @cwd's comment above with the guide.

[Dinesh Sunny]

where and how to set it?

[xonya]

Yes, specifying ENGINE=InnoDB DEFAULT CHARSET=utf8 at the end of the CREATE TABLE statement I was able to have a VARCHAR(255) primary key. Thanks.

[cagcak]

someone give him +1 to exceed the limit 191 :)

[Deimos]

You can add this code in the boot function of App/Providers/AppServiceProvider.php Schema::defaultStringLength(191); don't forget to add use schema; at the top

[Alex]

Indeed! useful for some languages like Vietnamese which we must use utf8mb4 for the accuracy in search!

[Alex]

Or you can use mysql version 5.7 to keep utf8mb4 VARCHAR(255)

[Neo]

Worked for Laravel 5.8 too.

[Arun Basil Lal]

This is what worked for me too. I had a varchar(250), but the data was never that long. Changed it to varchar(100). Thanks for the idea :)

[Teekin]

I'm not using a tool that works with your instructions, but I'm still voting it up for trying to help people with actually fixing the problem. There are endless explanations of what causes the problem, but remarkably little on how to actually solve it.

[mahyard]

I confirm that my issue was the database collation too. I don't have any explanation for it. I use mysql v5.6.32 and DB collation was utf8mb4_unicode_ci.

[Kaizoku Gambare]

Following this answer, I searched all the VARCHAR(255) in my sql dump file and replaced them by VARCHAR(191) . This worked. I have no idea about possible sideeffect I used it to import data from a server to localhost.

[N.B.]

This is a bad solution. Reason: indexes are not meant to be infinitely-long. When you apply a unique index to something, you want the index to have fixed-with most of the time. That means you SHOULDN'T make stuff like email unique, but you should hash the email and make that one unique. Unlike raw string-data, hashes are fixed width and can be indexed and made unique without issues. Instead of understanding the problem, you're spreading awful practices that don't scale.

[YugoAmaryl]

the utf8mb4 limit 191 is what most people needed

[Yohanim]

Should be the accapted answer

[asgs]

Are you sure the limit of 3072 only applies from 5.7 onwards? 5.6 has that too

[Christopher Schultz]

This can (and probably should) also be set in the server's configuration files (e.g. /etc/mysql/my.cnf or /etc/mysql/conf.d/innodb.cnf, etc.). If you do not make this change permanent, you will be bitten by it again if you try to CREATE or ALTER a table to create a new field of this kind.

[x-yuri]

You can find more information in the following gist. Including a way to reproduce it.

[Anthony Rutledge]

I think this answer is short on explanation. My answer informs and educates. ;-)

[Nico Haase]

Why exactly should one do that? Additionally, if this has any downsides (which I assume), you should mention this

[Nico Haase]

Is there any problem in using utf8mb4?

[Nico Haase]

"Almost" is a pretty good hint that this is not a long-term solution

[Nico Haase]

Can you explain where that number of 200 comes from, if other answers contain a lower number like 191?

[Mike Rippon]

I couldn't use prefix indices because I needed to maintain compatibility with H2 for testing purposes, and found using a hash column works well. I would strongly recommend using a collision resistant function such as SHA1 instead of MD5 to prevent malicious users from creating collisions. An index collision could be exploited to leak data if one of your queries only checks the hash value, and not the full column value.

[Danang Ponorogo]

I've done some research and found different views. This problem arises because of the use of indexing "unique" in the email field in the users table. So simply adding a limit value of 250 can solve the problem. This value is added to the file migration which creates the users table on the line with the code "unique ()" so it looks like this: $table->string('email', 250)->unique ();

[Martin Tournoij]

This will mean your column won't be able store some Unicode characters; most notable emojis.

[Ahmed Osama]

if you are using laravel ... this solution will save yout hours !!!

[Adem Tepe]

I think placing lower limits on VARCHAR fields is more appropriate (although it requires more work).

[Zaman]

try to add explain using phpmyadmin see i.imgur.com/fJzm4oE.png

[arefindev]

Making varchar value from 255 to 191 solved the issue in my case.