mysql to mariadb restoring backup

Question

My mysql database dump is fine but when I go to restore it on a new server which uses mariadb it will only partially restore and then throw an error;

#1071 - Specified key was too long: max key length is 767 bytes

I have found that when I'm installing with a program it works when I use Force UTF-8 collation on database" and "Force UTF-8 collation on tables" but I don't know how to do that when I just have the raw sql file - help?

Answer 1

This can be fixed can replacing utf8mb4 by utf8 in the raw sql dump.

Other solutions can be found here: #1071 - Specified key was too long; max key length is 767 bytes

Answer 2

This problem existed before the limit was raised in 5.7.7 (MariaDB 10.2.2?).

If you are hitting the limit because of trying to use CHARACTER SET utf8mb4. Then do one of the following (each has a drawback) to avoid the error:

• Upgrade to 5.7.7 (or later) for a 3072 byte limit;
• Change 255 to 191 on the VARCHAR -- you lose any values longer than 191 characters (unlikely?);
• ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese;
• Use a "prefix" index -- you lose some of the performance benefits.

• Or... Stay with 5.6/5.5/10.1 but perform 4 steps to raise the limit to 3072 bytes:

  SET GLOBAL innodb_file_format=Barracuda;
  SET GLOBAL innodb_file_per_table=1;
  SET GLOBAL innodb_large_prefix=1;
  logout & login (to get the global values);
  ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- (or COMPRESSED)