How to Solve Error Message Unknown collation: ‘utf8mb4_unicode_520_ci’ when Restoring SQL File in a Database running in MariaDB Server

By Posted on

Introduction

Apparently, upon the process for restoring a database using an SQL file in a MariaDB Server, there is an error message appear. Because of that error message, the process ends in a failure. The process which is causing the failure for restoring an SQL file into a database running is triggering the error message. That error message appear in the title of this article as in the following command execution : 

[root@localhost backup-db]# mysql -uroot -p db_latest < db_restore.sql
Enter password:
ERROR 1273 (HY000) at line 665: Unknown collation: 'utf8mb4_unicode_520_ci'
[root@localhost backup-db]#

Solution

Actually, the solution for solving the problem is very simple. It is because MariaDB in this context does not accept that specific collation. It is a collation of ‘utf8mb4_unicode_520_ci’. So, in order to be able to restore the SQL file into the database, just change the SQL file for the solution in this article. Those step for changing the SQL file exist in the following sequences :

  1. First of all, just copy the file or duplicate it into another file. The main purpose is to have a new file with a slight modification in order for the restore process so that it will end in a success. 

    [root@localhost backup-db]# cp db_restore.sql db_restore_edit.sql

  2. The following after, just edit the file with the name of ‘db_restore_edit.sql’. In this context, if the process for the editing is using vi or vim editor exist in any Linux or Unix based operating system, just execute the following pattern in it : 

    :%s/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g

    In term of other text editor, just replace all of the string occurrences of ‘utf8mb4_unicode_520_ci’ with ‘utf8mb4_unicode_ci’.

  3. Last but not least, just execute the file for trying to restore it once again. But at this time, use the other file which has already edited accordingly. The following is the execution of the process : 

    [root@localhost backup-db]# mysql -uroot -p db_latest < db_restore_edit.sql
Enter password:
[root@localhost backup-db]#

Leave a Reply