[Solved] ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'
I was working on the server where a migration went wrong. The client asked me to migrate a MailWizz installation back to their original server where they were running MariaDB v10.0 on Ubuntu 16.04.
My task was to migrate MailWizz installation from Ubuntu 22.04 server which was running MySQL v8.0.
I came across following error when I tried to restore the database from MySQL v8.0 to MariaDB v10.0.
ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'
utf8mb4_0900_ai_ci wasn't supported in MariaDB v10.0. I had to change it to utf8mb4_unicode_ci in the database backup file. I should be able to import it to MariaDB v10.0 without issue. I decided to use sed to search and replace.
Following was the command I used to search and replace with sed.
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' backup.sql
I successfully imported the modified database dump file after this modification.