Most info on the Net says to do the below. So I wanted to combine those into just 1 statement instead of 2.

init-connect='SET NAMES utf8'
init_connect='SET CHARACTER SET=utf-8'

Combining init_connect

Add to your /etc/my.cnf

[mysqld]
init_connect='SET collation_connection = utf8_general_ci,NAMES utf8'
character-set-server = utf8
collation-server = utf8_general_ci

You can change the default server character set and collation with the --character-set-server and --collation-server options when you start the server. The collation must be a legal collation for the default character set.

Misc my.cnf

init_connect='SET collation_connection = utf8_general_ci,NAMES utf8'
collation_server            = utf8_general_ci
collation_connection        = utf8_general_ci
character_set_client        = utf8
character_set_connection    = utf8
character_set_results       = utf8
character_set_server        = utf8
character_set_system        = utf8

10.1.4. Connection Character Sets and Collations

Getting Relevant MySQL Variables

# mysql INFORMATION_SCHEMA -tBe "SELECT * FROM GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE '%coll%' OR VARIABLE_NAME LIKE '%char%' OR VARIABLE_NAME='init_connect';"
+--------------------------+-------------------------------------------------------+
| VARIABLE_NAME            | VARIABLE_VALUE                                        |
+--------------------------+-------------------------------------------------------+
| CHARACTER_SETS_DIR       | /usr/share/mysql/charsets/                            |
| CHARACTER_SET_RESULTS    | utf8                                                  |
| CHARACTER_SET_CONNECTION | utf8                                                  |
| COLLATION_DATABASE       | utf8_general_ci                                       |
| CHARACTER_SET_SERVER     | utf8                                                  |
| COLLATION_SERVER         | utf8_general_ci                                       |
| CHARACTER_SET_SYSTEM     | utf8                                                  |
| CHARACTER_SET_FILESYSTEM | binary                                                |
| CHARACTER_SET_DATABASE   | utf8                                                  |
| CHARACTER_SET_CLIENT     | utf8                                                  |
| INIT_CONNECT             | SET collation_connection = utf8_general_ci,NAMES utf8 |
| COLLATION_CONNECTION     | utf8_general_ci                                       |
+--------------------------+-------------------------------------------------------+


# mysql -tBe "SHOW VARIABLES WHERE VARIABLE_NAME LIKE '%coll%' OR VARIABLE_NAME LIKE '%char%' OR VARIABLE_NAME='init_connect';"
+--------------------------+-------------------------------------------------------+
| Variable_name            | Value                                                 |
+--------------------------+-------------------------------------------------------+
| character_set_client     | latin1                                                |
| character_set_connection | latin1                                                |
| character_set_database   | utf8                                                  |
| character_set_filesystem | binary                                                |
| character_set_results    | latin1                                                |
| character_set_server     | utf8                                                  |
| character_set_system     | utf8                                                  |
| character_sets_dir       | /usr/share/mysql/charsets/                            |
| collation_connection     | latin1_swedish_ci                                     |
| collation_database       | utf8_general_ci                                       |
| collation_server         | utf8_general_ci                                       |
| init_connect             | SET collation_connection = utf8_general_ci,NAMES utf8 |
+--------------------------+-------------------------------------------------------+

Procedure MySQL Uses

To sum this up, the encoding/transcoding procedure MySQL uses to process the query and its results is a multi-step-thing:

MySQL treats the incoming query as being encoded in character_set_client.
MySQL transcodes the statement from character_set_client into character_set_connection
When comparing string values to column values MySQL transcodes the string value from character_set_connection into the chracter set of the given database column and uses the column collation to do sorting and comparison.
MySQL builds up the result set encoded in character_set_results (this includes result data as well as result metadata such as column names and so on)

my.cnf Definitions and Uses

init-connect: A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters. For example, each client session begins by default with autocommit mode enabled.
default-character-set: Use charset_name as the default character set for the client and connection. A common issue that can occur when the operating system uses utf8 or another multi-byte character set is that output from the mysql client is formatted incorrectly, due to the fact that the MySQL client uses the latin1 character set by default. You can usually fix such issues by using this option to force the client to use the system character set instead.
character-set-client: The character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when the client connects to the server. The global value of the variable is used to set the session value in cases when the client-requested value is unknown or not available, or the server is configured to ignore client requests.
character-set-system: The character set used by the server for storing identifiers. The value is always utf8.
character-set-server: The server's default character set.
collation-connection: The collation of the connection character set.
collation-server

More Info

You can find more information on the following manual pages:

Speed Up init_connect in my.cnf for UTF8 - AskApache

Charles Torvalds

13 Mar 2013

Exit mobile version