Character set & #39; utf8mb4 & #39; is not a compiled character set, utf8mb4compiled

Character set 'utf8mb4' is not a compiled character set, utf8mb4compiled

A Character set error occurred during a recent MySQL Data migration. The prompt is "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 describes the handling process and solutions for your reference.

 

1. error message
SHELL> mysqlbinlog -- database = bs_salary -- stop-datetime = "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 is not specified in the '/usr/share/mysql/charsets/Index. xml' file

 

2. Analysis and Solution
A. Error Analysis
According to the error message, the utf8mb4 character set is not compiled, but in fact it is useless.
Second, the/usr/share/mysql/charsets/Index. xml file does not specify the utf8mb4 character set. You need to check the Character Set Directory settings.

B. Character Set check
First, check the character set settings of the New and Old environments, both of which are UTF8, as shown below:
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

The path settings for character sets are inconsistent with the error message. Try the modifier configuration file.
SHELL> tail-3/etc/my. cnf
[Client]
# Default-character-set = utf8
Character-sets-dir =/app/soft/mysql/share/charsets/

The following error occurs again after the configuration file is modified. Therefore, the character set path is not the main cause.
Mysql: Character set 'utf8mb4' is not a compiled character set and is 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 the utf8mb4 character set to Index. xml.
Copy the utf8 configuration directly, change it to utf8mb4, and add it to/app/soft/mysql/share/charsets/Index. xml for troubleshooting.
<Charset name = "utf8mb4">
<Family> Unicode </family>
<Description> UTF-8 Unicode </description>
<Alias> UTF-8 </alias>
<Collation name = "utf8_general_ci" id = "33">
<Flag> primary </flag>
<Flag> compiled </flag>
</Collation>
<Collation name = "utf8_bin" id = "83">
<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 that is not compiled into your binary, you might run into the following problems:

• Your program uses an incorrect path to determine where the character sets are stored (which is typicallyshare/mysql/charsetsOrshare/charsetsDirectory under the MySQL installation directory). This can be fixed by using--character-sets-dirOption when you run the program in question. For example, to specify a directory to be used by MySQL client programs, list it in[client]Group of your option file. The examples given here show what 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 that cannot be loaded dynamically. In this case, you must recompile the program with support for the character set.

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

• The character set is a dynamic character set, but you do not have a configuration file for it. In this case, you 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 message. The file is namedIndex.xmlAnd the message is:

  Character set 'charset_name' is not a compiled character set and is notspecified in the '/usr/share/mysql/charsets/Index.xml' file

  To solve this problem, you shocould either get a new index file or manually add the name of any missing character sets to the 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, whencharacter_set_systemDiffers fromcharacter_set_serverOrcharacter_set_client, And you input characters manually (as database object identifiers, column values, or both), these may be displayed incorrectly in output from the client or the output itself may be formatted incorrectly. in such cases, starting the mysql client--default-character-set=system_character_set-That is, setting the client character set to match the system character set-shocould fix the problem.

From the above description, the suggested path is inconsistent with that of character-sets-dir. After the configuration file is modified, it is consistent. Finding the default Index. xml configuration file is caused by multiple versions. Second, it is unclear why you need to add the utf8mb4 configuration to the index. xml file. This character set is not used at all. I guess I want to check it all. Although it is available, it still feels less reliable.