1

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_%'; :

enter image description here

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

2 Answers 2

2

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', ...]

7
  • Is this documented somewhere? Mar 12, 2018 at 15:30
  • what should I do sir . I want to store emojis in mysql . but my db shows ??? instead of emojis Mar 12, 2018 at 15:55
  • Also I tried with a non-root account but the same happened Mar 12, 2018 at 15:59
  • 1
    Actually, it is any MySQL user with SUPER priv.
    – Rick James
    Mar 12, 2018 at 16:38
  • 1
    @Ramin - If you find a solution, please report it here; sounds like I need to learn something.
    – Rick James
    Mar 12, 2018 at 20:02
0

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.

5
  • For More detail Please visit at stackoverflow.com/questions/6115612/… Mar 12, 2018 at 10:07
  • I want to know why mysql ignores my conf file Mar 12, 2018 at 10:09
  • You haven't write correct key word that's why mysql ignores it Mar 12, 2018 at 10:12
  • its a server param not a client thats why I used mysqld Mar 12, 2018 at 10:23
  • you should write in [mysqld] as below mentioned and then check by the query:- show variables like "%character%"; [mysqld] init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8mb4' character-set-server=utf8mb4 collation-server=utf8_unicode_ci skip-character-set-client-handshake Result is Mar 12, 2018 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.