mysql ERROR 1071 (Specified key was too long; max key length is 1000 bytes) in simple dump-import setting
Solution 1
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
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_size
setting doesn't match the size of the log files (ib_logfile0
, ib_logfile1
, ...)
Solution 2
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
Viktor Trón
Ethereum go core ΞTH-ÐΞV, ΞTHΞЯSPHΞЯΞ, Übergeek Unltd freelance software engineer, data and information system architect web3.0, blockchain, crypto, networking, language technology, AI, machine learning linked in http://uk.linkedin.com/in/viktortron/ twitter http://twitter.com/zeligf github https://github.com/zelig
Updated on June 14, 2020Comments
-
Viktor Trón almost 4 years
dump mysql db on server 1
$ 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
$ 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:
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
[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? :)