Character Set Conflicts and Incorrect String Value

Character Set Conflicts

中文摘要：
Validate Mysql Database Backup得出问题。Analyze Problems in Mysql DB Backup定位问题。Correct Conflicts During Mysql DB Backup解决问题。Miscellaneous Problems in Mysql Backup Correction记录了各种意外及tricks。但是还没完，字符集问题来了。这部分工作于2017年12月完成。
关键词：Data truncated, character set conflict, incorrect string value, show_compatibility_56
When I import the corrected data into DB2, error occured: Data truncated for column loan_type at row 3793 4504 4743 5108 6516,Data truncated for column 'LOAN_TYPE,Data truncated for column 'USAGE'. Those records that were truncated are all messy characters.

show variables like ‘character%’;

This tells me information about character set. use DB; in the following table stands for situations or the environment we are in after executing the command use DB;

\	Database	Server
T470	Utf8	Utf8
DB2	Latin1	Latin1
T470(use DB;)	Utf8	Utf8
DB2 (use DB;)	Utf8	Latin1

set character_set_database=utf8; 
set character_set_server=utf8;

Naturally, one will use set to change the value of character set, but this doesn’t work for my problem. I changed the value of default character set in my.ini, restart the sql service, it doesn’t work too. I even tried alter database DB2 default character set=utf8;, then restart the sql service, those messy characters still there when I send queries to particular table. MySQL之对数据库库表的字符集的更改 gives me an explanation on why alter won’t work. Here are some heuristic info in mysql 5.7 doc:

character_set_database: The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.
.
Replication and Character Sets: If the master has databases with a character set different from the global character_set_server value, you should design your CREATE TABLE statements so that they do not implicitly rely on the database default character set. A good workaround is to state the character set and collation explicitly in CREATE TABLE statements.

So, normally speaking, there shouldn’t be problems if a defaut database’s (database being used) character set is set to utf8 (which contains almost every word in Chinese) already, whatever the server character set is.
But the most sarcastic and annoying thing is that I suddenly found that the messy character disappeared after a usual restart of server. I even couldn’t recall which step is the critical one leading to this ridiculous recover.

Incorrect String Value: '\xD6\xD0\xB9…

This is a isolated subject and have nothing to do with character set discussed above. I put this down here just to record a problematic case when I operate mysql.
When I log on the server, recheck the character set, a wierd message popped into my eyes.

mysql> show variables like 'character set%';
Empty set, 1 warning (0.07 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                      |
+---------+------+----------------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xD6\xD0\xB9\xFA\xB1\xEA...' for column 'VARIABLE_VALUE' at row 496 |
+---------+------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I found VARIABLE_VALUE in the information_schema.global_variables table,

select * from information_schema.global_variables limit 1;

There’s an another warning: The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'
Here’s the mysql 5.7 doc:¹

you can use the show_compatibility_56 system variable, which affects whether MySQL 5.6 compatibility is enabled with respect to how system and status variable information is provided by the INFORMATION_SCHEMA and Performance Schema tables, and also by the SHOW VARIABLES and SHOW STATUS statements.

So put it in lain words, show_compatibility_56 is a variable controlling compatibility with mysql 5.6. To solve Incorrect String Value: '\xD6\xD0\xB9… problem, just enter the below and punch enter (You have to reset it on again when logging onto the server next time, the variable is not in my.ini):

set global show_compatibility_56 =on;

Reference

---

1. MySQL 5.7 Reference Manual ↩︎