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:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.6.15, 5.6.21 OS:Microsoft Windows (Windows 7)
Assigned to: CPU Architecture:Any
Tags: character set, powershell, utf8

[12 Nov 2014 13:22] Daniël van Eeden
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
[13 Nov 2014 6:24] Umesh Shastry
Hello Daniël,

Thank you for the bug report and test case.

Thanks,
Umesh
[13 Nov 2014 6:25] Umesh Shastry
// 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>