简单转储导入设置中的 mysql 错误 1071（指定的密钥太长；最大密钥长度为 1000 字节）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10873870/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql ERROR 1071 (Specified key was too long; max key length is 1000 bytes) in simple dump-import setting
提问by Viktor Trón
dump mysql db on server 1
在服务器 1 上转储 mysql db
$ mysql --version
mysql Ver 14.14 Distrib 5.1.54, for debian-linux-gnu (x86_64) using readline 6.2
$ mysqldump -u root -p db > db.sql
import on server 2
在服务器 2 上导入
$ mysql --version
mysql Ver 14.12 Distrib 5.0.95, for unknown-linux-gnu (x86_64) using readline 5.1
$ mysql -u root -p db < db.sql
ERROR 1071 (42000) at line 807: Specified key was too long; max key length is 1000 bytes
I know there is a lot of questions and answers on this error but it still leaves me puzzled.
我知道这个错误有很多问题和答案，但它仍然让我感到困惑。
Can it be a version problem? I suspect no.
会不会是版本问题？我怀疑没有。
If I run it with --force option, it gets even wierder:
如果我使用 --force 选项运行它，它会变得更加奇怪：
ERROR 1071 (42000) at line 807: Specified key was too long; max key length is 1000 bytes
ERROR 1146 (42S02) at line 847: Table 'db.users' doesn't exist
ERROR 1146 (42S02) at line 848: Table 'db.users' doesn't exist
ERROR 1146 (42S02) at line 849: Table 'db.users' doesn't exist
ERROR 1146 (42S02) at line 850: Table 'db.users' doesn't exist
what is going on?
到底是怎么回事？
I mean apart from solving this, I would like to understand what settings affect a simple dump-import act and why can those settings not be explicit in my dump file and be set t import.
我的意思是除了解决这个问题之外，我想了解哪些设置会影响简单的转储导入行为，以及为什么这些设置不能在我的转储文件中明确显示并设置为导入。
I prefer not having to debug actual errors, this must be solveable on high level.
我更喜欢不必调试实际错误，这必须在高层次上可以解决。
UPDATE: SOLUTION as Frederic pointed me to the right direction. Basically my dump was trying to set db with INNODB engine, but mysql on server 2 had in /etc/my.cnf
更新：解决方案，因为 Frederic 为我指出了正确的方向。基本上我的转储试图用 INNODB 引擎设置 db，但是服务器 2 上的 mysql 在 /etc/my.cnf 中有
[mysqld]
skip-innodb
by simply deleting this option and restarting mysqld, my import ran without a croak. I am very sad such a simple thing like an unavailable engine is not worthy of warning or error rather than key length issue caused by silently falling back to myISAM. Hmm. So time to switch to posgresql? mongo? :)
通过简单地删除这个选项并重新启动 mysqld，我的导入运行没有吱吱声。我很伤心，像一个不可用的引擎这样简单的事情不值得警告或错误，而不是静默回退到 myISAM 导致的密钥长度问题。唔。那么是时候切换到 posgresql 了吗？蒙戈？:)
回答by Frederick Cheung
Sounds like innodb is disabled on the second machine, so mysql silently falls back to myisam which has different limitations: 1000 bytes per key instead of 3500
听起来在第二台机器上禁用了 innodb，所以 mysql 默默地回退到 myisam，它有不同的限制：每个键 1000 字节而不是 3500
The last time I saw something like this it was because of a configuration problem: mysql can't setup innodb on startup so it disables innodb. Check your mysql error log, it should flag any problems encountered during startup. For example, innodb will refuse to initialize if the innodb_log_file_sizesetting doesn't match the size of the log files (ib_logfile0, ib_logfile1, ...)
上次我看到这样的事情是因为配置问题：mysql 无法在启动时设置 innodb，因此它禁用了 innodb。检查您的 mysql 错误日志，它应该标记启动过程中遇到的任何问题。例如，如果innodb_log_file_size设置与日志文件（ib_logfile0, ib_logfile1, ...）的大小不匹配，innodb 将拒绝初始化
回答by FTRNX
This work for me.
这对我有用。
mysql -u USERNAME -p
use DATABASENAME;
set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;
set global innodb_file_per_table=true;
Error: “#1071 – Specified key was too long; max key length is 767 bytes” when import mysql db
回答by tquang
Besides, if your system is 64bit and you are running MySQL from yum (repository default), you will see this error when attemp restore database from other systems. I don't know why "yum install mysql mysql-devel mysql-server", them just run with 32bit.
此外，如果您的系统是 64 位，并且您正在从 yum（存储库默认）运行 MySQL，那么当您尝试从其他系统恢复数据库时，您将看到此错误。我不知道为什么“yum install mysql mysql-devel mysql-server”，他们只是在 32 位上运行。
So, in this case please install and use MySQL from Percona or Marina (of course is 64bit version)
所以，在这种情况下，请从 Percona 或 Marina 安装和使用 MySQL（当然是 64 位版本）
