MySQL – Fix ERROR 1071: Specified Key Too Long During Dump Restore

MySQLmysqldump

while trying to restore a MySql Dump, i get the following error:

ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 1000 bytes

any ideas?

Best Answer

You may to have adjust some VARCHAR length in some table based on the Character Set you are using.

That exact error message is actually posted as a bug report from Nov 2004, when in fact, it is not really a bug. That should direct you on how to adjust key lengths, especially your PRIMARY KEYs.

If you know which table is causing the Error 1071, you have to do the following:

Step 01) mysqldump only database schema

mysqldump --routines --triggers --all-databases --no-data > MySQLSchema.sql

Step 02) mysqldump only data

mysqldump --all-databases --no-create-info > MySQLData.sql

Step 03) Using vi or some other editor, edit the table's PRIMARY KEY to manually limit the PRIMARY KEY in such a way that the key does not exceed 1000 characters.

Step 04) Load the schema

mysql -A < MySQLSchema.sql

Step 05) Load the data

mysql -A < MySQLData.sql

You may have to resort to this if you cannot otherwise figure this out,

UPDATE 2012-01-23 11:43 EDT

Since you mentioned a certain table has the wrong storage engine, here is what you do:

Look above at Step 03. Go find the table in the file. Look for ENGINE=MyISAM in the file and change it to ENGINE=InnoDB. Then, load the schema file first and the data file afterwards.

UPDATE 2012-01-23 11:52 EDT

You should add this to to /etc/my.cnf

[mysqld]
default-storage-engine=InnoDB

then run service mysql restart. If you cannot restart mysql at this time, run this in the mysql client:

mysql> SET GLOBAL default_storage_engine = 'InnoDB';

However, the mysqldump will still have the ENGINE=MyISAM at the end of each CREATE TABLE. You will have to perform the 5-Step plan I mentioned above including changing the ENGINE=MyISAM to ENGINE=InnoDB. DO NOT CHANGE THAT FOR THE mysql DATABASE !!! Change only those tables that are yours.

Related Solutions

Mysql – Error while restoring a Database from an SQL dump

The reference to --binary-mode (introduced in MySQL 5.6.3) is probably a distraction.

It doesn't sound like you're dealing with a mysqldump output file, there. Try the file utility.

shell> file dumpfile.sql
dumpfile.sql: ASCII text

If you don't get the ASCII text response, you're dealing with either something that isn't a dump file from mysqldump at all, or you're dealing with something that's been compressed (with gzip or bzip2, for example), which you'd need to uncompress before piping it into mysql.

If you see SQLite 3.x database then you definitely have your answer... it's a raw SQLite database, not a MySQL dump file.

Indeed, the first few bytes of a SQLite database are these:

53 51 4C 69 74 65 20 66  SQLite f
6F 72 6D 61 74 20 33 00  ormat 3^@

Note that the 16th octet here is 0x00, explaining the ERROR: ASCII '\0' appeared in the statement... message in this case. The suggestion that --binary-mode is appropriate is a false alarm.

Windows users: the 'file' utility is a tool from Unix, but the Windows version can be found here.

Mysql – Specified key was too long; max key length is 1000 bytes in thesql 5.6

You can increase the maximum InnoDB index prefix size in MySQL 5.6 to 3072 bytes by setting innodb_large_prefix to ON along with other settings that you'll also need in order to enable that one, discussed here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

These changes should allow these indexes to be valid for InnoDB tables.

With a character set of (I assume) utf8, a VARCHAR(1024) would need 1024 x 3 = 3072 bytes for its index.

Updates:

I incorrectly showed the value of the variable as Yes when it should have been ON.

But also:

I think it needs 3072+2 – ypercube

When I thought about this, it sounded correct, because 1 byte is needed to store the size of the value when the value is 255 bytes or less, and 2 bytes are needed otherwise.

However, testing reveals that this isn't the case, in this case -- InnoDB using the COMPRESSED row format from Barracuda can actually index the full size of a VARCHAR(1024)... so either they've documented it strangely or the COMPRESSED row format stores the length out-of-band along with another block of metadata, so it doesn't count in the total bytes in this format.

COMPRESSED doesn't truncate the index into a prefix index and throw a warning until you go to VARCHAR(1025).

+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 3072 bytes |
+-------+------+----------------------------------------------------------+

It's nice the way it throws a warning instead of throwing an error, but that doesn't help us here because this still requires the explicit ROW_FORMAT declaration to trigger this behavior.

So, my initial answer is still wrong, because you have to explicitly add ROW_FORMAT=COMPRESSED to the end of the table definition. Otherwise you still get the same old error.

Actually, you get two different errors in 5.6.10. If you try to create a table with a fully-indexed VARCHAR(1024) you get this:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

...but if you try with a fully-indexed VARCHAR(1025) you get this:

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

That's sloppy code but the bottom line is that my answer doesn't actually fix this problem.

I don't see a way to use ROW_FORMAT=COMPRESSED by default, nor does it seem like a good idea if it were possible, and I'm inclined to suggest that the answer is...

...there's not a readily available workaround here. My other thought was "character sets" but the difference between latin1 and utf8 still isn't sufficient to explain 1,024 vs 1000 or 767. I'll happily get behind a better idea but at the moment, I can't think of a version of MySQL Server that this code would work properly on.