Bug #71181 Encoding big5 characters when insert to db via Mysql for excel
Submitted: 19 Dec 2013 7:41 Modified: 18 Jan 2014 0:44
Reporter: Fred Li Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S3 (Non-critical)
Version: OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[19 Dec 2013 7:41] Fred Li
Description:
Like Bug # 70259, I use big5 encoding, but also a question mark to replace big5 encoding

How to repeat:
always
[19 Dec 2013 7:44] Fred Li
none
[19 Dec 2013 11:30] Miguel Solorzano
Please provide an SQL script to recreate your database along with some of the data (including the exact character set used with it??. Thanks.
[20 Dec 2013 0:04] Fred Li
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `c_test` (
  `Dates` date NOT NULL DEFAULT '0000-00-00' COMMENT '訂購日期',
  `pf` varchar(10) NOT NULL COMMENT '訂購平台',
  `orderer` varchar(20) NOT NULL COMMENT '訂購者',
  `Cids` int(5) NOT NULL AUTO_INCREMENT COMMENT '自動編號',
  PRIMARY KEY (`Cids`),
  UNIQUE KEY `Cids` (`Cids`) USING BTREE,
  UNIQUE KEY `check` (`orderer`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=big5 COMMENT='客戶紀錄' AUTO_INCREMENT=3 ;

INSERT INTO `c_test` (`Dates`, `pf`, `orderer`, `Cids`) VALUES
('2013-12-18', '上品', '李先生', 1),
('2013-12-18', '綠界', '林小姐', 2);
[23 Dec 2013 15:55] Liang Guo
I'm not a mysql developer, I'm just a simplified Chinese user, 
I encounter a similar problem: cannot insert Chinese to mysql table with MySQL for excel. After some digg. I fount my MySQL server use latin1 Character Set. After recreate mysql server with utf8 Character Set, everything works for me. 

Would you like run 'show variables like '%char%';' to show your current character set?

this is my output:
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
[23 Dec 2013 15:58] Liang Guo
I have tested your script on my database. 
it works OK.
[24 Dec 2013 0:39] Fred Li
this is my output
Variable_name	Value	
character_set_client	utf8	
character_set_connection	utf8	
character_set_database	utf8	
character_set_filesystem	binary	
character_set_results	utf8	
character_set_server	utf8	
character_set_system	utf8
[24 Dec 2013 1:51] Liang Guo
Which version are you using?  I'm using 5.7.3.0-m13.
[24 Dec 2013 2:16] Fred Li
Server using version is 5.1.49
mysql for excel is not supported by older versions?
[24 Dec 2013 7:51] Liang Guo
I'm using MySQL 5.7 for Windows, and MySQL 5.5 for Linux. Both version work. 

Would you like try higher version? 

Thanks,
[18 Jan 2014 0:44] Javier Treviño
This is a duplicate of bug 70259 (http://bugs.mysql.com/bug.php?id=70259)

Tested inserting Chinese characters using MySQL for Excel, the data was inserted correctly in Chinese (verified it reading it in MySQL Workbench) and read back to Excel through an Insert Data operation correctly.

This does not seem to be a valid bug.
The user can reopen the bug if he can provide a configuration to reproduce the problem consistently.

The behavior the user reports may be caused by a misuse of misconfiguration, by misuse I mean attempting to append data with Chinese characters into a table configured with a character set (collation) that will not accept Chinese characters (e.g. latin1); by misconfiguration I mean attempting to insert Chinese characters into a table that has a correct character set (collation) like big5 or utf8, but the server's character set variables are set to use a character set non suitable for Chinese characters.