0

This question already has an answer here:

Senario: I have a CMS with a database .sql file.

  1. When I try to load the .sql file on my local phpMyAdmin, its successful, it loads all the tables into phpMyAdmin.
  2. 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?

marked as duplicate by Rick James mysql Apr 29 '17 at 22:17

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • I voted that it was a dup, not because of WordPress, but because of the 767 limit and its causes and cures. Essentially 5.6 screwed up; 5.7 fixed it. And/or you have different character set between the two scenarios. – Rick James Apr 29 '17 at 22:19
  • We can't make changes in the system variables as it is not allowed from our web hosting provider. Please suggest some other solution. – Pranoti Buradkar Kokas May 2 '17 at 7:36
  • 3 of the 4 suggestions in dba.stackexchange.com/a/142379/1876 do not involve system variables. – Rick James May 2 '17 at 15:50
2

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

  • We can't make changes in the system variables as it is not allowed from our web hosting provider. Please suggest some other solution. – Pranoti Buradkar Kokas May 2 '17 at 7:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.