Question:Mysql Error 1071

Edit

Question

What does Mysql Error 1071: Specified key was too long; max key length is 1024 bytes mean?

Edit

Answer

With Mysql v4.1 and later, strong Unicode support is built in. Unicode, is a specification defining a 16-bit character encoding scheme allowing characters from European, Chinese, Japanese, Hindi and all other major world languages.

The above problem was encoutered when trying to migrate data from a pre-4.1 version of Mysql to a 4.1.x version. During the import step, you encounter numerous errors. Such as: 

       mysql> CREATE TABLE mw_categorylinks  (
           ->   cl_from int(8) unsigned NOT NULL  default '0',
           ->   cl_to varchar(255) binary  NOT NULL default ,
           ->   cl_sortkey  varchar(255) binary NOT NULL default ,
            ->   cl_timestamp timestamp(14) NOT NULL,
            ->   UNIQUE KEY cl_from (cl_from,cl_to),
            ->   KEY cl_sortkey  (cl_to,cl_sortkey(128)),
           ->   KEY  cl_timestamp (cl_to,cl_timestamp)
           -> )  TYPE=MyISAM;
       ERROR 1071 (42000): Specified key was too long; max key length  is 1000 bytes


The problem occurs because MySQL allocates storage based on the character encoding setting. By default, UTF-8 is used which is 1-4 bytes per character (note that mysql 4.1 and 5 do not support 4byte UTF-8 encodings). Two alternative approaches to correcting this problem: change character encoding or modify DDL. The DDL modification - (i.e changing total key column length to be under 1000 bytes) is the simpler of the two. It is specific to the data without creating future issues possibly limiting or affecting character encoding settings. 

   References:
   MySQL bug #6604
   MySQL bug #9502
   neohapsis archives
   MySQL Unicode Support


Most Recent

Most Popular

Most Active Categories


Back To Top Add New Article Printable Page

Categories: Questions

Questions

MediaWiki

This page has been accessed 7,739 times.

This page was last modified 00:28, 10 January 2006.