ERROR 1115 (42000): Unknown character set: 'utf8mb4'
Solution 1
Your version does not support that character set, I believe it was 5.5.3
that introduced it. You should upgrade your mysql to the version you used to export this file.
The error is then quite clear: you set a certain character set in your code, but your mysql version does not support it, and therefore does not know about it.
According to https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html :
utf8mb4 is a superset of utf8
so maybe there is a chance you can just make it utf8, close your eyes and hope, but that would depend on your data, and I'd not recommend it.
Solution 2
You can try:
Open sql file by text editor find and replace all
utf8mb4 to utf8
Import again.
Solution 3
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.
Solution 4
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'sutf8
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.
Solution 5
Just open your sql file with a text editor and search for 'utf8mb4' and replace with utf8.I hope it would work for you
saravanakumar
Software Developer, practicing Java, JavaScript on Jboss. More info LinkedIn
Updated on August 28, 2020Comments
-
saravanakumar over 3 years
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?
-
saravanakumar about 10 yearsSimply changed utf8mb4 to utf8.It's working fine. Thanks @Nanne :-)
-
saravanakumar almost 10 yearsI already solve the problem, Thanks for your answer. This may help to others.
-
random_user_name about 8 yearsDefinitely does not work in my case to simply change to utf8 - ended up with funky / junk characters throughout.
-
Nanne about 8 yearsWell, obviously that could be the case -> your obvious sollutions is to re-encode your data from utf8mb4 to something your database supports OR to update your database to a version that supports your dataset.
-
Braham Dev Yadav almost 7 yearsyes in my case in localhost for old wamp it works when changed:
utf8mb4
toutf8
editing file and inported again to mysql -
Demiurg over 5 yearsReally bad solution as you may lose some data.
-
kloddant over 4 yearsHere's the linux command for that: sed -i 's/utf8mb4/utf8/g' dump.sql