Facebook X (Twitter) Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook X (Twitter) Instagram
    TecAdmin
    You are at:Home»General Articles»(Resolved) Unknown collation: utf8mb4_0900_ai_ci

    (Resolved) Unknown collation: utf8mb4_0900_ai_ci

    By RahulApril 17, 20233 Mins Read

    The collation utf8mb4_0900_ai_ci is a character set collation for MySQL databases, introduced in MySQL 8.0.1. It is based on the Unicode Collation Algorithm (UCA) 9.0.0, and the character set is utf8mb4, which supports a wide range of Unicode characters.

    The “ai” in the collation name stands for “accent insensitive” and the “ci” stands for “case insensitive.” This means that comparisons between characters are done without considering differences in case or accents.

    If you are receiving an “Unknown collation” error, it may be because your MySQL server version is older than 8.0.1 and doesn’t support this collation. To fix this issue, you can:

    Problem

    During the migration of a web application, I got the below error while restoring a database on another server. The collation id may differ based on the MySQL version.

    Error message:

    Error 1273 (HY000) at line 25 Unknown collation: ‘utf8mb4_0900_ai_ci’

    See the error screenshot during database restoration.

    Error 1273 (HY000) Unknown collation: 'utf8mb4_0900_ai_ci'

    Here you go with a solution.

    Solution

    After a little investigation, I found that the MySQL server running on the destination is an older version than the source. So we got that the destination server doesn’t contain the required database collation.

    Then we do a little tweak in the backup file to resolve this. Edit the database backup file in text editor and replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“.

    • Replace the below string:
      ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
      
    • With:
      ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
      

    Here we are changing the CHARSET to utf8, that is the older version and have limitation, Read the implications at the end of this article before making the changes in database.

    Save your file and restore the database.

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

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

    That it. after the above changes, the database was successfully restored!

    Hope this is solution helped you to resolve “Unknown collation: ‘utf8mb4_0900_ai_ci’” issue.

    Limitations of UTF8 Character Set:

    Changing the character set from utf8mb4 to utf8 in MySQL is not inherently bad, but it may have some implications that you should consider before making the change:

    • Limited Unicode support: The utf8 character set in MySQL only supports a limited range of Unicode characters, specifically the Basic Multilingual Plane (BMP), which includes characters from the Unicode code points U+0000 to U+FFFF. In contrast, utf8mb4 supports the full range of Unicode characters, including supplementary characters (code points U+10000 to U+10FFFF), such as emojis and certain rare symbols or scripts. If you need to store these supplementary characters in your database, you should use utf8mb4.
    • Data loss or corruption: If your existing data contains characters outside of the BMP, converting the character set from utf8mb4 to utf8 may result in data loss or corruption. These characters will be replaced with the Unicode replacement character (U+FFFD) during the conversion process.
    • Index length limitations: The utf8 character set uses less storage space (up to 3 bytes per character) compared to utf8mb4 (up to 4 bytes per character). This may help you work around index length limitations, especially with older versions of MySQL. However, you should be aware of the trade-offs in terms of Unicode support.

    FAQ MySQL
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    Using .env File in FastAPI

    Setting Up Email Notifications for Django Error Reporting

    How to Enable Apache Rewrite (mod_rewrite) Module

    View 28 Comments

    28 Comments

    1. Paul Alexandrow on September 14, 2023 9:13 pm

      Worked for me, thanks!

      Reply
    2. trustedmate on July 23, 2023 6:39 pm

      Thanks a lot..great solution

      Reply
    3. Liana on July 12, 2023 4:40 am

      thanks for info

      Reply
    4. Mina on April 12, 2023 2:51 pm

      Excellent! It worked for me…Thanks !

      Reply
    5. Arsh on February 21, 2023 5:39 pm

      Thanks mate. It solved my issue. I tried solutions from StackOverflow but none of them helped. Only this one helped.

      Reply
    6. Mohsin on January 10, 2023 8:29 am

      I installed `brew reinstall gnu-sed` and used `gsed` on mac with the same commands. Worked.

      Reply
    7. deepa on December 29, 2022 6:06 am

      its really very helpful thank you

      Reply
    8. Daniël Riezebos on November 16, 2022 10:40 am

      This helped me greatly, thank you very much!

      Reply
    9. Rez on October 27, 2022 7:49 am

      It worked. Thank you.

      Reply
    10. Mujeeb on October 18, 2022 1:50 pm

      yes it really help me alot

      Reply
    11. DHyey on October 6, 2022 2:53 pm

      Thanx! It helped me..

      Reply
    12. Lukas on August 27, 2022 6:07 pm

      This post should be removed, this has a high risk of causing data loss. Moving from utf8 to utf8mb4 doesn’t cause data loss, but moving from utf8mb4 to utf8 removes a byte of data, which is VERY dangerous. Please take this down.

      Reply
      • c on April 16, 2023 2:21 pm

        THANK YOU! I was going to point this out. Please DO NOT use utf8 as charset to replace utf8mb4.

        Reply
    13. samar on July 30, 2022 12:00 pm

      Thanks a lot. It worked for me

      Reply
    14. Nirav on June 25, 2022 5:17 am

      thanks, it is work for me

      Reply
    15. jordi on June 23, 2022 10:00 am

      thanks work!

      Reply
    16. Malvin on June 14, 2022 11:58 am

      And you will loose everything requiring the fourth byte. Emojis for example. But who cares? Ugly hacks made by people that don’t what they are talking about obviously make the world go round. Because using a current version of mySQL would be too much of burden, I guess…

      Reply
    17. John on May 22, 2022 9:35 pm

      This is great! It works for me!

      Reply
    18. ray on March 15, 2022 9:26 pm

      Thank you this was helpful. Just a minor correction on the intro paragraph utf8mb4_general_ci should be utf8_general_ci

      Reply
    19. Georgie on February 22, 2022 7:30 pm

      In my case I also had to add this command:
      “`
      sed -i ‘s/utf8mb4/utf8/g’ backup.sql
      “`

      Reply
    20. Elmer on February 16, 2022 5:53 am

      Funciono Perfecto!

      Gracias!

      Reply
    21. David on January 11, 2022 2:05 pm

      Thank you!!

      Reply
    22. Israel Guido on November 12, 2021 8:12 pm

      On Mac OSX this is correct:
      sed -e ‘s/utf8mb4_0900_ai_ci/utf8_general_ci/g’ oldFile.sql > newFile.sql

      Reply
    23. Felipe on November 9, 2021 5:15 pm

      Thank you!

      Reply
    24. Steve on September 2, 2021 7:38 am

      Thanks for this, I had to leave the CHARSET=utf8mb4 to get my import to work

      Reply
      • zied on April 27, 2022 1:42 pm

        me too

        Reply
      • Malvin on June 14, 2022 12:00 pm

        Otherwise you would be loosing data, like Emojis.

        Reply
    25. michael on August 14, 2021 1:04 am

      You’re a life-saver!

      Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • Using .env File in FastAPI
    • Setting Up Email Notifications for Django Error Reporting
    • How to Enable Apache Rewrite (mod_rewrite) Module
    • What are Microservices?
    • Variable Expansion in ZSH
    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.