Bug #81661 Can't set character_set_client in option files
Submitted: 31 May 2016 17:21 Modified: 31 May 2016 21:01
Reporter: Geoff Montee Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.6.30 OS:Any
Assigned to: CPU Architecture:Any

[31 May 2016 17:21] Geoff Montee
Description:
Currently, when "character_set_client" is set in an option file, the server doesn't use the value to set character_set_client. Instead, it thinks that it is a prefix option for character-set-client-handshake, so it tries to set that instead. These do appear to refer to different options.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_...

https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_character-set-cl...

For example, if I have the following in an option file:

[mysqld]
character_set_client='latin1'

The server's error log will show warnings like the following:

2016-05-31 13:04:50 0 [Warning] Using unique option prefix character_set_client instead of character-set-client-handshake is deprecated and will be removed in a future release. Please use the full name instead.
2016-05-31 13:04:50 0 [Warning] /usr/sbin/mysqld: ignoring option '--character-set-client-handshake' due to invalid value 'latin1'

The global value of character_set_client can be changed dynamically, so it seems that it would make sense to allow it to be set in option files as well.

mysql> SELECT @@global.character_set_client;
+-------------------------------+
| @@global.character_set_client |
+-------------------------------+
| latin1                        |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL character_set_client='utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.character_set_client;
+-------------------------------+
| @@global.character_set_client |
+-------------------------------+
| utf8                          |
+-------------------------------+
1 row in set (0.00 sec)

How to repeat:
1.) Set character_set_client in an option file.

2.) View the log to see the warnings.

Suggested fix:
Allow character_set_client to be set in option files.
[31 May 2016 20:15] Miguel Solorzano
Thank you for the bug report.

https://dev.mysql.com/doc/refman/5.6/en/charset-connection.html

"With the mysql client, to use a character set different from the default, you could explicitly execute SET NAMES every time you start up. To accomplish the same result more easily, add the --default-character-set option setting to your mysql command line or in your option file. For example, the following option file setting changes the three connection-related character set variables set to koi8r each time you invoke mysql:

[mysql]
default-character-set=koi8r"

C:\Program Files\MySQL\MySQL Server 5.6>bin\mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 "%character%";
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | koi8r                                                   |
| character_set_connection | koi8r                                                   |
| character_set_database   | utf8                                                    |
[31 May 2016 21:01] Geoff Montee
Hi Miguel,

Yes, I am aware that users can set --default-character-set on the client side, but this is not really equivalent to setting character_set_client on the server.

The --default-character-set option is a client-side option that tells the client to use that character set in its communications with the server. If you wanted this configuration change to affect every client that contacts a server, you would have to make sure that every client that you use supports this option, and that the option is set for all of those clients.

In contrast, the character_set_client option is a server-side variable that tells the server to use the provided character set for any clients that do not request a character set when they contact the server. The global value can currently be set dynamically. e.g.:

SET GLOBAL character_set_client='latin1';

But the global value cannot be set in option files. This seems like a strange distinction to me.