mysql change default character set latin1 to utf8

Question

I have PHP 5.5.9 and MySQL 5.5.43 Innodb. This is in Kubuntu LTS /etc/mysql/my.cnf

printf("Current character set: %s\n", mysqli_character_set_name($conn));

Current character set: latin1

I've tried

default-character-set = utf8

first but then my mysql server didn't restart

Then I've tried

character-set-server = utf8

then my mysql shell didn't start

How can I rescue this issue? I've managed with init_connect = 'SET NAMES utf8' solve the problem for nonroot users, but the charset remains the same (it only displays as nonprivileged users UTF8, it is still Latin1).

And if (a big if) smnd could tell me WHERE should I use utf8mb4? Also in my.cnf file? I had my tables in utf8 and have migrated those with which I am working to utf8mb4 ...

Another Q: IS that applicable for Linux (Kubuntu) server: https://www.youtube.com/watch?v=3M1Wpw4uOoM

Please help me, thanks, Gregor from lilaum.com

Answer 1

mysqli_set_charset('utf8').

init_connect is ignored for root connections.

utf8mb4 is effectively a superset of utf8. It is needed for Chinese and the new emoji, plus a scattering of obscure character sets.

Answer 2

PHP manual says:

MySQLnd always assumes the server default charset. This charset is sent during connection hand-shake/authentication, which mysqlnd will use.

MySQLnd is the MySQL native driver included with PHP 5.4 and above.

Unfortunately, I have to confirm that even in my system (PHP 5.6.17 with MySQL 5.5.47 on Debian Jessie 8.3) despite having configured /etc/mysql/my.cnf with the following entries

[mysqld]
character-set-server=utf8

[mysql]
default-character-set=utf8

the mysqlnd does not seem to assume the server default charset (utf8) but sets the connection charset to latin1 with collate latin1_swedish (maybe a default value considering MySQL AB was a swedish company).

So, it seems that there's no permanent way to set utf8 charset when connecting from a PHP application. You have to call

$mysqli->set_charset("utf8")

after each connection. This could be embedded in a custom 'dbconnect' PHP function in order to provide a sort of abstraction layer.