SQL: Key Specified Is Too Long

Question

I Tried To Install phpMyLibrary. During Step 3 Installer Gives Me An Error In SQL Commands. Error Is As Follows:

SQL=Specified key was too long; max key length is 1000 bytes

My Codes Are:

CREATE TABLE `#__patron` (
  `uid` bigint(10) NOT NULL,
  `gid` bigint(10) NOT NULL,
  `barcode` bigint(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `branch` varchar(255) NOT NULL,
  `code` varchar(100) NOT NULL,
  `type` varchar(100) NOT NULL,
  `sup` varchar(255) NOT NULL,
  `org` varchar(255) NOT NULL,
  `dept` varchar(255) NOT NULL,
  `add1` varchar(255) NOT NULL,
  `add2` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(255) NOT NULL,
  `zip` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `grade` varchar(255) NOT NULL,
  `sex` varchar(255) NOT NULL,
  `ethnic` varchar(255) NOT NULL,
  `birthdate` date NOT NULL,
  `graddate` date NOT NULL,
  `parentinfo` varchar(255) NOT NULL,
  `comment` varchar(255) NOT NULL,
  `custfield1` varchar(255) NOT NULL,
  `custfield2` varchar(255) NOT NULL,
  `custfield3` varchar(255) NOT NULL,
  `custfield4` varchar(255) NOT NULL,
  UNIQUE KEY `uid` (`uid`),
  UNIQUE KEY `barcode` (`barcode`),
  KEY `name` (`name`,`code`,`type`)
) ENGINE=MyISAM;

I Doesn't Know Which Key Specified Is Too Long Help To Me Fix This Error.

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

Your error is coming from referencing too many bytes in the key-- you've reached the 1000 byte limit for a key with name, code and type:

KEY `name` (`name`,`code`,`type`)

That key contains 3 varchar fields. Each varchar field is equivalent to 3 times its precision. This results in 3*(255+100+100) = 1365 bytes

If you switch your engine from myisam to innodb it will work. If that's not an option, you need to remove that key or reduce its size.

Here's a good post about the limits with keys in mysql: https://stackoverflow.com/a/3489331/1073631

edited May 23 '17 at 12:15

Community

1
1

answered Mar 05 '16 at 05:32

sgeddes

62,311
6
61
83

1

You've nailed it, but just wanted to clarify that the 3x length is not always true, it depends on character encoding. UTF-8 is 3x length, but latin1 is 1x length, others vary. – Hart CO Mar 05 '16 at 06:06
@HartCO -- thanks for the clarification, very useful. – sgeddes Mar 05 '16 at 06:10

SQL: Key Specified Is Too Long

1 Answers1