How to Solve MariaDB Error Message ERROR 1071 (42000) : Specified key was too long; max key length is 767 bytes

This is an article written to provide an actual solution of how to solve the error message shown in the title of this article. The situation is involving on the process of restoring a dump file created by dumping it from a database in MySQL Database. The database is a MySQL Server version: 5.7.18 MySQL Community Server (GPL). This is an effort of migrating the database to another type of database which in this case, it is MariaDB with the version of 5.5.56-MariaDB.

Below is the output of the restore process of a dump file retrieved from MySQL Database Server where the target of the restore process is into MariaDB Server. For more info, it is actually a process of migrating a Moodle database from MySQL Database to MariaDB. But the focus is in the restore process as shown below :

[root@hostname mysql]# mysql -uroot moodle < /home/user/moodle-dump-file.sql 
ERROR 1071 (42000) at line 1313: Specified key was too long; max key length is 767 bytes    
[root@hostname mysql]#

The error is specified as shown in the title and also in the above output command generated :

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

The step which is actually taken for solving the above problem basically just editing the MariaDB Database file configuration. The file itself is located in /etc/my.cnf.

innodb_large_prefix=on
innodb_file_format=Barracuda

Don’t forget to place it in the [mysqld] section. The following are the complete configuration of it :

[mysqld]
innodb_large_prefix=on
innodb_file_format=Barracuda

After editing the MariaDB configuration file by adding the above two lines of configuration in the [mysqld] section, just restart the service by executing the following command :

service mysqld restart

It can also be restarted using the following command :

systemctl mysqld restart

The point is just to restart the service using any available methods in the operating system where the database located. The process of restoring the file retrieved from MySQL Database Server can be continued. By re-executing the above command for restoring the dump file into the database. There may be an additional error message triggered after depends on the condition or the situation, but in general the error message generated in the title of this article has already been solved.