How do I convert a MySQL database from utf8mb4 to utf8 encoding?

Question

What happened: I had my blog hosted on SiteGround and my plan expired. I created a full website backup first which contained all the files including the public_html folder adn the databases associated with my WordPress blog.

The issue I'm having now is that I can't upload my databases on my current host (000WebHost) because it won't read the utf8mb4 encoded mysql files.

The error I get is:

1273 - Unknown collation: 'utf8mb4_unicode_ci'

I looked for ways to convert utf8mb4 encoding to utf8 but it all requires running a MySQL query and 1. I can't do that in MyPHPAdmin because I can't upload the database in the first place and 2. I don't know how to run it in the command line

Any suggestions? Have you been through this before? Thanks

Answer 1

It is totally essential to use utf8mb4 for your site to be secure and bug free in all wordpress versions since 4.2 and probably before that. You need to upgrade your mysql software to a newer version, or change whatever is need in its configuration to support it.

Answer 2

Mark is right, if your host does not support utf8mb4 you should not use them.

However, you can run a search-and-replace inside your import file, look for "utf8mb4" and replace with "utf8". Note that this will not increase the index sizes meant to fit multibyte characters, and will also likely still fail (probably silently by stripping contents) on import if any of your post content contains multibyte characters, such as emoji 🍔.