Home Website Tutorials Fix the Unknown collation: utf8mb4_0900_ai_ci error

Fix the Unknown collation: utf8mb4_0900_ai_ci error

by Thạch Phạm
Published: Last Updated on
A+A-
Reset

During the process of moving the database from the old server to the new server and importing it, you will encounter an error saying Unknown collation: utf8mb4_0900_ai_ci as shown below:

2022 04 10 23.00.24
Fix the Unknown collation: utf8mb4_0900_ai_ci error

The main and most common cause is that the MySQL/MariaDB versions of the 2 servers are not the same, so when importing to the new server, the collation ID doesn’t match.

You can fix this error in 2 methods:

Method 1: Change MySQL/MariaDB version

In this way, you need to downgrade or upgrade the MySQL/MariaDB version on the new server to the same version as the old server to resolve the issue. However, there is a disadvantage that changing the version is very complicated and the developer does not allow you to downgrade.

You can just uninstall and reinstall. And this will be very risky for the server that has and has an active website.

Method 2: Change the content of the coding table of the Database

In this way, you will change the CHARSET and COLLATE of the database, it will be much simpler and less risky.

  • Change utf8mb4_0900_ai_ci to utf8mb4_general_ci
  • Change CHARSET=utf8mb4 to CHARSET=utf8

On Linux systems, there is a strength that you can change the contents of a file with a command line. Here, I use the command sed

Note:

  • Command 1 will look into the backup.sql file. If there is a line utf8mb4_0900_ai_ci, it will be automatically replaced with utf8_general_ci
  • Command 2: will look into the backup.sql file. If there is a line CHARSET=utf8mb4, it will be automatically replaced with CHARSET=utf8

Note: Replace backup.sql with your database file name.

AZDIGI Tutorial
Lệnh 1: sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' backup.sql  
Lệnh 2: sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql  
    

After running the command, you can use that Database to import again.

Wishing you success!

If you need assistance, you can contact support in the ways below:

  • Hotline 247: 028 888 24768 (Ext 0)
  • Ticket/Email: You use the email to register for the service and send it directly to: support@azdigi.com .
  • Refer to Hosting/VPS service here!
Đánh giá

Tham gia nhóm hỗ trợ Server - Hosting

Tham gia nhóm Hỗ trợ Server - Hosting & WordPress để cùng nhau hỏi đáp và hỗ trợ các vấn đề về WordPress, tối ưu máy chủ/server.

Tham gia ngay

Bài viết cùng chuyên mục

AZDIGI – Không chỉ là đơn vị hàng đầu trong lĩnh vực Web Hosting và Máy chủ, chúng tôi mong muốn mang lại những kiến thức bổ ích nhất và luôn cập nhật thường xuyên cho cộng đồng người đam mê thiết kế website, công nghệ,…

Vui lòng không sao chép nội dung nếu chưa xin phép. Designed and Developed by PenciDesign