#1071 - Specified key was too long; max key length is 1000 bytes

Question

I know questions with this title have been answered before, but please do read on. I've read thoroughly all the other questions/answers on this error before posting.

I am getting the above error for the following query:

CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
  `menu_id` varchar(32) NOT NULL,
  `parent_menu_id` int(32) unsigned DEFAULT NULL,
  `menu_name` varchar(255) DEFAULT NULL,
  `menu_link` varchar(255) DEFAULT NULL,
  `plugin` varchar(255) DEFAULT NULL,
  `menu_type` int(1) DEFAULT NULL,
  `extend` varchar(255) DEFAULT NULL,
  `new_window` int(1) DEFAULT NULL,
  `rank` int(100) DEFAULT NULL,
  `hide` int(1) DEFAULT NULL,
  `template_id` int(32) unsigned DEFAULT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `layout` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`menu_id`),
  KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Does anyone have idea why and how to fix it? The catch is - this same query works perfectly on my local machine, and worked as well on my previous host. Btw.it's from a mature project - phpdevshell - so I'd guess these guys know what they are doing, although you never know.

Any clue appreciated.

I'm using phpMyAdmin.

If with **Java Hibernate** refer to answer https://stackoverflow.com/a/76016256/9370144 — Nadun Kulatunge, Apr 14 '23 at 14:38

Bill Karwin · Accepted Answer · 2018-06-18T14:21:50.380

256

As @Devart says, the total length of your index is too long.

The short answer is that you shouldn't be indexing such long VARCHAR columns anyway, because the index will be very bulky and inefficient.

The best practice is to use prefix indexes so you're only indexing a left substring of the data. Most of your data will be a lot shorter than 255 characters anyway.

You can declare a prefix length per column as you define the index. For example:

...
KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50))
...

But what's the best prefix length for a given column? Here's a method to find out:

SELECT
 ROUND(SUM(LENGTH(`menu_link`)<10)*100/COUNT(`menu_link`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`menu_link`)<20)*100/COUNT(`menu_link`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`menu_link`)<50)*100/COUNT(`menu_link`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`menu_link`)<100)*100/COUNT(`menu_link`),2) AS pct_length_100
FROM `pds_core_menu_items`;

It tells you the proportion of rows that have no more than a given string length in the menu_link column. You might see output like this:

+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
|         21.78 |         80.20 |        100.00 |         100.00 |
+---------------+---------------+---------------+----------------+

This tells you that 80% of your strings are less than 20 characters, and all of your strings are less than 50 characters. So there's no need to index more than a prefix length of 50, and certainly no need to index the full length of 255 characters.

PS: The INT(1) and INT(32) data types indicates another misunderstanding about MySQL. The numeric argument has no effect related to storage or the range of values allowed for the column. INT is always 4 bytes, and it always allows values from -2147483648 to 2147483647. The numeric argument is about padding values during display, which has no effect unless you use the ZEROFILL option.

edited Jun 18 '18 at 14:21

answered Jan 05 '12 at 18:24

Bill Karwin

538,548
86
673
828

31

Thanks so much for detailed explanation. In addition to fixing a problem, I've also learned something valuable. – CodeVirtuoso Jan 05 '12 at 18:53
Really, very useful query for finding out the length to which index should be set. Been using this some times to determine the best length for an index. Thank you for sharing! – Niraj Kumar Jan 27 '18 at 19:35
2

There's a subtle bug in your handy query to measure how long the strings really are: you're assuming that the strings are all present; that is, that they are all there. If some are null, it will throw off your calculations and underreport short strings. You want to use count([field_name]) instead of count(*). – D Mac Jun 17 '18 at 18:59
It won't use more than the first "prefix" index. – Rick James Sep 10 '19 at 16:00
1

This is a life saver, when you find a mysql database which has all its varchar fields set to length 255 (even on columns that have max 10 characters in real life) and you try to add a composite index. Thank you! – Udo Klimaschewski Sep 16 '21 at 07:25
What if your key is a unique key, and this isn't about performance, but about ensuring correct values? – Jon Oct 04 '21 at 23:35
@Jon A unique key on a prefix of a string means the part in the prefix must be unique. If that doesn't work for the strings in your column, then define a longer prefix, or no prefix. – Bill Karwin Oct 05 '21 at 01:50
@BillKarwin What I meant was -- what if we need a unique key with all these full prefixes (or no prefix), and we've exceeded the built-in 3072 limit? – Jon Oct 05 '21 at 15:16
@Jon Don't make a unique key on such a long string. – Bill Karwin Oct 05 '21 at 15:30
@BillKarwin unfortunately that's the requirement for the business logic – Jon Oct 05 '21 at 17:57
1

@Jon I don't know what you want me to say. MySQL cannot make an index, unique or non-unique, on such a long string. You could add another column to store a hash of your string and put a unique index on that. – Bill Karwin Oct 05 '21 at 19:19
@BillKarwin makes sense, just unfortunate that we have such limiting restrictions these days. I understand it's not good for performance, but uniqueness is for conformance, not performance – Jon Oct 05 '21 at 20:01
I think we won't get the real max column length because of ROUND(). When I use your method, the length of 2000 will cover 100 percent of my records. While using `SELECT MAX(LENGTH(source)) FROM tb;`, the result is more than 6000. – wbzy00 Dec 05 '22 at 12:58
@wbzy00, What do you propose as a solution? – Bill Karwin Dec 05 '22 at 15:32
@Bill Karwin Remove `ROUND()` from your solution, or use `SELECT MAX(LENGTH(source)) FROM tb;`, if the purpose is to find the max column length;. Yours is still OK for deciding on a proper length for indexing, but not accurate enough for getting the max column length, because `ROUND()` will round 99.9% to 100% apparently. – wbzy00 Dec 06 '22 at 12:35
I find it confusing to use SUM() this way. To me, using COUNT() instead of SUM() makes more sense, i.e. `SELECT ROUND(COUNT(LENGTH(menu_link)<10)*100/COUNT(menu_link),2) AS pct_length_10 FROM pds_core_menu_items;`, but this would give the wrong result of 100.00. – wbzy00 Jan 07 '23 at 07:26
1

@wbzy00, Correct, COUNT() counts values of 0 the same as it counts values of 1. But SUM() counts only values of 1, since adding a 0 doesn't change the sum. Thus SUM() of a boolean expression is equal to the COUNT() of rows where the boolean expression is true. This works in MySQL because MySQL treats false as the integer value 0 and true as the integer value 1. – Bill Karwin Jan 07 '23 at 18:28
In what way is this too long? My create table statement worked perfectly in MySQL, but it is now failing after trying to run it in MariaDB. I see no reason why 255 characters is to long for a key in this day and age. Anything requiring smaller than 255 is antiquated. – John Foll Mar 05 '23 at 15:06
@JohnFoll You're aware that MySQL and MariaDB are different products? – Bill Karwin Mar 05 '23 at 16:59
@JohnFoll It also depends which version of MariaDB you use (check with `SELECT VERSION();`). MariaDB 10.3.1 and later always default to large index prefixes (cf. https://mariadb.com/kb/en/innodb-limitations/). If you use an older version, see https://stackoverflow.com/a/43403017/20860 for instructions on what you can do. – Bill Karwin Mar 05 '23 at 17:29

score 37 · Answer 2 · answered Jan 05 '12 at 17:50

This error means that length of index index is more then 1000 bytes. MySQL and storage engines may have this restriction. I have got similar error on MySQL 5.5 - 'Specified key was too long; max key length is 3072 bytes' when ran this script:

CREATE TABLE IF NOT EXISTS test_table1 (
  column1 varchar(500) NOT NULL,
  column2 varchar(500) NOT NULL,
  column3 varchar(500) NOT NULL,
  column4 varchar(500) NOT NULL,
  column5 varchar(500) NOT NULL,
  column6 varchar(500) NOT NULL,
  KEY `index` (column1, column2, column3, column4, column5, column6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

UTF8 is multi-bytes, and key length is calculated in this way - 500 * 3 * 6 = 9000 bytes.

But note, next query works!

CREATE TABLE IF NOT EXISTS test_table1 (
  column1 varchar(500) NOT NULL,
  column2 varchar(500) NOT NULL,
  column3 varchar(500) NOT NULL,
  column4 varchar(500) NOT NULL,
  column5 varchar(500) NOT NULL,
  column6 varchar(500) NOT NULL,
  KEY `index` (column1, column2, column3, column4, column5, column6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

...because I used CHARSET=latin1, in this case key length is 500 * 6 = 3000 bytes.

Thanks for reply, this works, but at cost of giving up utf8 charset. Can this restriction somehow be overcome (I have full server access), is it there for a good reason? — CodeVirtuoso, Jan 05 '12 at 18:32
This is terrible advice, please learn what charsets are and how this will cause major issues in the future. A mixed charset database not only causes issues when using joins or sub-selects, it puts your data into a non-normalised format and can be near impossible to correct later. — Geoffrey, Mar 12 '19 at 00:51
I moved the charset to latin1, got it working and than changed it again to utf-8 — Ran, Apr 09 '21 at 13:11

score 34 · Answer 3 · edited Jun 20 '20 at 09:12

34

I had this issue, and solved by following:

Cause

There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes that you can check here.

Resolution

Make sure MySQL is configured with the InnoDB storage engine.

Change the storage engine used by default so that new tables will always be created appropriately:

set GLOBAL storage_engine='InnoDb';

For MySQL 5.6 and later, use the following:

SET GLOBAL default_storage_engine = 'InnoDB';

And finally make sure that you're following the instructions provided in Migrating to MySQL.

Reference

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 04 '18 at 22:26

Vishrant

15,456
11
71
120

3

In most cases, we forget to configure the storage engine as 'InnoDB'. The following answer is the most simple one and probably will solve the problem for most of the users here. Thanks. – Frederiko Ribeiro Oct 22 '19 at 16:43

score 11 · Answer 4 · answered Apr 04 '14 at 19:49

11

run this query before creating or altering table.

SET @@global.innodb_large_prefix = 1;

this will set max key length to 3072 bytes

answered Apr 04 '14 at 19:49

Raza Ahmed

2,661
2
35
46

6

FWIW, MySQL 8.0 removed this variable because it is always enabled in that version. There is no more need for it as a variable. – Bill Karwin Mar 25 '22 at 15:04

score 6 · Answer 5 · edited Jun 28 '22 at 05:43

6

I was facing the same issue; used the below query to resolve it.

While creating the DB you can use utf-8 encoding.

eg. create database my_db character set utf8 collate utf8mb4;

EDIT:

(Considering suggestions from comments)

Changed utf8_bin to utf8mb4

edited Jun 28 '22 at 05:43

J. Scott Elblein

4,013
15
58
94

answered Sep 05 '18 at 08:28

Sudhir Dhumal

902
11
22

3

This pointed me in the right direction. For me I had to change my collation to: utf8_general_ci – Ian Newland Jan 09 '19 at 09:58
4

This is NOT the right direction, do not do this! MySQL's `utf8` is NOT `utf8`, it is a bugged proprietary format that should never have come to fruition. `utf8mb4` is true `utf8` and is the recommended default for proper `utf8` support. – Geoffrey Mar 12 '19 at 00:55
`utf8mb4` is the correct encoding to use on mysql and not `utf8` – alok Mar 17 '20 at 16:14

score 4 · Answer 6 · answered Feb 02 '22 at 10:35

4

if you are using Laravel 7 or Laravel 8, goto to config/database.php

'engine' => 'innoDb',

that should work especially using Wamp or Xampp.

answered Feb 02 '22 at 10:35

Razor Mureithi

351
1
4
15

In my scenario, this did the trick. I have utf8mb4 database with utf8mb4_general_ci collation using InnoDB engine and my config was null. Changing engine to innodb I was able to run migrations. – Marcos Rocha Mar 04 '23 at 14:36

score 3 · Answer 7 · answered Nov 28 '12 at 23:12

This index size limit seems to be larger on 64 bit builds of MySQL.

I was hitting this limitation trying to dump our dev database and load it on a local VMWare virt. Finally I realized that the remote dev server was 64 bit and I had created a 32 bit virt. I just created a 64 bit virt and I was able to load the database locally.

score 3 · Answer 8 · answered Apr 30 '22 at 17:59

3

Well , I just changed from MyISAM to InnoDB like this

Before changing

ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

After changing

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

answered Apr 30 '22 at 17:59

Codeparl

300
1
8

score 2 · Answer 9 · answered Feb 05 '16 at 20:36

2

I have just made bypass this error by just changing the values of the "length" in the original database to the total of around "1000" by changing its structure, and then exporting the same, to the server. :)

answered Feb 05 '16 at 20:36

Vishwadeep Kapoor

114
5

Lalit Sati · Answer 10 · 2023-02-03T23:57:48.490

Getting the same exception while running back-end application in this case we can set out engine "InnoDb" set GLOBAL storage_engine='InnoDb';

if the above configuration is not working for you then change your key length for example if it is by default selected 255 char you can change it below of 100 Example - create table role ( role_name varchar(99) not null, role_description varchar(255), primary key (role_name)); it is works for me

score 0 · Answer 11 · answered Aug 11 '21 at 12:04

0

I had this error and I changed my tables column length smaller for the indexed foreign key columns so I changed it like this:

VARCHAR(1024)

To:

VARCHAR(512)

And run the query again.

answered Aug 11 '21 at 12:04

Pejman Kheyri

4,044
9
32
39

score 0 · Answer 12 · edited Jun 28 '22 at 05:40

0

I have gone through so many tricks, but none worked for me. Then I found the best and simplest one:

If you are working with mysql-workbench, while altering index select (Engine:)-innoDb.

edited Jun 28 '22 at 05:40

J. Scott Elblein

4,013
15
58
94

answered Dec 13 '21 at 13:31

AkshayMesharam

31
5

score 0 · Answer 13 · answered May 03 '22 at 04:22

0

If the sql is not created yourself, maybe just check and set the length of column to usual length.

answered May 03 '22 at 04:22

hatanooh

3,891
1
14
9

#1071 - Specified key was too long; max key length is 1000 bytes

13 Answers13

Linked

Related