I'm having an issue getting UTF-8 characters to work in the MySQL command line when I am running MySQL 8 in a Docker container.
When I copy and paste a UTF-8 character into Terminal, the character is deleted.
For example
mysql> INSERT INTO your_table (name) VALUES ('🤌');
becomes
mysql> INSERT INTO your_table (name) VALUES ('');
I have tried three different ways to set up the Docker container, and they all have the same result. I have tried using the default encodings, the command line parameters (as per the documentation) and using a custom my.cnf file.
I can see the MySQL character_set variables change, but I cannot copy and paste UTF-8 characters into the MySQL command line. However, I can copy and paste UTF-8 characters via the MySQL command line for my local MySQL 5.7 database, and I can add UTF-8 characters to all 3 databases using the Intell-J DB Browser plugin.
Is this an issue with the Docker image setup of the MySQL command line tool?
Any ideas?
Details of the tests below:
1 No encodings specified:
% docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=root -p 9306:3306 -d mysql:8
% docker exec -it some-mysql mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| collation_connection | latin1_swedish_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
10 rows in set (0.05 sec)
mysql> INSERT INTO your_table (name) VALUES ('');
2 Configuration without a conf file (using command line parameters to set the character-set)
% docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=root -p 9306:3306 -d mysql:8 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
% docker exec -it some-mysql mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| collation_connection | latin1_swedish_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.18 sec)
mysql> INSERT INTO your_table (name) VALUES ('');
3 Using a MySQL Configuration file
% cat /Users/me/Code/xxx/docker/mysql/conf.d/my.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
% docker run --name some-mysql -v /Users/me/Code/xxx/docker/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=root -p 7306:3306 -d mysql:8
% docker exec -it some-mysql mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------------------+
11 rows in set (0.01 sec)
mysql> INSERT INTO your_table (name) VALUES ('');
The problem was two fold:
I created a custom MySQL 8 image using this Dockerfile, which used a Debian install to copy sytems fonts:
# Stage 1: Use Debian to install locales and fonts
FROM debian:latest as build
# Install locales and fonts
RUN apt-get update && \
apt-get install -y locales fonts-dejavu-core && \
locale-gen C.UTF-8 || true && \
dpkg-reconfigure --frontend=noninteractive locales && \
update-locale LANG=C.UTF-8
# Stage 2: Use official MySQL image as the base image
FROM mysql:8
# Set environment variables for UTF-8 locale
ENV LANG=C.UTF-8
ENV LC_ALL=C.UTF-8
# Copy locales and fonts from the build stage
COPY --from=build /usr/share/fonts /usr/share/fonts
COPY --from=build /usr/lib/locale /usr/lib/locale
COPY --from=build /usr/bin/locale /usr/bin/locale
COPY --from=build /usr/sbin/locale-gen /usr/sbin/locale-gen
COPY --from=build /etc/locale.gen /etc/locale.gen
COPY --from=build /usr/bin/localedef /usr/bin/localedef
# Expose MySQL port
EXPOSE 3306
# Set default command
CMD ["mysqld"]
I built the custom Docker image with this command:
docker build -t custom-mysql:8 .
I created a new Docker container using this image:
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=root -p 9306:3306 -d custom-mysql:8
When I logged into the MySQL command line tool, I was finally able to copy and paste UTF-8 characters:
% docker exec -it some-mysql mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> INSERT INTO your_table (name) VALUES ('🤌');