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_unicode_520_ci |
| collation_server | utf8mb4_unicode_520_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_unicode_520_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 ('');