Character set ' Utf8mb4 ' is not a compiled Character set

Recently encountered a character set problem during a MySQL data migration, prompting for "Character set ' UTF8MB4 ' is not a compiled Character set". That is, the character set UTF8MB4 is not a compiled character set and is not specified in the Index.xml file. The following is the process and solutions for everyone to refer to.

1. Error hints
shell> mysqlbinlog--database=bs_salary--stop-datetime= "2014-12-15 8:24:48"/home/robin/mysql-bin.000399 \
> |mysql-uroot-p--force--database=salary_1215
Mysql:character set ' Utf8mb4 ' is not a compiled Character set and was not specified in the '/usr/share/mysql/charsets/inde X.xml ' File

2. Analysis and Solution
A, error analysis
From the error hints given, it is said that the UTF8MB4 character set is not compiled, and in fact the character set is not used at all.
Second, this file/usr/share/mysql/charsets/index.xml does not specify the UTF8MB4 character set, you need to check the character set directory settings.

B, Character set check
First check the new and old environment character set settings, both sides are UTF8, as follows
Mysql> Show variables like '%char% ';
+--------------------------+---------------------------------+
| variable_name | Value |
+--------------------------+---------------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /app/soft/mysql/share/charsets/|
+--------------------------+---------------------------------+
# Author:leshami
# Blog:Http://blog.csdn.net/leshami

C, server character set path settings

For path settings for character sets, inconsistent with error prompts, try modifier configuration file
Shell> tail-3/etc/my.cnf
[Client]
#default-character-set=utf8
character-sets-dir=/app/soft/mysql/share/charsets/

After you modify the configuration file, you encounter the following error message again, so the character set path is not the main reason
Mysql:character set ' Utf8mb4 ' is not a compiled Character set and was not specified in the '/app/soft/mysql/share/charsets /index.xml ' File
Shell>/APP/SOFT/MYSQL/SHARE/CHARSETS/INDEX.XML/APP/SOFT/MYSQL/SHARE/CHARSETS/INDEX.XML.BK

D. Add utf8mb4 character set to Index.xml
The configuration of the UTF8 is copied directly, and UTF8MB4 is added to the/app/soft/mysql/share/charsets/index.xml troubleshooting after the change.
<charset name= "UTF8MB4" >
<family>Unicode</family>
<description>utf-8 unicode</description>
<alias>utf-8</alias>
<collation name= "utf8_general_ci" id= ">"
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name= "Utf8_bin" id= ">"
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>

3, MySQL official explanation

Https://dev.mysql.com/doc/refman/5.5/en/charset-configuration.html

If you try to use a character set, then the compiled into your binary and you might run into the following problems:

• your program uses a incorrect path to determine where the Character sets was stored (which is typically the share/mysql/charsets or share/charsets directory under the MySQL installation directory). This can is fixed by using the --character-sets-dir option if you run the program in Questio N. For example, to specify a directory to being used by MySQL client programs, list it in the [c Lient] Group of your option file. The examples given here show what's the setting might look like for Unix or Windows, respectively:

  [client]character-sets-dir=/usr/local/mysql/share/mysql/charsets[client]character-sets-dir=" C:/Program Files/mysql/mysql Server 5.5/share/charsets "

• The character set is a complex character set this cannot be loaded dynamically. Must recompile the program with the character set.

  For Unicode character sets, you can define collations without recompiling by using LDML notation. See sections 10.4.4, "Adding a UCA Collation to a Unicode Character Set".

• The character set is a dynamic character set, and you don't have a configuration file for it. Should install the configuration file for the character set from a new MySQL distribution.

• If your character Set index file does not contain the name for the character set, your program displays an error Messa Ge. the file is named and the Index.xml message is:

  Character Set " charset_name is not a compiled Character set and was notspecified in the '/usr/share/mysql/charsets/index . xml ' File

  To solve this problem, you should either get a new index file or manually add the name of any missing character sets to th E current file.

You can force client programs to use specific character set as follows:

[client]default-character-set=charset_name

This is normally unnecessary. However, when character_set_system differs character_set_server by or character_set_client , and you input characters manually (as database object identifiers, column values, or both), these may is displayed incorrectly in output from the client or the output itself could be formatted INCO rrectly. In such cases, starting the MySQL client with --default-character-set=system_character_set -that are, setting the client character set to match the system characte R set-should Fix the problem.

Judging from the above description of my situation, the path of the hint is inconsistent with the Character-sets-dir road. By modifying the configuration file, the consistency is maintained. The default index.xml configuration file was found because of a multi-version issue. The second is not clear why to add utf8mb4 this configuration to the Index.xml file, simply useless to this character set. I guess I'll have to test it all over again. Although added is available, it still feels less reliable.

Character set ' UTF8MB4 ' is not a compiled Character set