Bug #78373 init_connect cannot execute multiple SET statements for charsets
Submitted: 9 Sep 2015 2:23 Modified: 14 Sep 2015 7:29
Reporter: Rene Wooller Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.5.40b, 5.6.20 OS:Any
Assigned to: CPU Architecture:Any

[9 Sep 2015 2:23] Rene Wooller
Description:
init_connect fails to execute if there is more than one parameter being set, or if an alias to more than one parameter, like "SET NAMES" is used.

How to repeat:
1. Set the init_connect variable using a super user in any of the following ways:

SET global init_connect = 'SET NAMES utf8mb4';

OR

SET global init_connect = 'SET character_set_client = utf8mb4, character_set_results = utf8mb4, character_set_connection = utf8mb4, collation_connection = utf8mb4_general_ci';

OR 

SET global init_connect = 'SET character_set_client = utf8mb4; SET character_set_results= utf8mb4; SET character_set_connection = utf8mb4; SET collation_connection = utf8mb4_general_ci;';

2. Initiate a new connection as a user that doesn't have super privilages. 

Look at the variables:
show variables like '%coll%';

    collation_connection	utf8_general_ci

show variables like '%char%';

    character_set_connection	 utf8

3. Note that the character set for the connection hasn't been set to utf8mb4

4. To confirm that 'init_connect' works for setting single parameters, repeat 1, 2 with:

SET global init_connect = 'SET character_set_connection = utf8mb4;'

And note that the connection does get changed.
[9 Sep 2015 9:28] MySQL Verification Team
Hi Rene,

Thank you for the report.
I'm not seeing this issue with GA version, could you please check with 5.6.26? 

// 5.6.26

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.26: bin/mysql  -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.26-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

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> show variables like '%char%';
+--------------------------+---------------------------------------------------------------------+
| Variable_name            | Value                                                               |
+--------------------------+---------------------------------------------------------------------+
| character_set_client     | utf8                                                                |
| character_set_connection | utf8                                                                |
| character_set_database   | latin1                                                              |
| character_set_filesystem | binary                                                              |
| character_set_results    | utf8                                                                |
| character_set_server     | latin1                                                              |
| character_set_system     | utf8                                                                |
| character_sets_dir       | /export/umesh/server/binaries/mysql-advanced-5.6.26/share/charsets/ |
+--------------------------+---------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> SET global init_connect = 'SET character_set_client = utf8mb4; SET character_set_results= utf8mb4; SET character_set_connection = utf8mb4; SET collation_connection = utf8mb4_general_ci;';
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.26: bin/mysql  -uushastry -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.26-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

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> show variables like '%char%';
+--------------------------+---------------------------------------------------------------------+
| Variable_name            | Value                                                               |
+--------------------------+---------------------------------------------------------------------+
| character_set_client     | utf8mb4                                                             |
| character_set_connection | utf8mb4                                                             |
| character_set_database   | latin1                                                              |
| character_set_filesystem | binary                                                              |
| character_set_results    | utf8mb4                                                             |
| character_set_server     | latin1                                                              |
| character_set_system     | utf8                                                                |
| character_sets_dir       | /export/umesh/server/binaries/mysql-advanced-5.6.26/share/charsets/ |
+--------------------------+---------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%coll%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | latin1_swedish_ci  |
| collation_server     | latin1_swedish_ci  |
+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql>

Thanks,
Umesh
[14 Sep 2015 7:29] Rene Wooller
Sorry - have tried again with 5.6.23 on the cmd line and it's working. It must have been my gui client causing the issue.

Thanks!