Tuesday, 19 May 2020

MySQL: Fix Error 1071 "Specified key was too long"

MySQL/MariaDB Error 1071 when try to import SQL File:

#1071 - Specified key was too long; max key length is 767 bytes
Sometime error is "max key length is 1000 bytes"

Reason:
From MySQL 5.6 and MariaDB 10.2.2 InnoDB File Format default is Barracudar. Barracudar is newer file type ,support many new format types (DYNAMIC and COMPRESSED). But in older version of MySQL or MariaDB default InnoDB File Format is still Antelope. So if you want to import a database using Barracudar(exported from newer version), you also need to change the current file format to Barracudar.

Solved:
To do that, run the following command in the Mysql command line (Root account is required):

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = on;
SET GLOBAL innodb_default_row_format = dynamic;
SET GLOBAL innodb_large_prefix = 1;
SET GLOBAL innodb_file_format_max = Barracuda;
FLUSH PRIVILEGES;
Done ! The problem has been solved, you can try imoport SQL file again.

Tip: You can run Mysql command line from linux terminal or run Query in Navicat, PHPMyadmin ....

mysql -h localhost -u root -p
Refer: https://dev.mysql.com/doc/refman/5.6/en/innodb-file-format-enabling.html

1 comment:

  1. Not the nickel video with all those strains such as you see now, however nickel three-reel slots with one or three strains, just like the quarters or dollars. Maximum wager was three cash, and you would go a great distance if you were only betting 15 cents on a nickel sport or 75 cents on quarters. Today, after all, video slots have taken over casino flooring. They’re the most-played games, especially in penny denominations, in order that they get probably 온라인 카지노 the most floor space. One thing our Videoslots casino review loves is reality that|the reality that} this operator holds licences issued by playing regulators within the UK and Malta.

    ReplyDelete