MariaDB clients to use UTF-8 by default

Question

How do I get MariaDB clients to use UTF-8? I don't have this problem with MySQL of the equivalent version. I am trying to do this without issuing SET NAMES via the client. I get latin1 with the cmd line client and php's mysqli driver.

PHP mysqli_get_charset:

print_r(mysqli_get_charset($link));
stdClass Object
(
    [charset] => latin1
    [collation] => latin1_swedish_ci
    [dir] => 
    [min_length] => 1
    [max_length] => 1
    [number] => 8
    [state] => 1
    [comment] => 
)

Here is the output from the command line client:

echo "show variables like 'char%';" | mysql -u root -p

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      /usr/share/mysql/charsets/

Here is my my.cnf file.

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set=utf8
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
default-character-set=utf8
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
default-character-set  = utf8
default-collation      = utf8_general_ci
character_set_server   = utf8
collation_server       = utf8_general_ci
init_connect='SET NAMES utf8'
key_buffer              = 512M
max_allowed_packet      = 64M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 4M
query_cache_size        = 64M
expire_logs_days        = 10
max_binlog_size         = 100M
[mysqldump]
quick
quote-names
max_allowed_packet      = 64M
default-character-set=utf8
[mysql]
no-auto-rehash  # faster start of mysql but no tab completition
default_character_set=utf8
[isamchk]
key_buffer              = 128M
[client-server]
default-character-set=utf8
[client-mariadb]
default-character-set=utf8
!includedir /etc/mysql/conf.d/

You want to change the character set on your database??...is that your question?? — Hackerman, Apr 12 '13 at 23:05
No, I want to MariaDB to instruct clients to use UTF-8 as the default character set. I amended my question and added some PHP output. — Matthew Scragg, Apr 12 '13 at 23:12
http://stackoverflow.com/questions/202205/how-to-make-mysql-handle-utf-8-properly — Hackerman, Apr 12 '13 at 23:21

score 1 · Answer 1 · answered Apr 13 '13 at 05:40

1

The problem is, you shouldn't issue SET NAMES via the client anyway.
Because this command is insufficient, as beside server you have to set the client encoding as well. And this can be done only by using API function. So, instead of issuing SET NAMES query, in your PHP scripts you ought to set client encoding by means of msql(i)_set_charset() function or DSN.

So, remember - changing default encoding won't relieve you from necessity of setting encoding in PHP.

Nevertheless, utf-8 makes more sense as default than latin1. So, add the following line in my.ini, under [mysqld] section

init-connect='SET NAMES utf8'

answered Apr 13 '13 at 05:40

Your Common Sense

156,878
40
214
345

I don't think you understood the question. Maria wasn't using the directives as expected. I didn't want to use SET NAMES via the client because I have multiple database drivers to different servers that are initialized but not always used, therefore issuing that query is a waste. mysqli_set_charset still does a SET NAMES and I realize it sets the client encoding as well but the client encoding will be utf8 by default if setup properly. I had the init-connect statement you mentioned in my.cnf as a last ditch effort, still didn't work. Anyhow, I figured it out as described in my answer. – Matthew Scragg May 14 '13 at 03:08

score 1 · Accepted Answer · answered May 07 '13 at 15:33

1

I figured out the problem. I installed mariadb using their repo and apt-get. Everything seemed to go ok. I had these really slow restarts using the command service mysql restart and also the encoding issue. I noticed there was 2 instances of mysqld, I went ahead and sent a kill command, I would not recommend kill -9, regular kill worked just fine. Then did a service mysql restart, it started quickly, character encoding worked as expected.

answered May 07 '13 at 15:33

Matthew Scragg

4,540
3
19
27

One of my servers had the same slow `service mysql restart` issue. Fixed it after reading your answer. Thanks much! – Pothi Kalimuthu Sep 25 '13 at 08:51

MariaDB clients to use UTF-8 by default

2 Answers2