Question :
I want the server to run an initial SQL query every time it starts:
[mysqld]
init-connect='SET NAMES utf8mb4'
and I want it to be only user specific not globally. so I created a .my.cnf in my user’s home directory(~/.my.cnf
) but when I try to see if it has affected the server it does not show my added setting and it seems that my settings are being ignored by the server:
mysqld --print-defaults
------------------------------------
--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
--bind-address=0.0.0.0 --
key_buffer_size=16M
--max_allowed_packet=16M
--thread_stack=192K
--thread_cache_size=8
--myisam-recover-options=BACKUP
--query_cache_limit=1M
--query_cache_size=16M
--log_error=/var/log/mysql/error.log
--expire_logs_days=10
--max_binlog_s
ize=100M
--default_password_lifetime=0
also this is the result of SHOW VARIABLES LIKE 'character_set_%';
:
I want theses params to be set to 'utf8mb4'
wach time the server boots.
character_set_client
character_set_results
character_set_connection
also I should say that I use Laravel Vagrant as my development environment
Answer :
init-connect
is ignored when you connect as the MySQL root
user.
Check the file config/database.php
for Laravel. It needs to say something like: 'mysql' => [..., 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', ...]
With the mysql client, to use a character set different from the default, you could explicitly execute SET NAMES
every time you start up.
To accomplish the same result more easily, add the --default-character-set
option setting to your mysql command line or in your option file. For example, the following option file setting changes the three connection-related character set variables set to koi8r
each time you invoke mysql:
[mysql]
default-character-set=koi8r
If you are using the mysql client with auto-reconnect enabled (which is not recommended), it is preferable to use the charset command rather than SET NAMES
. For example:
mysql> charset utf8
Charset changed
The charset command issues a SET NAMES
statement, and also changes the default character set that mysql uses when it reconnects after the connection has dropped.