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

User · Answer

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.

User · Answer

run this query before creating or altering table    SET   global innodb large prefix   1   this will set max key length to 3072 bytes

User · Answer

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

User · Answer

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

User · Answer

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   lt 10  100 COUNT  menu link   2  AS pct length 10   ROUND SUM LENGTH  menu link   lt 20  100 COUNT  menu link   2  AS pct length 20   ROUND SUM LENGTH  menu link   lt 50  100 COUNT  menu link   2  AS pct length 50   ROUND SUM LENGTH  menu link   lt 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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to phpmyadmin

Examples related to mysqldump