i was trying to import my backup after ive change the charset to utf8 from latin1 , now during my import i got this error ERROR 1071 (42000) at line 2137: Specified key was too long; max key length is 1000 bytes try to change my.cnf and set all charset and connection to utf8 , but now luck , i dont want to get back to latin1 and i know this will fix the issue , but utf8 my gaol any clue ? i know latin 1 byte = 1 char and utf8 3 byte = 1 ..
Asked
Active
Viewed 1,135 times
1
-
2UTF-8 is *not* 3 bytes per character. Different characters can require a different number of bytes to encode. Most characters that are commonly used in latin1 have the same 1 byte representation in UTF-8. – Mark Byers Nov 09 '10 at 07:04
-
Relative answers: [here](http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/1814538#1814538) and [there](http://stackoverflow.com/questions/1037598/specified-key-was-too-long-max-key-length-is-1000-bytes/1037613#1037613). – dma_k Nov 10 '11 at 10:13
3 Answers
3
Although UTF-8 is not always 3 bytes for every character, MySQL is reserving 3 bytes per character for indexes.
If your application allows for it (i.e. it's not used in a UNIQUE KEY constraint), re-define / ALTER the table to use a prefix key of the appropriate length (i.e. < 1000/3).
CREATE TABLE my_table .... KEY my_field_20 (my_field(20));
Riedsio
- 9,758
- 1
- 24
- 33
-1
Use the --force option in your mysqldump command, it will ignore the error and continue the backup.
Christophe Eblé
- 8,071
- 3
- 33
- 32
-
What will be with index? What if index's prefix is physically longer than 1000 bytes? – zerkms Nov 09 '10 at 07:10
-
-
SleepyCod : am not doing a backup am restoring my back after changing the latin1 to utf using sed -e 's/latin1/utf8/g' db_utf8.sql > /home/db-utf8.sql – user172697 Nov 09 '10 at 07:37