set: 'utf8mb4' in mysql

Posted in MySql - Last updated Jun. 25, 2015

When attempting to load a database dumped with mysqldump into an older MySQL database, I got the error message ERROR 1115 (42000) : Unknown character set: 'utf8mb4' in mysql.

What's utf8mb4?

utf8mb4 is a utf8 character set, which was added to MySQL in version 5.3.3, that fully supports unicode. Read this post by Mathias Bynens for more information about the difference between to the two character sets with MySQL.

How do I solve the problem?

If you dumped a table/database that supports utf8mb4 then you need to load it back into a MySQL database that is at least version 5.3.3. If you try to do it in an older version, you're going to get the error message I gave in the opening paragraph.

Let's say you dumped the database like this:

mysqldump -u [username] -p [database] > database.sql

You can then run "sed" against it to change all the instances of utf8mb4 to utf8 like this:

sed s/utf8mb4/utf8/g database.sql > database.sed.sql

It'll run pretty fast; doing this on a 500MB file only took maybe 10 seconds on the server I was doing it on. Note that it will replace all instances of utf8mb4 with utf8. So, for example, if I'd dumped the table containing this post then all instances in the content would also have changed.

You can probably modify the sed expression to work out all the various ways the character set is set in the file and run those, avoiding modifying anything else, but you still may have issues with character encoding being broken - just a final warning :)

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Installing Fail2ban

  Installing Fail2ban Installing Fail2ban on Ubuntu Server 16.04 is simple. Run the following...

How to display sockets summary and all open network ports with ss command

How to display sockets summary with ss command # ss -s How to display all open network ports...

How to find a folder in Linux using the command line

  I am a new Linux user. How do I find files and folders in Linux using the bash command line?...

How to scan for viruses with ClamAV on Ubuntu

ClamAV There aren't many viruses made for Linux distributions and as such, most people who use...

How to setup a UFW firewall on Ubuntu 18.04 LTS server

How do I setup a firewall with UFW (uncomplicated firewall) on an Ubuntu Linux 18.04 LTS server...