1

I am having some strange issues with character sets on my system. After digging through all kinds of settings, I found a discrepancy in my character set settings.

When I run show variables like 'char%'; I get:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
  1. Are there any side effects to changing the the character_set_server variable?
    • Could it possibly affect what character set a connection will use when talking with the database?
    • Could it possibly alter the existing data in any way?

From my understanding, it looks like all it does is set the character set for you when creating a new database if you don't specify a charset. Is that really all it does?

Source: https://www.blueboxcloud.com/insight/blog-article/getting-out-of-mysql-character-set-hell

0

You've stumbled onto one of the imponderables of MySQL's default configuration. To quote from the documentation on Character Set Configuration:

...when character_set_system differs from character_set_server or character_set_client, and you input characters manually (as database object identifiers, column values, or both), these may be displayed incorrectly in output from the client or the output itself may be formatted incorrectly. In such cases, starting the mysql client with --default-character-set=system_character_set—that is, setting the client character set to match the system character set—should fix the problem.

To distill, not changing the value of character_set_server will almost certainly eventually cause you problems if you use Unicode characters outside the base ISO-latin-1 character set.

  • Any idea what the documentation you posted means when it says you input characters manually? – zechdc Feb 25 '16 at 19:11
  • Not sure how this applies, but I found some text in the documentation that lead me to believe that character_set_server is kinda useless if you set the DB charset, which I have done. The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose. dev.mysql.com/doc/refman/5.5/en/charset-server.html – zechdc Feb 25 '16 at 19:12
  • I ran into trouble when I entered accented characters through the mysql client - PHP json_encode() b!+ched at me about "malformed utf8" - and I understand others have had the same problem from Workbench or phpmyadmin. Changing character_set_server fixed the problem. – Darwin von Corax Feb 25 '16 at 19:15
  • @DarwinvonCorax - that's surprising. The 3 settings changed by SET NAMES (or equivalent) are more relevant. – Rick James Mar 3 '16 at 18:55

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.