Error while importing sql - Specified key was too long; max key length is 1000 bytes

Question

I am trying to import an sql file which shows me the following error:

Error
SQL query:

-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS  `users` (

 `uid` INT( 11 ) NOT NULL AUTO_INCREMENT ,
 `uemail` VARCHAR( 255 ) NOT NULL ,
 `upassword` VARCHAR( 255 ) NOT NULL ,
 `uname` VARCHAR( 255 ) NOT NULL ,
 `uemailh` VARCHAR( 255 ) NOT NULL ,
 `uclass` TINYTEXT NOT NULL ,
 `regno` VARCHAR( 8 ) NOT NULL ,
 `absencecount` VARCHAR( 255 ) NOT NULL DEFAULT  'a:10:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;i:4;i:0;i:5;i:0;i:6;i:0;i:7;i:0;i:8;i:0;i:9;i:0;}',
 `internalmarks` VARCHAR( 500 ) NOT NULL DEFAULT  's:73:"["0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0"]";',
PRIMARY KEY (  `uid` ) ,
UNIQUE KEY  `uemail` (  `uemail` ,  `uname` ) ,
KEY  `regno` (  `regno` )
) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT =2;

MySQL said: Documentation

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

I've tried to create a new database in my local machine everything works fine. But, while importing it in the remote server it shows the above error. Any Idea?

Answer 1

According to the MySQL Documentation on CREATE INDEX:

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.

My guess would be the character set you are using in conjunction with

UNIQUE KEY  `uemail` (  `uemail` ,  `uname` ) ,

Please note that both of those columns are VARCHAR(255). Together their maximum character length could be 511 (255 characters X 2 + 1 byte for VARCHAR length management). Since UTF8 is a multibyte character set, 511 * 2 = 1022. That's way past InnoDB's limit of 767 bytes. Even if you convert the table to MyISAM, it's still past 1000 bytes.

I would suggest drastically reducing the widths of uemail and uname.