How to change latin1 to utf8

[AlexJ]

What settings i need to put in my.cnf so that latin1_swedish_ci changed to utf8_unicode_ci. If I put collation_connection = utf8_unicode_ci and then restart MySQL it doesn't start at all.

mysql> SHOW VARIABLES LIKE 'collation%';

+----------------------+-------------------+

| Variable_name | Value |

+----------------------+-------------------+

| collation_connection | latin1_swedish_ci |

| collation_database | utf8_unicode_ci |

| collation_server | utf8_unicode_ci |

+----------------------+-------------------+

3 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'character_set%';

+--------------------------+----------------------------+

| 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/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

[Machsterdaemon]

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci

You could also add this too with the other ones:

skip-character-set-client-handshake

skip-character-set-client-handshake

will ignore client information and use the default server character set.

You can additionally check the php.ini file and change default charset like this if it's needed:

default_charset = "UTF-8"

[AlexJ]

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci

I already had this in my.cnf but that doesn't fix the issue. It still shows me latin1 and latin1_swedish_ci as mentioned in the first post!

[Machsterdaemon]

I already had this in my.cnf but that doesn't fix the issue. It still shows me latin1 and latin1_swedish_ci as mentioned in the first post!

Do you have this too:

skip-character-set-client-handshake

Update:

It would be helpful if you posted all charset related variables you have added to your my.cnf file and the mysql version you are using.

[AlexJ]

Do you have this too:

skip-character-set-client-handshake

Update:

It would be helpful if you posted all charset related variables you have added to your my.cnf file and the mysql version you are using.

That fixed the issue but is it good idea to skip the handshake?

mysqld was started with the --skip-character-set-client-handshake option, which causes it to ignore client character set configuration. This reproduces MySQL 4.0 behavior and is useful should you wish to upgrade the server without upgrading all the clients.

Here is my settings;

#-------CHARACTER SET----------
character_set_server = utf8
skip-character-set-client-handshake
# -- doesn't exist character_set_connection = utf8
# -- doesn't exist character_set_results = utf8
# -- doesn't exist character_set_database = utf8

collation_server = utf8_unicode_ci
init-connect='SET NAMES utf8'
# -- doesn't exist collation_connection = utf8_unicode_ci

I am using MySQL version 5.6.19

[Machsterdaemon]

That fixed the issue but is it good idea to skip the handshake?

I'm really not sure about it, it may depend on the applications you are using, but generally I think it's ok to have it, and often recommendable. Many server administrators are used to enable this option when configuring the charset variables.

I'm using it on my own personal server using the same mysql version as you.