#StackBounty: #debian #mysql Why can't paste or type special character into MariaDB shell?

Bounty: 50

My os info:

uname -a
Linux debian 5.10.0-8-amd64 #1 SMP Debian 5.10.46-4 (2021-08-03) x86_64 GNU/Linux

Locale setting:

locale 
LANG=en_HK.UTF-8
LANGUAGE=en_HK:en
LC_CTYPE="en_HK.UTF-8"
LC_NUMERIC="en_HK.UTF-8"
LC_TIME="en_HK.UTF-8"
LC_COLLATE="en_HK.UTF-8"
LC_MONETARY="en_HK.UTF-8"
LC_MESSAGES="en_HK.UTF-8"
LC_PAPER="en_HK.UTF-8"
LC_NAME="en_HK.UTF-8"
LC_ADDRESS="en_HK.UTF-8"
LC_TELEPHONE="en_HK.UTF-8"
LC_MEASUREMENT="en_HK.UTF-8"
LC_IDENTIFICATION="en_HK.UTF-8"
LC_ALL=

Copy a special character å from webpage in the clipboard,and i can paste it into both mousepad and shell terminal, it can be shown normally.

enter image description here

The mysql version info:

mysql --version
mysql  Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

The character and collation setting in MariaDB:

MariaDB [(none)]> show variables like '%char%';
+--------------------------+----------------------------+
| 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     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)
show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

I can paste it into sqlite3 shell from clipboard,and can type the special character into sqlite3 shell!

enter image description here

And i can execute msyql select command on bash shell and show special character.

debian@debian:~$ mysql -u root -D test -p -e 'select "å"  ;'
+----+
| å  |
+----+
| å  |
+----+

Mariadb shell can show special character properly:

select * from `character`;
+---------+
| special |
+---------+
| å       |
+---------+
1 row in set (0.000 sec)

Enter into MariaDB ,mouse cursor into MariaDB shell, the special character å can’t be pasted into MariaDB shell,and i can’t type special character å into the Mariadb shell too!
I can’t type the below sql command in Mariadb shell:

select * from `character` where special="å";

How can make MariaDB shell accept it as input and show it normally?
I solved it by using MariaDB’s default setting.

sudo vim /etc/mysql/mariadb.cnf
# i delete all my customized setting.
[client]
[mysql]
[client-server]
socket = /run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

sudo systemctl restart mysqld
mysql -u root -p
MariaDB [(none)]> show variables like '%collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)

MariaDB [(none)]> select "å" ;
+----+
| å  |
+----+
| å  |
+----+
1 row in set (0.000 sec)

Now i can type or paste and show special character in MariaDB shell.
Please give a explanation why can type and show special character in MariaDB shell when to use default setting?

@roaima,here is my customised settings which result in failure on pasting and typing special character.

cat    /etc/mysql/mariadb.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'

Here is the default settings which result in success on pasting and typing special character.

cat /etc/mysql/mariadb.cnf
[client]
[mysql]
[client-server]
socket = /run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.