How do I get the MySQL command line tools to display Unicode correctly?


Roger's House

I am using a Python program to write text containing Unicode characters to a MySQL database. For example, two of the characters are

u'\u2640' a symbol for Venus or female
u'\u2642' a symbol for Mars or male

I use utf8mb4 for almost all MySQL-related character sets. Here is an excerpt from /etc/mysql/my.cnf

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
default-character-set=utf8mb4
character-set-server =utf8mb4
character_set_system =utf8mb4

Additionally, all tables are created with the following parameters:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

In all but one, the handling of Unicode works just fine. I can write Unicode to a database table, read it, display it, etc. without any problems. The exception is mysql, which is the MySQL command line tool. When I execute a SELECT statement to view rows in a table containing Venus and Mars Unicode characters, here is what I see on the screen:

| Venus     | ♀      |
| Mars      | ♂      |

What I should see in the right column are the standard glyphs for Venus and Mars.

Any ideas on how to get the MySQL command line tools to display Unicode correctly?

edit:

I've done a lot of research on various MySQL system variables etc, and now I realize that the my.cnf setup shown above has some serious issues. In fact, the server mysqld will not start with the settings shown. To correct the problem, remove these from [mysqld]:

default-character-set=utf8mb4
character-set-system=utf8mb4

I'm not sure what the [client] option does, but it doesn't seem to hurt.

In Python u'\u2640' represents a single Unicode character, "♀". This compiles to three bytes containing the hex value E29980. I have no problems encoding and decoding Unicode. The correct values ​​are stored in the MySQL table; they are correctly read from the table, and when displayed by a Python program, they appear as follows:

♀   Venus
♂   Mars

Program output can be redirected to a file, processed by a text editor, etc., and the correct Unicode symbols are displayed in all cases.

There is only one place where the correct Unicode symbols are not displayed, and that is when I use the MySQL command line tools. When I issue a SELECT statement on a table that contains Unicode symbols, I get the garbage shown above. This is not a Windows specific issue. I had the exact same problem when I ran the MySQL command line tool on Windows, Mac OS X and Ubuntu.

Roger's House

I'm a little embarrassed to report that the MySQL command line tools have never had a problem displaying Unicode characters. Why do I think there is?

I have written a number of Python 2 programs using MySQLdb to communicate with MySQL. My data involves Unicode characters, such as the symbols for Mars and Venus. I am able to write these Unicode characters to the database, read them back, and generally operate on them like any other character.

One annoyance: Using the MySQL command line tools, when I select rows from a table that contains symbols like Mars and Venus, I only see garbage. That's what brought me to my original post asking how to get Unicode to display correctly. I have never gotten a satisfactory answer.

Recently I started converting Python 2 programs to Python 3, using pymysql to communicate with MySQL. Immediately, I ran into a problem. The Unicode characters I read from the database seem to be all wrong. Investigation showed that, in fact, the bytes stored in the database (created with Python 2) did not form the correct utf8 sequence for the Unicode characters I was using.

I converted the Python 2 program that created the table to Python 3, recreated the table, and, presto, changeo, everything worked. In other words, the characters in the database were wrong from day one, but when read by a Python 2 program, the original Unicode characters were correctly recreated.

Of course, all of a sudden, the MySQL command line tools started displaying Unicode characters normally. The problem is that the bytes in the database created by Python 2 and MySQLdb are not the correct utf8 representation of the characters I'm storing. I don't know what the exact bytes are, and I've been dealing with this problem too long to take the time to find out.

I recommend this article to anyone using Unicode in MySQL . It shows all the MySQL parameters that must be set for Unicode, and shows how to view the parameters in your own MySQL installation.

Related


How do I get C:/ on the command line

username I am on Windows 10. I'm trying to load Adobe Dreamweaver extension via command line on Windows. I want to navigate to C:\Program Files\Adobe\Adobe Extension Manager CSx\ I am following the instructions here . If I select command prompt (admin), I getC

How do I get C:/ on the command line

username I am on Windows 10. I'm trying to load Adobe Dreamweaver extension via command line on Windows. I want to navigate to C:\Program Files\Adobe\Adobe Extension Manager CSx\ I am following the instructions here . If I select command prompt (admin), I getC

PostgreSQL - do I need command line tools?

David Blomstrom I decided to try PostgreSQL. It does look interesting, but it's not very user friendly. I got great help from the PostgreSQL email list, but they insist that the tool to use is a command line editor (PSQL). Unfortunately, it was a total disaste

PostgreSQL - do I need command line tools?

David Blomstrom I decided to try PostgreSQL. It does look interesting, but it's not very user friendly. I got great help from the PostgreSQL email list, but they insist that the tool to use is a command line editor (PSQL). Unfortunately, it was a total disaste

How do I get images to display correctly on my website?

Stefan Georgiev Working on an ASP.NET project, my current theme is a restaurant. My problem is that I made a Scaffolded project to create a "MealsController" and my buttons (Edit, Create, Details, Delete) work fine. However, I'm currently confused on how to di

How do I get images to display correctly on my website?

Stefan Georgiev Working on an ASP.NET project, my current theme is a restaurant. My problem is that I made a Scaffolded project to create a "MealsController" and my buttons (Edit, Create, Details, Delete) work fine. However, I'm currently confused on how to di

How do command line tools communicate with browsers?

Junke | This is a command line tool force-cli When used force login, it will open a browser and ask for Oauth login. After authorization, the browser will close and return to the command line tool. I am new to command line tools (Terminal.app or cmd.exe) My qu

How do I turn on display settings in the command line?

Pierre I have Ubuntu 18.04 installed, and when I set up an extra monitor for my laptop, it's being used as the primary monitor for obvious reasons. However, sometimes when I start the laptop, the attached monitor stays blank and has to be rebooted or rebooted

How do I get the Windows command prompt to display the time?

Daniel Williams I want my command prompt (not powershell, just command prompt) to show the time like this: 14:02> Is there a way to do this? Basically, I have everything running from the prompt and am happy to see when I execute various commands. GroovyDotCom

How do I get the Windows command prompt to display the time?

Daniel Williams I want my command prompt (not powershell, just command prompt) to show the time like this: 14:02> Is there a way to do this? Basically, I have everything running from the prompt and am happy to see when I execute various commands. GroovyDotCom

How can I make Windows display Unicode characters correctly?

user 541686 How can I make Windows 7 display Unicode correctly, like all characters in this question ? Right now, I only see squares for some of them. red sand brick Internet browser You didn't say it explicitly, but I guess your question is about web browsers

How can I fix my locale to display Unicode correctly in irssi?

menstrual I've just arrived at the new lab and Japan and the servers available are only Japanese locales. request locale -aa return C POSIX ja_JP ja_JP.eucjp ja_JP.ujis ja_JP.utf8 japanese japanese.euc So I changed the environment variables and now set my loc

How can I make Windows display Unicode characters correctly?

user 541686 How can I make Windows 7 display Unicode correctly, like all characters in this question ? Right now, I only see squares for some of them. red sand brick Internet browser You didn't say it explicitly, but I guess your question is about web browsers

How can I fix my locale to display Unicode correctly in irssi?

menstrual I've just arrived at the new lab and Japan and the servers available are only Japanese locales. request locale -aa return C POSIX ja_JP ja_JP.eucjp ja_JP.ujis ja_JP.utf8 japanese japanese.euc So I changed the environment variables and now set my loc

How do I get the command line that starts the process

velvet: From Java, is it possible to get the full command line with all the arguments to start the application? System.getEnv()and System.getProperties()doesn't seem to contain these values. Stephen Denne: Some of these are available from RuntimeMXBean by call

How do I get the Windows version using the command line?

Yoder If I am in CMDI can usewinver I can see my OS version is 20H2now. But this is GUI result and I want to use command line to make it. if i use ver Microsoft Windows [Version 10.0.19042.868] If I'm in powershell, I can use Get-ComputerInfo WindowsVersion

How do I get the command line that starts the process

velvet: From Java, is it possible to get the full command line with all the arguments to start the application? System.getEnv()and System.getProperties()doesn't seem to contain these values. Stephen Denne: Some of these are available from RuntimeMXBean by call

How do I get Cj to send newlines in the tmux command line?

Sam I'm already a big fan of using C-jinstead of <ENTER>sending newlines. Unfortunately, in the tmux command line ( which is invoked by prefix-:default ) C-j, even set -gw status-keys emacsputting in does nothing ~/.tmux.conf. (Note that it C-jsends newlines f

How do I get the command line that starts the process

velvet: From Java, is it possible to get the full command line with all the arguments to start the application? System.getEnv()and System.getProperties()doesn't seem to contain these values. Stephen Denne: Some of these are available from RuntimeMXBean by call

How do I get Cj to send newlines in the tmux command line?

Sam I'm already a big fan of using C-jinstead of <ENTER>sending newlines. Unfortunately, in the tmux command line ( which is invoked by prefix-:default ) C-j, even set -gw status-keys emacsputting in does nothing ~/.tmux.conf. (Note that it C-jsends newlines f