Bug #92880 the character problem for mysqldump when deal the `mysql`.`event`'s name column
Submitted: 22 Oct 2018 14:15 Modified: 7 Nov 2018 9:23
Reporter: Peng Jiang JIANG Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: mysqlpump Command-line Client Severity:S3 (Non-critical)
Version: OS:Linux
Assigned to: CPU Architecture:Any

[22 Oct 2018 14:15] Peng Jiang JIANG
Description:
i create a event with character_set_client=latin1 .
this event's name is "新年";
----
create definer=CURRENT_USER event `新年` on schedule every 1 second  on completion preserve disable do call test_proce();

mysql> select hex(name) from event;                                                                             
+----------------------------+
| hex(name)                  |
+----------------------------+
| C3A6E28093C2B0C3A5C2B9C2B4 |
----

then i use mysqldump  to dump data to local  ,--default-character-set=binary 
last,i use mysqldump dump data to mysql ,--default-character-set=utf8mb4

mysql> select hex(name) from event;
+----------------------------------------------------------+
| hex(name)                                                |
+----------------------------------------------------------+
| C383C2A6C3A2E282ACE2809CC382C2B0C383C2A5C382C2B9C382C2B4 |

i find the sql file content:

/*!50106 DROP EVENT IF EXISTS `e2æS°å¹´''`  */;;   
/*!50003 SET character_set_client  = latin1 */ ;;
/*!50003 SET character_set_results = latin1 */ ;;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = '' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `e2æS°å¹´''` ON SCHEDULE EVERY 1 SECOND STARTS '2018
-09-27 10:30:22' ON COMPLETION PRESERVE DISABLE ON SLAVE DO call test_proce() */ ;;

when mysqldump dump data into mysql, the event name  have  utf8-> latin1 -> utf8 

"新年"   latin1 (E696B0E5B9B4) -> utf8(C3A6E28093C2B0C3A5C2B9C2B4)->binary(C3A6E28093C2B0C3A5C2B9C2B4)===latin1(C3A6E28093C2B0C3A5C2B9C2B4)->utf8(C383C2A6C3A2E282ACE2809CC382C2B0C383C2A5C382C2B9C382C2B4 )

the define of event table:

| event | CREATE TABLE `event` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `body` longblob NOT NULL,
  `definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `execute_at` datetime DEFAULT NULL,
  `interval_value` int(11) DEFAULT NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_executed` datetime DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` int(10) unsigned NOT NULL,
  `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events' |

the character_set_client column  value is  latin1_swedish_ci .

what mysqldump do :
    create table event ..
    insert into event ...
    drop event ...
    create event ...

and my event name is   wrong encoding ,although, the column of character_set_client is right value
 

How to repeat:
i think this is a bug,
[2 Nov 2018 16:59] Sinisa Milivojevic
HI,

You can not use latin1 character set for Chinese characters. Please, use the correct client character set and let us know whether you can repeat the problem. Using wrong character set is not a bug. Use the correct one and we shall analyse it.

Also, do let us know the version and release of the clients and server that you are using.
[5 Nov 2018 3:48] Peng Jiang JIANG
thanks for your suggestion.

i use gbk for Chinese characters to test;
repeat step:
1. set character  and prepare data 

mysql> show global variables like '%char%';
+--------------------------+------------------------------------------------------------------------------------------+
| Variable_name            | Value                                                                                    |
+--------------------------+------------------------------------------------------------------------------------------+
| character_set_client     | gbk                                                                                      |
| character_set_connection | gbk                                                                                      |
| character_set_database   | gbk                                                                                      |
| character_set_filesystem | binary                                                                                   |
| character_set_results    | gbk                                                                                      |
| character_set_server     | gbk                                                                                      |
| character_set_system     | utf8                                                                                     

create table event_table
(
id int(11) not null auto_increment primary key,
time datetime not null
) engine=innodb default charset=utf8;

delimiter //
drop procedure if exists test_proce//
create procedure test_proce()
begin
insert into event_table (time) values(now());
end//
delimiter ;

create definer=CURRENT_USER event `e2新年''` on schedule every 1 second  on completion preserve disable do call test_proce();

mysql> select * from event;
+-------+------------+-------------------+---------+------------+----------------+----------------+---------------------+---------------------+---------------+---------------------+------+----------+---------------+------------------------+---------+------------+-----------+----------------------+----------------------+----------------+-------------------+
| db    | name       | body              | definer | execute_at | interval_value | interval_field | created             | modified            | last_executed | starts              | ends | status   | on_completion | sql_mode               | comment | originator | time_zone | character_set_client | collation_connection | db_collation   | body_utf8         |
+-------+------------+-------------------+---------+------------+----------------+----------------+---------------------+---------------------+---------------+---------------------+------+----------+---------------+------------------------+---------+------------+-----------+----------------------+----------------------+----------------+-------------------+
| hello | e2新年'' | call test_proce() | root@%  | NULL       |              1 | SECOND         | 2018-11-05 11:13:46 | 2018-11-05 11:13:46 | NULL          | 2018-11-05 03:13:46 | NULL | DISABLED | PRESERVE      | NO_ENGINE_SUBSTITUTION |         |      86829 | SYSTEM    | gbk                  | gbk_chinese_ci       | gbk_chinese_ci | call test_proce() |
+-------+------------+-------------------+---------+------------+----------------+----------------+---------------------+---------------------+---------------+---------------------+------+----------+---------------+------------------------+---------+------------+-----------+----------------------+----------------------+----------------+-------------------+
1 row in set (0.00 sec)

mysql> select hex(name) from event; 
+----------------------------+
| hex(name)                  |
+----------------------------+
| 6532E98F82E69DBFE58BBE2727 |
+----------------------------+

by now ,the data in db is right.

2.backup data  with binary  character by mysqldump

3.import data to another db with utf8mb4 by mysqldump

4.check data  in new db , set names gbk 
  
Database changed
mysql> select hex(name ) from event;
+----------------------------------+
| hex(name )                       |
+----------------------------------+
| 6532E996BAE5829BE6BE98E98D953F27 |

mysql> select * from event;
+-------+--------------+-------------------+---------+------------+----------------+----------------+---------------------+---------------------+---------------+---------------------+------+--------------------+---------------+------------------------+---------+------------+-----------+----------------------+----------------------+----------------+-------------------+
| db    | name         | body              | definer | execute_at | interval_value | interval_field | created             | modified            | last_executed | starts              | ends | status             | on_completion | sql_mode               | comment | originator | time_zone | character_set_client | collation_connection | db_collation   | body_utf8         |
+-------+--------------+-------------------+---------+------------+----------------+----------------+---------------------+---------------------+---------------+---------------------+------+--------------------+---------------+------------------------+---------+------------+-----------+----------------------+----------------------+----------------+-------------------+
| hello | e2鏂板 | call test_proce() | root@%  | NULL       |              1 | SECOND         | 2018-11-05 11:25:05 | 2018-11-05 11:25:05 | NULL          | 2018-11-05 03:13:46 | NULL | SLAVESIDE_DISABLED | PRESERVE      | NO_ENGINE_SUBSTITUTION |         |       1242 | SYSTEM    | gbk                  | gbk_chinese_ci       | gbk_chinese_ci | call test_proce() |
+-------+--------------+-------------------+---------+------------+----------------+----------------+---------------------+---------------------+---------------+---------------------+------+--------------------+---------------+------------------------+---------+------------+-----------+----------------------+----------------------+----------------+-------------------+

the event name is `e2鏂板 `  not 'e2新年'.

last ,Positioning result:

if mysqldump just import data back to db with `create table event ..` and `insert into event values(...)` ,the data in db is right .
but mysqldump will truncate event table after insert . and   execute  `create definer=CURRENT_USER event `e2新年''` on schedule every 1 second  on completion preserve disable do call test_proce();`  with gbk character.
this time will have a Character conversion  for gbk -> utf8

'新年' utf8 is  'E696B0E5B9B4'  

when i first create event; it store in db is   'E98F82E69DBFE58BBE' (gbk(E696B0E5B9B4)-> utf8 ) ,mysqldump to file,it store with E98F82E69DBFE58BBE in file.

when i dump back to db, mysqldump read from file with `create definer=CURRENT_USER event 'E98F82E69DBFE58BBE ' ......`  ,(gbk(E98F82E69DBFE58BBE)-> utf8 ) ,it is stored in db with  E996BAE5829BE6BE98E98D 

mysql  Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:          41
Current database:       mysql
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.28-cdb2016-log 20180122
Protocol version:       10
Server characterset:    gbk
Db     characterset:    gbk
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               20163
Uptime:                 37 min 23 sec
[5 Nov 2018 13:46] Sinisa Milivojevic
Hi,

If you wish to import a dump into the utf8mb4 format, you have to export it in that format. 

This is documented in our Reference Manual.

Next, 5.1 and 5.5 are no longer supported, so you could try some supported version.
[6 Nov 2018 1:44] Peng Jiang JIANG
in fact,this problem is not associated to the character for mysqldump import.

i analysis the dump file ,find the mysqldump recreate event with next content:

--
-- Dumping events for database 'hello'
--
/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;
/*!50106 DROP EVENT IF EXISTS `e2m~O~Bm~]1m~K */;A\', 
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = gbk */ ;;
/*!50003 SET character_set_results = gbk */ ;;
/*!50003 SET collation_connection  = gbk_chinese_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`%`*/ /*!50106 EVENT `e2m~O~Bm~]1m~K ON SCHEDULE EVERY 1 SECOND STARTS '2018-11-05 11::can13:46' ON COMPLETION PRESERVE DISABLE ON SLAVE DO call test_proce() */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
DELIMITER ;
/*!50106 SET TIME_ZONE= @save_time_zone */ ; 

not matter the character what i use,the event always use character_set_client  = gbk to import , but the event name(ut8) read from file will  have a Character conversion from  gbk(mysqldump think so) -> utf8

this problem i have make sure,but i don't know how to describe it clearly .
[6 Nov 2018 1:48] Peng Jiang JIANG
event i use binary import back to db,this problem also exist;
[6 Nov 2018 12:58] Sinisa Milivojevic
HI,

As I wrote before, export data in utfmb4 and you will import it in utfmb4, without any problems. Otherwise, you will have to do the translation. Also, specifying character set in the DDL will override default charsets and collations.
[6 Nov 2018 13:58] Peng Jiang JIANG
thanks for your reply,

i am very agree "As I wrote before, export data in utfmb4 and you will import it in utfmb4, without any problem" . and i will to do by the way.

"specifying character set in the DDL will override default charsets and collations." but the problem happen at the time , the specifying character set in the DDL is mysqldump write to dump , so that the event create with specifying character set when i 
 import by mysqldump ,not utf8mb4. 

and my event name(read from file with utf8) will be think as specifying character conversion to utf8  (store character)
[6 Nov 2018 14:17] Peng Jiang JIANG
i read  mysqldump.c file,

<code>
    if (switch_character_set_results(mysql, "binary"))
      DBUG_RETURN(1);

    while ((event_list_row= mysql_fetch_row(event_list_res)) != NULL)
    {
      event_name= quote_name(event_list_row[1], name_buff, 0);
      DBUG_PRINT("info", ("retrieving CREATE EVENT for %s", name_buff));
      my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE EVENT %s", 
          event_name);
</code>

<code>
  switch_cs_variables(sql_file, delimiter,
                                row[4],   /* character_set_client */
                                row[4],   /* character_set_results */
                                row[5]);  /* collation_connection */
          }
<code/>

mysqldump  query create ddl with binary ,and set character_set_client  specifying  character set before ddl in dump file . if i use mysqldump import this file ,it will  cause a conversion
[6 Nov 2018 14:20] Sinisa Milivojevic
Sorry, but it is very hard to understand you.

You are reporting here actually just a problem with a title of the event ????
[7 Nov 2018 9:23] Peng Jiang JIANG
i'm not sure the influence if event's name changed. as a  tester , i just think i should keep the data quite right .  i will  ask my colleague try to repaire the problem .

Thank you for your answer . 
have  a  good  morning.