What is unknown collation ‘utf8mb4_unicode-520-ci’ and how to solve it?

What is unknown collation ‘utf8mb4_unicode-520-ci’ and how to solve it?

  1. Bhawani Singh
    An error message describes a problem that stop a user or system from completing a task.1273 - Unknown Collation ‘utf8mb4_unicode-520-ci’ is an error message that you get while importing the WordPress database. This error is caused because the local server and live server are running different versions of MYSQL.

    For example if a server was running MYSQL 5.5, old server was running MYSQL 5.6. So you can get this error when you are trying to import the .sql file would have exported from the old version. MYSQL does not support utf8mb4_unicode_520_ci but MYSQL5.6 does.

    database.jpg

    How the error occurs and what are main causes for it?

    This error is caused by the difference in encoding types between the source and destination database. The newer sql database may use uft8mb4, and the older version may use utf8.This process of importing may cause this error. The 520 refers to MYSQL’s Unicode Collation Algorithm 5.2.0. This error may also happen when if you are trying to import MariaDB into MYSQL.

    How to fix the error?

    There are many methods to fix this error.

    Search and replace database dump file:

    Step 1: Open the sql file in your text editor;

    Step 2: Search: utf8mb4_unicode_ci Replace:uft8_general_ci(Replace All)

    Step 3: Search: utf8mb4_unicode_520_ci Replace:uft8_general_ci(Replace All)

    Step 4: Search: utf8mb4 Replace: uft8(Replace All)

    Step 5: Save and upload.

    Export Method:

    Step: In the case of using phpMyAdmin, log on to your database, Use EXPORTMETHOD: Custom. Then Under format-specific options: choose mysql4.0. And click Go

    Note: This resolves the unknown collation error. This is very simple one.

    Format specification while exporting the database method:

    1. In phpMyAdmin login to the database and just ensure it is the database requirement.

    2. Select export option from the database and choose custom. The custom button will be ratio button.

    3. There is the option “Database system or older MySQL server to maximize output compatibility with: MYSQL40.

    4. Click on go.

    SQL File:

    Step 1: This is same as text editor method but we do it in .sql file

    Step 2: ENGINE-Inne0DB DEFAULT CHARSET=uft8mb4 COLLATE=utf8mb4_unicode_520_ci;

    Step 3: In your .sql file, and swapping it with

    Step 4: ENGINE-Inne0DB DEFAULT CHARSET=uft8 COLLATE=utf8_general_ci;