CODEWITHSUNDEEP
mysqlmariadb
Matthew Scragg
Matthew Scragg

Reputation: 4638

MariaDB clients to use UTF-8 by default

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/

Upvotes: 5

Views: 18332

Answers (2)

Matthew Scragg
Matthew Scragg

Reputation: 4638

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.

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157919

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'

Upvotes: 1

Related Questions