MySQL collation complexities leads to backup incompatibilities

Previously, utf8mb4_general_ci was the default collation. Because the utf8mb4_0900_ai_ci collation is now the default, new tables have the ability to store characters outside the Basic Multilingual Plane by default. Emojis can now be stored by default.

— https://www.monolune.com/articles/what-is-the-utf8mb4_0900_ai_ci-collation

I got hit by this issue a couple of times and now I learn that utf8mb4_0900_ai_ci is the new default.

Backup and Migrate should not fail here and automatically convert default collation from utf8mb4_0900_ai_ci and utf8mb4_general_ci and vice versa. The implementation might not be simple. Automated conversion should be limited only to the former and new default MySQL collations.

MySQL snippets that may help

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# The above code is for 'VARCHAR' column. The exact statement depends on the column type, maximum length, and other properties.

Ther is also the possibility of doing a database backup directly to another collation using the --default-character-set flag.

Thank you for this solution. Just moved over to aws/rds using 8.0.35 last week. Found this issue when trying to restore to local dev to do some work. Had 379 tables using utf8mb4_0900_ai_ci. Followed the steps above and it restored successfully.

Before going live, we restored from old hosting to aws with no issues. So does that mean it translates/converts from utf8mb4_general_ci to utf8mb4_0900_ai_ci with no issue, but not the opposite direction?

Being bold here. I am closing this ticket as there are two solutions.

Solution 1: Make sure both MySQL server are using the same version. E.g. MySQL 8.x. There should not be any collation collision when running the same major version.

Solution 2:: Use a workaround with search/replace in a text editor. See comment #7 above.

Note that with Solution 2 you may need to unzip the file, search/replace the collection, save the file and gzip it for Backup and Migrate to process it correctly.