| Bug #70270 | mysqldump's bug with binary character set | ||
|---|---|---|---|
| Submitted: | 8 Sep 2013 14:54 | Modified: | 8 Sep 2013 23:10 |
| Reporter: | yin ye | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.1.54, 5.6.12 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Sep 2013 18:00]
Peter Laursen
thre is a problem wiht the charset I belive - waht is alos verified here: http://bugs.mysql.com/bug.php?id=67739 Now wheb trying a 3rd party GUI tool (SQLyog) after CREATE TABLE .. CHARSET=gbk; INSERT INTO t1 VALUES(1,1,'xxxx'); .. I export with SQLyog (to an utf8 encoded file on Windows) and import with same to a new table `t2` (edit all statements in the dump accordingly). And I get the error "Error Code: 1062 - Duplicate entry '1' for key 'PRIMARY'". And there is *ABSOLUTELY NOT* any duplicate entry anywere. The CREATE TABLE statement succeeded, but the INSERT failed. The error occured with an empty table. (wiht SQLyog there is no option to set the default client character set (it is always utf8). All this maybe related to the 'wordprocessor-stile beautiful double-quotes' you use in the default.
[8 Sep 2013 18:17]
Peter Laursen
I posted another report here: http://bugs.mysql.com/bug.php?id=70271 Probably the underlying problems is the same (a problem the charset as scuh), but symptoms are different. MySQL supporters can merge if their analysis finds that they are duplicates.
[8 Sep 2013 18:22]
Peter Laursen
Added a reference to thsi bug in http://bugs.mysql.com/bug.php?id=67739
[8 Sep 2013 23:10]
MySQL Verification Team
Thank you for the bug repor.
miguel@TikalVM 5.6]$ bin/mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14-debug Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `t1`(
-> `iNetbarId` int(11) NOT NULL DEFAULT '0',
-> `iUin` bigint(20) NOT NULL DEFAULT '0',
-> `vNetbarName` varchar(80) NOT NULL DEFAULT '“-”',
-> PRIMARY KEY (`iNetbarId`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> insert into t1 values(1,1,'xxxx');
Query OK, 1 row affected (0.02 sec)
mysql> exit
Bye
[miguel@TikalVM 5.6]$ bin/mysqldump --default-character-set=binary test t1 > t1.sql
[miguel@TikalVM 5.6]$ bin/mysql test < t1.sql
ERROR 1067 (42000) at line 25: Invalid default value for 'vNetbarName'
[miguel@TikalVM 5.6]$
Description: For a table with gbk charset, then dump with --default-character-set=binary.When dump in with mysql, raise error of "Invalid default value". How to repeat: CREATE TABLE `t1`( `iNetbarId` int(11) NOT NULL DEFAULT '0', `iUin` bigint(20) NOT NULL DEFAULT '0', `vNetbarName` varchar(80) NOT NULL DEFAULT '“-”', PRIMARY KEY (`iNetbarId`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk; insert into t1 values(1,1,'xxxx'); >mysqldump -S /tmp/mysql_20120.sock --default-character-set=binary test t1 > t1.sql >mysql -S /tmp/mysql_20120.sock test < t1.sql ERROR 1067 (42000) at line 25: Invalid default value for 'vNetbarName' Suggested fix: diff --git a/client/mysqldump.c b/client/mysqldump.c index 07cf88a..f18d89f 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -37,7 +37,7 @@ */ #include <my_global.h> #include <my_sys.h> @@ -2455,7 +2455,14 @@ static uint get_table_structure(char *table, char *db, char *table_type, fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" : "/*!40101 SET @saved_cs_client = @@character_set_client */;\n" "/*!40101 SET character_set_client = utf8 */;\n" + "/*!40101 SET @saved_cs_connection = @@character_set_connection */;\n" + "/*!40101 SET character_set_connection = utf8 */;\n" "%s;\n" + "/*!40101 SET character_set_connection = @saved_cs_connection */;\n" "/*!40101 SET character_set_client = @saved_cs_client */;\n", row[1]);