MySQL Bugs: #70271: weird errors with CHARSET=gbk

Bug #70271	weird errors with CHARSET=gbk
Submitted:	8 Sep 2013 18:12	Modified:	9 Sep 2013 12:36
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.6.13	OS:	Windows (7)
Assigned to:		CPU Architecture:	Any

Description:
This is an offset from http://bugs.mysql.com/bug.php?id=70270. 

Using both
1) the 'mysql' command line client 'in unicode mode' (as created by the .msi installer in start menu
2) SQLyog 11.24
(the error in the 2 clients is exactly the same and occurs at exacly same point)

.. this simple table

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');

.. cannot be imported after exported to an SQL-dump.
But data can be inserted interactively (from uft8 clients - SQLyog at least!)

How to repeat:
CLI output: 

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.13-log MySQL Community Server (GPL)

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> use bla;
Database changed
mysql> /*
   /*> SQLyog Ultimate v11.24 (64 bit)
   /*> MySQL - 5.6.13-log : Database - bla
   /*> *********************************************************************
   /*> */
mysql> /*!40101 SET NAMES utf8 */;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> /*!40101 SET SQL_MODE=''*/;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' *
/;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE /*!32312 IF NOT EXISTS*/`bla` /*!40100 DEFAULT CHARACTER
SET utf8 */;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> USE `bla`;
Database changed
mysql>
mysql> /*Table structure for table `t2` */
mysql>
mysql> DROP TABLE IF EXISTS `t2`;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE TABLE `t2` (
    ->   `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.03 sec)
mysql>
mysql> /*Data for the table `t2` */
mysql>
mysql> insert  into `t1`(`iNetbarId`,`iUin`,`vNetbarName`) values (1,1,'xxxx');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>
mysql> /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
Query OK, 0 rows affected (0.00 sec)
mysql> /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
Query OK, 0 rows affected (0.00 sec)
mysql> /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
Query OK, 0 rows affected (0.00 sec)
mysql>

Note:
mysql> insert  into `t1`(`iNetbarId`,`iUin`,`vNetbarName`) values (1,1,'xxxx');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

.. at a time when the table is still completely empty.

Suggested fix:
Some issue with "word-processor-style beautiful quotes" in gbk charset?

dump as created by SQLyog - as posting in a web form may corrupt rare characters

Attachment: gdk.sql (application/octet-stream, text), 1.04 KiB.

added a refernce to this bug in http://bugs.mysql.com/bug.php?id=67739

Let me add that I am 110% confident that SQLyog can be replaced with any UTF client (Workbench on any platform, BASH on Linux) and the problem reported will still be reproducible.

(BTW: I also found this: http://forums.mysql.com/read.php?103,379624,379624)

Thank you for the bug report. I am a bit confuse regarding the How to repeat instructions, first command in Unicode client is:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use bla;

Which was the state of bla databse (table, data) ?

Some lines after you printed:

mysql> CREATE DATABASE /*!32312 IF NOT EXISTS*/`bla` /*!40100 DEFAULT CHARACTER
SET utf8 */;
Query OK, 1 row affected (0.00 sec)

Please clarify. Thanks.

Please ignore.

I had the table `t1`, dumped it and replaced `t1``with `t2` in the script. However in this line I missed to do so:
insert  into `t1`(`iNetbarId`,`iUin`,`vNetbarName`) values (1,1,'xxxx');

So the script actually created `t2` but tried to insert to `t1` where such row already existed.  The error is correct. The INSERT statement violates a PK-constraint.

(the database existed at the time of USE. But not relevant).

'not a bug'.