[mysql] ERROR 1115 (42000): Unknown character set: 'utf8mb4'

I have a MySQL dump, which I tried to restore with:

mysql -u"username" -p"password" --host="127.0.0.1" mysql_db < mysql_db

However, this threw an error:

ERROR 1115 (42000) at line 3231: Unknown character set: 'utf8mb4'

This is lines 3231-3233:

/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;

I am using MySQL 5.1.69. How can I solve this error?

You can try:

Open sql file by text editor find and replace all

utf8mb4 to utf8

Import again.

Just open your sql file with a text editor and search for 'utf8mb4' and replace with utf8.I hope it would work for you

I am answering the question - as I didn't find any of them complete. As nowadays Unknown character set: 'utf8mb4' is quite prevalent as lot of deployments have MySQL less then 5.5.3 (version in which utf8mb4 was added).

The error clearly states that you don't have utf8mb4 supported on your stage db server.

Cause: probably locally you have MySQL version 5.5.3 or greater, and on stage/hosted VPS you have MySQL server version less then 5.5.3

The utf8mb4 character sets was added in MySQL 5.5.3.

utf8mb4 was added because of a bug in MySQL's utf8 character set. MySQL's handling of the utf8 character set only allows a maximum of 3 bytes for a single codepoint, which isn't enough to represent the entirety of Unicode (Maximum codepoint = 0x10FFFF). Because they didn't want to potentially break any stuff that relied on this buggy behaviour, utf8mb4 was added. Documentation here.

From SO answer:

Verification: To verify you can check the current character set and collation for the DB you're importing the dump from - How do I see what character set a MySQL database / table / column is?

Solution 1: Simply upgrade your MySQL server to 5.5.3 (at-least) - for next time be conscious about the version you use locally, for stage, and for prod, all must have to be same. A suggestion - in present the default character set should be utf8mb4.

Solution 2 (not recommended): Convert the current character set to utf8, and then export the data - it'll load ok.

maybe whole database + tables + fields should have the same charset??!

i.e.

CREATE TABLE `politicas` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Nombre` varchar(250) CHARACTER SET utf8 NOT NULL,
  -------------------------------------^here!!!!!!!!!!!
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  -------------------------------------------------^here!!!!!!!!!

As some suggested here, replacing utf8mb4 with utf8 will help you resolve the issue. IMHO, I used sed to find and replace them to avoid losing data. In addition, opening a large file into any graphical editor is potential pain. My MySQL data grows up 2 GB. The ultimate command is

sed 's/utf8mb4_unicode_520_ci/utf8_unicode_ci/g' original-mysql-data.sql > updated-mysql-data.sql
sed 's/utf8mb4/utf8/g' original-mysql-data.sql > updated-mysql-data.sql

Done!

Open your mysql file any edit tool

find

/*!40101 SET NAMES utf8mb4 */;

change

/*!40101 SET NAMES utf8 */;

Save and upload ur mysql.

This can help:

mysqldump --compatible=mysql40 -u user -p DB > dumpfile.sql

PHPMyAdmin has the same MySQL compatibility mode in the 'expert' export options. Although that has on occasions done nothing.

If you don't have access via the command line or via PHPMyAdmin then editing the

/*!50003 SET character_set_client  = utf8mb4 */ ; 

bit to read 'utf8' only, is the way to go.