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

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.

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.

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.

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

run this query before creating or altering table.

SET @@global.innodb_large_prefix = 1;

this will set max key length to 3072 bytes

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

While creating 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

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.

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. :)

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

'engine' => 'innoDb',

that should work especially using Wamp or Xampp.

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.

I have gone through so many tricks...but none work for me and then i found...best and simple one

       if you are working with mysql-workbench,
       while altering index select (Engine:)-innoDb.
       apply and enjoy...

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;

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