Facebook X (Twitter) Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook X (Twitter) Instagram
    TecAdmin
    You are at:Home»Databases»MySQL»(Resolved) Unknown collation: utf8mb4_unicode_520_ci

    (Resolved) Unknown collation: utf8mb4_unicode_520_ci

    By RahulJuly 30, 20222 Mins Read

    A collation is a set of rules that defines how to compare and sort character strings in a database server. In this tutorial, we are discussing an error faced during database restoration on another server.

    Let’s see the problem and solution to the issue faced recently:

    The Problem:

    During the migration of a WordPress application, I faced the following error with the restoration of the MySQL database. The collation id may differ based on the MySQL version.

    Error message:

    Error 1273 (HY000) at line 36 Unknown collation: 'utf8mb4_unicode_520_ci'
    

    Here you go with a solution.

    The Solution:

    After searching for the error, I found that the MySQL server running on the other server is an older version than the source. So we find out that the destination server doesn’t support the ‘utf8mb4_unicode_520_ci’ collation.

    To resolve this issue, I did a little change in the backup file. Edit the database backup file in text editor and replace all occurrences of “utf8mb4_unicode_520_ci” with “utf8mb4_general_ci“. Also, if you found “CHARSET=utf8mb4“, replace this with “CHARSET=utf8“.

    Replace the below string:

    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
    

    with:

    ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
    

    Save the backup file and restore the database.

    The Linux system users can use the sed command to replace text in files directly.

    sed -i 's/utf8mb4_unicode_520_ci/utf8_general_ci/g' backup.sql  
    sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql  
    

    That’s it, Now the database is successfully restored without any errors!

    Hopefully, this is solution helped you to resolve “Unknown collation: ‘utf8mb4_unicode_520_ci’” issue with MySQL databases.

    collation database MySQL
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    Working with Python FastAPI and MySQL

    Resolving MySQL ERROR 1041 (HY000): Out of Memory

    How to Calculate the MySQL Database Size

    Add A Comment

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • Difference Between Full Virtualization vs Paravirtualization
    • Virtualization vs. Containerization: A Comparative Analysis
    • Using .env Files in Django
    • Using .env File in FastAPI
    • Setting Up Email Notifications for Django Error Reporting
    Facebook X (Twitter) Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.