MAMP MySQL not recognizing my.cnf values in OSX

Question

Trying to go UTF8 permanently and can't get MAMP's install of MySQL to recognize my.cnf values.

MAMP Version 2.0.5 (2.0.5)

MySQL 5.5.9

my.cnf file:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default-character-set=utf8
default-collation=utf8_general_ci
collation_server=utf8_general_ci
character_set_server=utf8
init-connect='SET NAMES utf8'

Location of file:

/Applications/MAMP/Library/Conf/

MySQL varibles on startup:

mysql> show variables where variable_name LIKE '%char%' OR variable_name LIKE '%colla%';
+--------------------------+--------------------------------------------+
| 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       | /Applications/MAMP/Library/share/charsets/ |
| collation_connection     | utf8_general_ci                            |
| collation_database       | latin1_swedish_ci                          |
| collation_server         | latin1_swedish_ci                          |
+--------------------------+--------------------------------------------+

Switching between various db's will get both _database values to utf8, but I can't seem to get both _server options to reflect utf8 / utf8_unicode_ci:

use tsdb;

+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | utf8                                       |
| character_set_connection | utf8                                       |
| character_set_database   | utf8                                       |
| character_set_filesystem | binary                                     |
| character_set_results    | utf8                                       |
| character_set_server     | latin1                                     |
| character_set_system     | utf8                                       |
| character_sets_dir       | /Applications/MAMP/Library/share/charsets/ |
| collation_connection     | utf8_general_ci                            |
| collation_database       | utf8_general_ci                            |
| collation_server         | latin1_swedish_ci                          |
+--------------------------+--------------------------------------------+

Tried

set global character_set_server = utf8;

etc, but it didn't take on restart.

This is my first time messing with my.cnf so I'm sure I'm overlooking something basic. Is there info missing from my.cnf, is the syntax wrong? Or is order important?

Thanks.

Analgesia

Answer 1

Included skip-character-set-client-handshake in the [mysqld] group of the my.cnf file and everything seems correctly configured, UTF8 straight through. I'm still not sure why default-character-set=utf8 in the [client] group didn't take here, but I'm a newbie so hopefully someone can shed light there. You must create my.cnf in Applications/MAMP/conf and IN MAMP Pro, you go under the File > Edit Template > MySQL my.cnf to make the changes.

my.cnf:

# The MySQL server
[mysqld]
skip-character-set-client-handshake
collation_server=utf8_unicode_ci
character_set_server=utf8

Results:

mysql> SHOW VARIABLES WHERE variable_name LIKE '%char%' OR variable_name LIKE '%colla%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | utf8                                       |
| character_set_connection | utf8                                       |
| character_set_database   | utf8                                       |
| character_set_filesystem | binary                                     |
| character_set_results    | utf8                                       |
| character_set_server     | utf8                                       |
| character_set_system     | utf8                                       |
| character_sets_dir       | /Applications/MAMP/Library/share/charsets/ |
| collation_connection     | utf8_unicode_ci                            |
| collation_database       | utf8_unicode_ci                            |
| collation_server         | utf8_unicode_ci                            |
+--------------------------+--------------------------------------------+

This also solved why mysqladmin's variables were different than mysql's when using SHOW VARIABLES for each.

Solution mentioned in comments of MySQL manual here.

Analgesia

This worked for me. In MAMP, you create my.cnf in Applications/MAMP/conf and IN MAMP Pro, you go under the File > Edit Template > MySQL my.cnf to make the changes. – Ferrochromium

@Analgesia This question finally solved my hours-and-hours-of-searching to find an answer! Please edit your answer, please mention that "my.cnf" file must be placed in "Applications/MAMP/conf" for MAMP 2.0 – Baseboard

Just to clarify, you don't need any of the settings proposed in the question for [mysqld], just those proposed in the answer. – Rubenrubens

skip-character-set-client-handshake does the magic, thanks! – Woodland