MySQL Bugs: #86421: MYSQL CLIENT 8.0.2 GET WRONG CHARACTER SET WHEN CONNECTED TO SERVER 5.7

Bug #86421	MYSQL CLIENT 8.0.2 GET WRONG CHARACTER SET WHEN CONNECTED TO SERVER 5.7
Submitted:	23 May 2017 8:59
Reporter:	Bernt Marius Johnsen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	8.0.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a 5.7 client connects to a 5.7 server in a UTF-8 environment in a UTF-8 environment (e.g.
xterm), we get:

mysql> show variables like 'character%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /home/bernt/mysql/5.7/sql/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0,00 sec)

But when a 8.0.2 client connects to a 5.7 server, we get:

mysql> show variables like 'character%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | latin1                                    |
| character_set_connection | latin1                                    |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | latin1                                    |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /home/bernt/mysql/5.7/sql/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0,01 sec)

How to repeat:
Connect a 8.0.2 client to a 5.7 server and do

show variables like 'character%';

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19995
Server version: 5.5.52-0+deb8u1-log (Debian)

Hi Johnsen

My server version is 

5.5.52-0+deb8u1-log 

Even unable to type special characeters from mysql command line

Hi JOhnsen

now my problem is database already changed and data also changed, now I need to insert correct data into datbase.

mysql> SHOW GLOBAL VARIABLES LIKE '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.52              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.52-0+deb8u1-log |
| version_comment         | (Debian)            |
| version_compile_machine | x86_64              |
| version_compile_os      | debian-linux-gnu    |
+-------------------------+---------------------+
7 rows in set (0.29 sec)

First off, there are two sets of variables, GLOBAL and SESSION. Type "show global variables where variable_name like '%char%' \g" to see what I mean. Now repeat the command replacing "global" with "session". I just discovered something a little confusing; some accounts preferentially use GLOBAL variables while most use SESSION. "I think" this feature may only happen when the account in question has priv=super (a little hacking would confirm this). If your global variables are set to utf8 (or utf8mb4), and your client is returning latin1 but you expect utf8, then you will need to use a different character set option in your connect string. Or you could connect normally then always issue "set character_set_results = 'utf8';" as a first command. But here is the way to ensure all global and session variables use the same character set: add these lines to config file 'my.cnf':

[client] 
default-character-set=utf8 
[mysqld] 
character-set-server = utf8