Same script produces 'Specified key was too long' on one server and not on another Senario: I have a CMS with a database .sql file.


When I try to load the .sql file on my local phpMyAdmin, its successful, it loads all the tables into phpMyAdmin.
When I try to load the same file on Real CMS host(Network Solutions) phpMyAdmin, it is not loading the tables.


The error I get on the Real CMS Server is: 


  Specified key was too long; max key length is 767 bytes


What could cause the same script to produce this error on one server and not on the other?
There are 2 possible scenarios that can cause this, it's hard to tell which one you are having if we don't see the SQL file you are trying to import.

Maybe your default engine isn't the same on both servers.
If you are loading data in a MyISAM table on your localhost and a InnoDB table on Real CMS this could cause the issue because the maximum prefix is 767 bytes long for InnoDB and 1000 bytes for MyISAM. So if you have a key with a prefix of, lets say, 800 bytes it would load fine on your localhost but not in Real CMS.

It could also be caused by a different server configuration. The max key length for a MyISAM table can be controlled by the innodb_large_prefix system variable. 

Please see the documentation for CREATE INDEX which states:


  Prefix support and lengths of prefixes (where supported) are storage
  engine dependent. For example, a prefix can be up to 767 bytes long
  for InnoDB tables or 3072 bytes if the innodb_large_prefix option is
  enabled. For MyISAM tables, the prefix limit is 1000 bytes. The NDB
  storage engine does not support prefixes