| 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: | |
| Category: | MySQL Server: mysqlpump Command-line Client | Severity: | S3 (Non-critical) |
| Version: | OS: | Linux | |
| Assigned to: | CPU Architecture: | Any | |
[2 Nov 2018 16:59]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.
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,