ERROR 1115 42000 Unknown character set utf8mb4

Question

I have a MySQL dump  which I tried to restore with   mysql -u username  -p password  --host  127 0 0 1  mysql db  lt  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

User · Answer

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

User · Answer

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  gt  updated-mysql-data sql sed  s utf8mb4 utf8 g  original-mysql-data sql  gt  updated-mysql-data sql   Done

User · Answer

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

User · Answer

You can try   Open sql file by text editor find and replace all  utf8mb4 to utf8   Import again

User · Answer

Open your mysql file any edit tool  find         40101 SET NAMES utf8mb4       change        40101 SET NAMES utf8       Save and upload ur mysql

User · Answer

This can help   mysqldump --compatible mysql40 -u user -p DB  gt  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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to sql

Examples related to character-encoding

Examples related to utf8mb4