-2

when i try to create below table in my sql i got an error stating that

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

CREATE TABLE fields_meta_data 
(id varchar(255)  NOT NULL ,
 name varchar(255)  NULL ,
 vname varchar(255)  NULL ,
 comments varchar(255)  NULL ,
 help varchar(255)  NULL ,
 custom_module varchar(255)  NULL ,
 type varchar(255)  NULL ,
 len int(11)  NULL ,
 required bool  DEFAULT '0' NULL ,
 default_value varchar(255)  NULL ,
 date_modified datetime  NULL ,
 deleted bool  DEFAULT '0' NULL ,
 audited bool  DEFAULT '0' NULL ,
 massupdate bool  DEFAULT '0' NULL ,
 duplicate_merge smallint  DEFAULT '0' NULL ,
 reportable bool  DEFAULT '1' NULL ,
 importable varchar(255)  NULL ,
 ext1 varchar(255)  NULL ,
 ext2 varchar(255)  NULL ,
 ext3 varchar(255)  NULL ,
 ext4 text  NULL  , 
 PRIMARY KEY (id),   
 KEY idx_meta_id_del (id, deleted),   
 KEY idx_meta_cm_del (custom_module, deleted)
 )
Alen Oblak
  • 3,285
  • 13
  • 27
user1948334
  • 13
  • 2

2 Answers2

0

Why do you need to use such wide fields? Even for id you have used VARCHAR(255).

You are trying to create keys/indexes which are just too wide.

If you simply reduce your appetite and use reasonable field defintions, like integer for id, and other fields narrower than 255, you should get it working in no time.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • thanks for your reply when i reduced the id lenght to 36,i successfully created that table,,thanks once again :) – user1948334 Jan 15 '13 at 08:46
  • Glad you got it working. You should reduce size of other columns as low as you are permitted to - this can never hurt for performance and db size on disk. – mvp Jan 15 '13 at 08:53
0

I gues you are using UTF8 character set. Your ID column takes 255 * 4 bytes, that is 1020 bytes, which is too much for MyISAM tables.

Alen Oblak
  • 3,285
  • 13
  • 27