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

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

  • Is this documented somewhere? – dezso Mar 12 '18 at 15:30
  • what should I do sir . I want to store emojis in mysql . but my db shows ??? instead of emojis – Ramin Mar 12 '18 at 15:55
  • Also I tried with a non-root account but the same happened – Ramin Mar 12 '18 at 15:59
  • 1
    Actually, it is any MySQL user with SUPER priv. – Rick James Mar 12 '18 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 '18 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.

  • For More detail Please visit at stackoverflow.com/questions/6115612/… – Pankaj Kumar Mar 12 '18 at 10:07
  • I want to know why mysql ignores my conf file – Ramin Mar 12 '18 at 10:09
  • You haven't write correct key word that's why mysql ignores it – Pankaj Kumar Mar 12 '18 at 10:12
  • its a server param not a client thats why I used mysqld – Ramin Mar 12 '18 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 – Pankaj Kumar Mar 12 '18 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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