Logo
  • Ubuntu
  • CentOS
  • Debian
  • Fedora
  • RedHat

[Solved] Unknown collation: utf8mb4_0900_ai_ci - DesignLinux

Jun 29 2021
designlinux 0 Comments
[Solved] Unknown collation: utf8mb4_0900_ai_ci

A collation means a predefined rules to compare and sort character strings in a database server. Generally, this error occurs during the database restoration on another server. In this guide, we will show you how to fix Unknown collation: utf8mb4_0900_ai_ci error.

Let us discuss the cause and solution about the issue:

Problem – Unknown collation: utf8mb4_0900_ai_ci#

When you migration any web application to another server, you might get the following error at the time of database restore. The collation id may difference based on MySQL version.

Error message:

Error 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Solution#

This type of error occurs when a MySQL destination server has older version than the source server. We found the solution and the the destination server doesn’t required database collation.

Edit the database backup file using your favorite text editor and replace utf8mb4_0900_ai_ci with utf8mb4_general_ci and CHARSET=utf8mb4 with CHARSET=utf8.

Replace below string:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

with:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Save and close the file.

In Linux systems, you can use the sed command to replace text in file directly.

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

All done. Now your database should restore successfully.

Conclusion#

I hope this guide helped you to fix Unknown collation: utf8mb4_0900_ai_ci issue.

Related

Tags: database, mariadb, mysql

LFCA: Learn Software Deployment Environments – Part 23

Prev Post

ReactOS Alternative to Windows – Review, and Installation

Next Post
Archives
  • January 2023
  • December 2022
  • November 2022
  • October 2022
  • September 2022
  • July 2022
  • June 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
Categories
  • AlmaLinux
  • Android
  • Ansible
  • Apache
  • Arch Linux
  • AWS
  • Backups
  • Bash Shell
  • Bodhi Linux
  • CentOS
  • CentOS Stream
  • Chef
  • Cloud Software
  • CMS
  • Commandline Tools
  • Control Panels
  • CouchDB
  • Data Recovery Tools
  • Databases
  • Debian
  • Deepin Linux
  • Desktops
  • Development Tools
  • Docker
  • Download Managers
  • Drupal
  • Editors
  • Elementary OS
  • Encryption Tools
  • Fedora
  • Firewalls
  • FreeBSD
  • FTP
  • GIMP
  • Git
  • Hadoop
  • HAProxy
  • Java
  • Jenkins
  • Joomla
  • Kali Linux
  • KDE
  • Kubernetes
  • KVM
  • Laravel
  • Let's Encrypt
  • LFCA
  • Linux Certifications
  • Linux Commands
  • Linux Desktop
  • Linux Distros
  • Linux IDE
  • Linux Mint
  • Linux Talks
  • Lubuntu
  • LXC
  • Mail Server
  • Manjaro
  • MariaDB
  • MongoDB
  • Monitoring Tools
  • MySQL
  • Network
  • Networking Commands
  • NFS
  • Nginx
  • Nodejs
  • NTP
  • Open Source
  • OpenSUSE
  • Oracle Linux
  • Package Managers
  • Pentoo
  • PHP
  • Podman
  • Postfix Mail Server
  • PostgreSQL
  • Python
  • Questions
  • RedHat
  • Redis Server
  • Rocky Linux
  • Security
  • Shell Scripting
  • SQLite
  • SSH
  • Storage
  • Suse
  • Terminals
  • Text Editors
  • Top Tools
  • Torrent Clients
  • Tutorial
  • Ubuntu
  • Udemy Courses
  • Uncategorized
  • VirtualBox
  • Virtualization
  • VMware
  • VPN
  • VSCode Editor
  • Web Browsers
  • Web Design
  • Web Hosting
  • Web Servers
  • Webmin
  • Windows
  • Windows Subsystem
  • WordPress
  • Zabbix
  • Zentyal
  • Zorin OS
Visits
  • 0
  • 259
  • 614,631

DesignLinux.com © All rights reserved

Go to mobile version