| Bug #74817 | Character set issue with view | ||
|---|---|---|---|
| Submitted: | 12 Nov 2014 13:22 | Modified: | 13 Nov 2014 6:24 |
| Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.6.15, 5.6.21 | OS: | Windows (Windows 7) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | character set, powershell, utf8 | ||
[13 Nov 2014 6:24]
MySQL Verification Team
Hello Daniël, Thank you for the bug report and test case. Thanks, Umesh
[13 Nov 2014 6:25]
MySQL Verification Team
// 5.6.21
PS D:\ushastry\mysql-5.6.21> bin\mysql -u root -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2014, 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(id int,name varchar(100));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t1 values(1,'test1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(2,'test1 daniël');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t1 values(3,'test1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(4,'test Dani');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(5,'test Dani');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(6,'test1 Daniël');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(7,'testx Daniël');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(8,'testx Daniël');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+--------------+
| id | name |
+------+--------------+
| 1 | test1 |
| 2 | test1 daniël |
| 3 | test1 |
| 4 | test Dani |
| 5 | test Dani |
| 6 | test1 Daniël |
| 7 | testx Daniël |
| 8 | testx Daniël |
+------+--------------+
8 rows in set (0.00 sec)
mysql> select * from t1 where name like '%aniël%';
+------+--------------+
| id | name |
+------+--------------+
| 2 | test1 daniël |
| 6 | test1 Daniël |
| 7 | testx Daniël |
| 8 | testx Daniël |
+------+--------------+
4 rows in set (0.00 sec)
mysql> create view v1 as select * from t1 where name like '%aniël%';
Query OK, 0 rows affected (0.05 sec)
mysql> show variables like 'character\_set\_%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | cp850 |
| character_set_connection | cp850 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | cp850 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+--------+
7 rows in set (0.00 sec)
mysql> show create view v1\G
*************************** 1. row ***************************
View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1
`.`id` AS `id`,`t1`.`name` AS `name` from `t1` where (`t1`.`name` like '%ani+l%')
character_set_client: cp850
collation_connection: cp850_general_ci
1 row in set (0.00 sec)
mysql> select * from v1;
Empty set (0.00 sec)
mysql> select * from information_schema.views where table_name='v1'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: v1
VIEW_DEFINITION: select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t1` where (`test`.`t1`.`
name` like '%aniël%')
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: cp850
COLLATION_CONNECTION: cp850_general_ci
1 row in set (0.02 sec)
mysql> show create view v1\G
*************************** 1. row ***************************
View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1
`.`id` AS `id`,`t1`.`name` AS `name` from `t1` where (`t1`.`name` like '%ani+l%')
character_set_client: cp850
collation_connection: cp850_general_ci
1 row in set (0.00 sec)
mysql>
Description: When using PowerShell on Windows the character set of view can be wrong. I guess mysql.exe expect input in cp850, but gets utf8/utf16. When starting mysql.exe with --default-character-set=utf8 everything works as expected. How to repeat: Start mysql.exe from within a powershell window and create a view. mysql> select * from t1; +----+--------------+ | id | name | +----+--------------+ | 1 | test1 | | 2 | test1 daniël | | 3 | test | | 4 | test Dani | | 5 | test Dani | | 6 | test Daniël | | 7 | testx Daniël | | 8 | testx Daniël | +----+--------------+ 8 rows in set (0.02 sec) mysql> select * from t1 where name like '%aniël%'; +----+--------------+ | id | name | +----+--------------+ | 2 | test1 daniël | | 6 | test Daniël | | 7 | testx Daniël | | 8 | testx Daniël | +----+--------------+ 4 rows in set (0.00 sec) mysql> create view v1 as select * from t1 where name like '%aniël%'; Query OK, 0 rows affected (0.02 sec) mysql> show variables like 'character\_set\_%'; +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | character_set_client | cp850 | | character_set_connection | cp850 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | cp850 | | character_set_server | latin1 | | character_set_system | utf8 | +--------------------------+--------+ 7 rows in set (0.01 sec) mysql> show create view v1\G *************************** 1. row *************************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i d` AS `id`,`t1`.`name` AS `name` from `t1` where (`t1`.`name` like '%aniOö£┬¢l%') character_set_client: cp850 collation_connection: cp850_general_ci 1 row in set (0.02 sec) mysql> select * from v1; Empty set (0.00 sec) mysql> select * from information_schema.views where table_name='v1'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: v1 VIEW_DEFINITION: select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t1` where (`test`.`t1`.` name` like '%aniël%') CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: @localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: cp850 COLLATION_CONNECTION: cp850_general_ci 1 row in set (0.01 sec) mysql> show create view v1\G *************************** 1. row *************************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i d` AS `id`,`t1`.`name` AS `name` from `t1` where (`t1`.`name` like '%aniOö£┬¢l%') character_set_client: cp850 collation_connection: cp850_general_ci 1 row in set (0.00 sec) Suggested fix: Detect correct charset when started from powershell. Update 'Unicode Support on Windows' to include powershell http://dev.mysql.com/doc/refman/5.6/en/mysql-tips.html