| Bug #72996 | desc error on convert | ||
|---|---|---|---|
| Submitted: | 13 Jun 2014 6:08 | Modified: | 10 Apr 2015 7:06 |
| Reporter: | qinglin zhang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.16, 5.6.19, 5.6.18,5.6.23 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | character, SET | ||
[13 Jun 2014 6:47]
MySQL Verification Team
Thank you for the report.
I'm not seeing this issue on our latest build 5.6.20(also on 5.6.17).
Could you please try it on 5.6.17 or GA version 5.6.19?
mysql> CREATE DATABASE IF NOT EXISTS `my_drc1` DEFAULT CHARACTER SET binary;
Query OK, 1 row affected (0.05 sec)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use `my_drc1`;
Database changed
mysql> DROP TABLE IF EXISTS `set_data_type3`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `set_data_type3` (
-> `drc_set_cn` set('测测测','密密密') DEFAULT NULL
-> );
Query OK, 0 rows affected (0.69 sec)
mysql> desc set_data_type3;
+------------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------------+------+-----+---------+-------+
| drc_set_cn | set('▒▒▒▒','▒▒▒▒▒▒') | YES | | NULL | |
+------------+------------------------+------+-----+---------+-------+
1 row in set (0.06 sec)
mysql> select version();
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.20-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)
[15 Jun 2014 13:05]
qinglin zhang
HI, the version is not released yet.
[16 Jun 2014 4:36]
MySQL Verification Team
Thank you for the feedback.
I'm not seeing it on latest GA i.e 5.6.19
mysql> CREATE DATABASE IF NOT EXISTS `my_drc1` DEFAULT CHARACTER SET binary;
Query OK, 1 row affected (0.00 sec)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use `my_drc1`;
Database changed
mysql> DROP TABLE IF EXISTS `set_data_type3`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `set_data_type3` (
-> `drc_set_cn` set('测测测','密密密') DEFAULT NULL
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc set_data_type3;
+------------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------------+------+-----+---------+-------+
| drc_set_cn | set('▒▒▒▒','▒▒▒▒▒▒') | YES | | NULL | |
+------------+------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | binary |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.19 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.19 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
[16 Jun 2014 4:53]
MySQL Verification Team
// 5.7.5
mysql>
mysql> CREATE DATABASE IF NOT EXISTS `my_drc1` DEFAULT CHARACTER SET binary;
Query OK, 1 row affected (0.00 sec)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use `my_drc1`;
Database changed
mysql> DROP TABLE IF EXISTS `set_data_type3`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `set_data_type3` (
-> `drc_set_cn` set('测测测','密密密') DEFAULT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc set_data_type3;
+------------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------------+------+-----+---------+-------+
| drc_set_cn | set('▒▒▒▒','▒▒▒▒▒▒') | YES | | NULL | |
+------------+------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.7.5 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.5-m15-enterprise-commercial-advanced-log |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
[17 Jun 2014 2:27]
qinglin zhang
HI, I tried 5619 and find the same problem. my configuration is:
$ cat my.cnf
[client]
socket = /u01/guyue.zql/5619/run/mysql.sock
[innotop]
socket = /u01/guyue.zql/5619/run/mysql.sock
[mysql]
auto-rehash
[mysqld]
core-file
#### Baes dir ####
basedir = /u01/guyue.zql/5619
datadir = /u01/guyue.zql/5619/data
tmpdir = /u01/guyue.zql/5619/tmp
socket = /u01/guyue.zql/5619/run/mysql.sock
#### Base configure info ####
port = 5619
skip-name-resolve
old_passwords = 0
lower_case_table_names = 1
log_bin_use_v1_row_events=1
open_files_limit = 65535
read_rnd_buffer_size = 5M
max_allowed_packet = 24M
max_connect_errors = 50000
max_connections = 5000
max_user_connections = 2000
thread_cache_size=64
binlog_format=row
#### Log info ####
log-error = /u01/guyue.zql/5619/log/alert.log
log-queries-not-using-indexes
long_query_time = 1
#### Binary log && Relay log ####
log-bin = /u01/guyue.zql/5619/log/mysql-bin
log-slave-updates = 1
relay-log = /u01/guyue.zql/5619/log/mysqld-relay-bin
relay-log-index = /u01/guyue.zql/5619/log/mysqld-relay-bin.index
master-info-file = /u01/guyue.zql/5619/log/master.info
relay-log-info-file = /u01/guyue.zql/5619/log/relay-log.info
#expire_logs_days=1
max_binlog_size = 500M
max_binlog_cache_size = 2G
relay_log_purge=1
### Server id ####
server-id=5619
#### query cache ####
query_cache_size = 0
query_cache_limit = 1K
query_cache_min_res_unit = 1K
query_cache_type=0
I repeat the problem under the following step:
CREATE DATABASE `my_drc1` /*!40100 DEFAULT CHARACTER SET binary */
set names gbk;
DROP TABLE IF EXISTS `set_data_type`;
CREATE TABLE `set_data_type3` (
`drc_set_cn` set('测测测','密密密') DEFAULT NULL
);
desc set_data_type;
mysql> desc set_data_type3;
+------------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------+------+-----+---------+-------+
| drc_set_cn | set(' | YES | | NULL | |
+------------+-------+------+-----+---------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------
---------------------------+
| Level | Code | Message
|
+---------+------+--------------------------------------------------------------
---------------------------+
| Warning | 1366 | Incorrect string value: '\xB2\xE2\xB2\xE2\xB2\xE2...' for col
umn 'COLUMN_TYPE' at row 1 |
| Warning | 1366 | Incorrect string value: '\xB2\xE2\xB2\xE2\xB2\xE2...' for col
umn 'COLUMN_TYPE' at row 1 |
+---------+------+--------------------------------------------------------------
---------------------------+
2 rows in set (0.00 sec)
mysql> show create table set_data_type3;
+----------------+--------------------------------------------------------------
-------------------------------------------------------------+
| Table | Create Table
|
+----------------+--------------------------------------------------------------
-------------------------------------------------------------+
| set_data_type3 | CREATE TABLE `set_data_type3` (
`drc_set_cn` set('测测测','密密密') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=binary |
+----------------+--------------------------------------------------------------
-------------------------------------------------------------+
1 row in set (0.00 sec)
By the way,'oxB2E2' is the gbk code of chinese code '测'
[17 Jun 2014 2:40]
qinglin zhang
Appendix: The client is mysql build on windows while server is linux. Linux XXXXXXXXXX 2.6.32-220.17.1.tb619.el6.x86_64 #1 SMP Fri Jun 8 13:48:13 CST 2012 x86_64 x86_64 x86_64 GNU/Linux
[10 Apr 2015 7:06]
MySQL Verification Team
Thank you for the feedback.
My colleague Dachao has confirmed this issue now:
## 5.6.18/23 hosted on Solaris and client connected from Win7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.18-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercia
Copyright (c) 2000, 2015, 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 DATABASE `my_drc1` /*!40100 DEFAULT CHARACTER SET binary */ ;
Query OK, 1 row affected (0.01 sec)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use my_drc1;
Database changed
mysql> DROP TABLE IF EXISTS `set_data_type`;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CREATE TABLE `set_data_type3` (
-> `drc_set_cn` set('测测测','密密密') DEFAULT NULL
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc set_data_type3;
+------------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------+------+-----+---------+-------+
| drc_set_cn | set(' | YES | | NULL | |
+------------+-------+------+-----+---------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xB2\xE2\xB2\xE2\xB2\xE2...' for column 'COLUMN_TYPE' at row 1 |
| Warning | 1366 | Incorrect string value: '\xB2\xE2\xB2\xE2\xB2\xE2...' for column 'COLUMN_TYPE' at row 1 |
+---------+------+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
(12:15:12 PM) Umesh: also, pls post output of
\s
show variables like '%version%';
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.23, for Win32 (x86)
Connection id: 11
Current database: my_drc1
Current user: dachao@x.x.x.x
SSL: Not in use
Using delimiter: ;
Server version: 5.6.18-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version: 10
Connection: y.y.y.y via TCP/IP
Server characterset: utf8
Db characterset: binary
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 2 days 23 hours 54 min 15 sec
Threads: 1 Questions: 224 Slow queries: 0 Opens: 98 Flush tables: 1 Open tables: 84 Queries per second avg: 0.000
show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.18 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.18-enterprise-commercial-advanced-log |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | sparc |
| version_compile_os | solaris10 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
## Locale
This is on Solaris Server side :
-bash-3.2# locale
LANG=
LC_CTYPE="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_COLLATE="C"
LC_MONETARY="C"
LC_MESSAGES="C"
LC_ALL=
Description: Desc table or columns from infromation_schema don't show full column information when data type is set and the set contains character in chinese. the stack lie bellow: #0 my_well_formed_len_mb (cs=0x17e3580, b=0x7fcf9c4591e0 "set('\262\342\262\342\262\342','\303\334\303\334\303\334')references", e=0x7fcf9c4591f6 "references", pos=22, error=0x7fcf9c458fec) at /u01/guyue.zql/workplace/mysql-5.6.15/strings/ctype-mb.c:408 #1 0x000000000083ed76 in well_formed_copy_nchars (to_cs=0x17e3580, to=0x7fcee00da8f0 "", to_length=66, from_cs=0x17e3580, from=0x7fcf9c4591e0 "set('\262\342\262\342\262\342','\303\334\303\334\303\334')references", from_length=22, nchars=22, well_formed_error_pos=0x7fcf9c459118, cannot_convert_error_pos=0x7fcf9c459110, from_end_pos=0x7fcf9c459108) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_string.cc:938 #2 0x00000000009197b7 in Field_blob::store_internal (this=0x7fcee00dc8b8, from=0x7fcf9c4591e0 "set('\262\342\262\342\262\342','\303\334\303\334\303\334')references", length=22, cs=0x17e3580) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/field.cc:7811 #3 0x00000000009199bb in Field_blob::store (this=0x7fcee00dc8b8, from=0x7fcf9c4591e0 "set('\262\342\262\342\262\342','\303\334\303\334\303\334')references", length=22, cs=0x17e3580) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/field.cc:7842 #4 0x000000000082d48e in get_schema_column_record (thd=0x2364080, tables=0x7fcee00e7a28, table=0x7fcee00db0a0, res=false, db_name=0x7fcf9c45aa50, table_name=0x7fcf9c45aa40) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_show.cc:4776 #5 0x00000000008294a7 in fill_schema_table_by_open (thd=0x2364080, is_show_fields_or_keys=true, table=0x7fcee00db0a0, schema_table=0x150a8c0, orig_db_name=0x7fcf9c45aa50, orig_table_name=0x7fcf9c45aa40, open_tables_state_backup=0x7fcf9c45a920, can_deadlock=false) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_show.cc:3526 #6 0x000000000082a3bf in get_all_tables (thd=0x2364080, tables=0x7fcee0005f30, cond=0x0) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_show.cc:4046 #7 0x00000000008386dd in do_fill_table (thd=0x2364080, table_list=0x7fcee0005f30, join_table=0x7fcee00e7578) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_show.cc:7283 #8 0x0000000000838ab4 in get_schema_tables_result (join=0x7fcee00d85b0, executed_place=PROCESSED_BY_JOIN_EXEC) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_show.cc:7384 #9 0x000000000080cacd in JOIN::prepare_result (this=0x7fcee00d85b0, columns_list=0x7fcf9c45add0) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_select.cc:822 #10 0x00000000007aa6bb in JOIN::exec (this=0x7fcee00d85b0) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_executor.cc:116 #11 0x000000000080d602 in mysql_execute_select (thd=0x2364080, select_lex=0x23669b8, free_join=true) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_select.cc:1100 #12 0x000000000080d919 in mysql_select (thd=0x2364080, tables=0x7fcee0005f30, wild_num=0, fields=..., conds=0x0, order=0x2366b80, group=0x2366ab8, having=0x0, select_options=2684619520, result=0x7fcee00d8588, unit=0x2366370, select_lex=0x23669b8) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_select.cc:1221 #13 0x000000000080b93f in handle_select (thd=0x2364080, result=0x7fcee00d8588, setup_tables_done_option=0) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_select.cc:110 #14 0x00000000007e560d in execute_sqlcom_select (thd=0x2364080, all_tables=0x7fcee0005f30) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_parse.cc:5373 #15 0x00000000007dde1d in mysql_execute_command (thd=0x2364080) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_parse.cc:2908 #16 0x00000000007e7d2e in mysql_parse (thd=0x2364080, rawbuf=0x7fcee0004ec0 "desc set_data_type3", length=19, parser_state=0x7fcf9c45c670) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_parse.cc:6514 #17 0x00000000007dad2e in dispatch_command (command=COM_QUERY, thd=0x2364080, packet=0x2481f51 "desc set_data_type3", packet_length=19) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_parse.cc:1568 #18 0x00000000007d9554 in do_command (thd=0x2364080) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_parse.cc:1039 #19 0x00000000007a038d in do_handle_one_connection (thd_arg=0x23a9ae0) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_connect.cc:982 #20 0x000000000079fe95 in handle_one_connection (arg=0x23a9ae0) at /u01/guyue.zql/workplace/mysql-5.6.15/sql/sql_connect.cc:898 #21 0x0000000000b7c7f7 in pfs_spawn_thread (arg=0x2280fd0) at /u01/guyue.zql/workplace/mysql-5.6.15/storage/perfschema/pfs.cc:1858 #22 0x0000003659e077e1 in start_thread () from /lib64/libpthread.so.0 #23 0x0000003659ae68ed in clone () from /lib64/libc.so.6 How to repeat: cat char.test: CREATE DATABASE `my_drc1` DEFAULT CHARACTER SET binary; set names gbk; DROP TABLE IF EXISTS `set_data_type`; CREATE TABLE `set_data_type3` ( `drc_set_cn` set('测测测','密密密') DEFAULT NULL ); show create table my_drc1.set_data_type; desc my_drc1.set_data_type; mysql> desc set_data_type3; +------------+-------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------+------+-----+---------+-------+ | drc_set_cn | set(' | YES | | NULL | | +------------+-------+------+-----+---------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\xB2\xE2\xB2\xE2\xB2\xE2...' for column 'COLUMN_TYPE' at row 1 | | Warning | 1366 | Incorrect string value: '\xB2\xE2\xB2\xE2\xB2\xE2...' for column 'COLUMN_TYPE' at row 1 | +---------+------+-----------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) Suggested fix: none