1

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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
2
  • But, that works in my local machine... Commented Feb 17, 2014 at 16:48
  • 1
    Your remote machine and local machine are evidently using two different default charsets. Commented Feb 18, 2014 at 2:13

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.