Mysql does not commit changes after setting character set

Question

I am trying to set character set server by using set character_set_server = utf8; (it's originally utf8mb4) after I logged in to MySQL using mysql -u root -p (I also tried another user and also specified a DB with --database). After running the set command I checked the variable by using show variables like "%charac%". It shows the correct value as utf8. However, after I quit the session and re-logged in and rechecked the variable it shows utf8mb4. I also tried running commit; after setting character set, again it didn't change the value. What should I do?

Answer 1

What you're trying to do is not a transaction. Transactions are used with DML (data modifying language) Statements (like select, insert, upate, delete). Therefore committing is not the right thing to do here.

Most variables have a global value and a value that's only valid for the current session. You can see that in the manual here (look at scope). When you don't specify that you want to change the global value, the session value is assumed.

Try with

set global character_set_server = 'utf8';

Add that change also to your etc/my.cnf under the [mysqld] section. Otherwise this change will be lost again, when you restart the mysql server (mysqld).

And finally, you might want to leave that at uft8mb4. You might think, that you don't need the additional byte, but keep in mind, that that additional byte is only used, when you really use a character like an emoji in your text. And performance-wise it actually performs better. I have no proof at hand here, but I was recently at a database conference, where one speech was about benchmark checks. There it said, it performs much better. The utf8 that was previously implemented by is a crippled utf8 that should die as soon as possible.