utf8 non ascii characters are not accepted in the mysql cli when launched with --default-character-set=utf8

[Pierre François]

When you type

SELECT "é" AS letter;

at the prompt of the command line interface of the mysql client, the letter with an accent will not be echoed at the command line and the result of the command will be an empty field instead of a field containing the letter "é", as if the next command was issued:

SELECT "" AS letter;

As a workaround, you send letters with accents through a pipeline to the mysql client, typing at the shell prompt:

echo 'SELECT "é" AS letter;' | mysql

This version of the mysql client is configured to use the EditLine wrapper, which seems to be guilty of this behaviour.

ProblemType: Bug
DistroRelease: Ubuntu 18.04
Package: mysql-client-core-5.7 5.7.28-0ubuntu0.18.04.4
ProcVersionSignature: Ubuntu 4.15.0-70.79-generic 4.15.18
Uname: Linux 4.15.0-70-generic x86_64
ApportVersion: 2.20.9-0ubuntu7.9
Architecture: amd64
CurrentDesktop: ubuntu:GNOME
Date: Mon Nov 25 10:47:10 2019
InstallationDate: Installed on 2018-02-26 (636 days ago)
InstallationMedia: Ubuntu 17.10 "Artful Aardvark" - Release amd64 (20180105.1)
KernLog:

Logs.var.log.daemon.log:

Logs.var.log.mysql.error.log:
MySQLConf.etc.mysql.conf.d.mysql.cnf: [mysql]
MySQLConf.etc.mysql.conf.d.mysqldump.cnf:
 [mysqldump]
 quick
 quote-names
 max_allowed_packet = 16M
MySQLConf.etc.mysql.mysql.conf.d.mysqld_safe_syslog.cnf:
 [mysqld_safe]
 syslog
MySQLVarLibDirListing: False
SourcePackage: mysql-5.7
UpgradeStatus: Upgraded to bionic on 2018-05-08 (565 days ago)

[Pierre François (pf-openoffice)]

Output of `mysql --version` is:

mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper

[Andreas Hasenack (ahasenack)]

I get the expected result with your select test:
ubuntu@bionic-mysql:~$ mysql --version
mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper
ubuntu@bionic-mysql:~$ sudo mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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> SELECT "é" AS letter;
+--------+
| letter |
+--------+
| é |
+--------+
1 row in set (0.00 sec)

mysql>

Maybe your locale is set incorrectly?
Here is mine in that test system:
ubuntu@bionic-mysql:~$ locale
LANG=C.UTF-8
LANGUAGE=
LC_CTYPE="C.UTF-8"
LC_NUMERIC="C.UTF-8"
LC_TIME="C.UTF-8"
LC_COLLATE="C.UTF-8"
LC_MONETARY="C.UTF-8"
LC_MESSAGES="C.UTF-8"
LC_PAPER="C.UTF-8"
LC_NAME="C.UTF-8"
LC_ADDRESS="C.UTF-8"
LC_TELEPHONE="C.UTF-8"
LC_MEASUREMENT="C.UTF-8"
LC_IDENTIFICATION="C.UTF-8"
LC_ALL=

[Pierre François (pf-openoffice)]

I discovered today that I inherited in my current bash an alias from the setup of a previous installation:

alias mysql='mysql --default-character-set=utf8'

Since I removed that alias (with unalias mysql), the misbehaviour disappeared.

So, the description of the bug should be reformulated in the following way: "utf8 non ascii characters are not accepted as input of the mysql command line when launched with the option --default-character-set=utf8".

[Pierre François (pf-openoffice)]

I wonder how I have to edit the summary of the bug above. I don't know if it is a good idea anyway. Perhaps it is better to close this report and to open a new bug report with a new summary. Any advice?

[Paride Legovini (paride)]

Hello Pierre,

Thank you for taking the time to report this bug and helping to make Ubuntu better. I've been able to reproduce the issue using --default-character-set=utf8. Some more data points:

1. Reproduces in Bionic, Eoan, Focal.
2. Does NOT reproduce on Xenial (5.7.28-0ubuntu0.16.04.2).
3. Reproduces on Debian sid (5.7.26-1+b1).
4. Does NOT reproduce in Debian jessie (5.5.62-0+deb8u1).
5. Reproduces with --default-character-set=utf8mb4 (= true utf8).
6. Locale is always C.UTF-8.

I was tempted to call this an upstream bug, but given that it reproduces on Debian with version 5.7.26-1+b1 but does not reproduce on Xenial with version 5.7.28-0ubuntu0.16.04.2 this may not be the case. The bug requires further investigation.

[Christian Ehrhardt  (paelzer)]

I do not know where in between this got (re)fixed.
But in version 8.0.28-0ubuntu4 in Jammy I get the same correct behavior no matter what is set.

ubuntu@j:~$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.28-0ubuntu4 (Ubuntu)

Copyright (c) 2000, 2022, 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> SELECT "é" AS letter;
+--------+
| letter |
+--------+
| é |
+--------+
1 row in set (0.00 sec)

mysql>

---

ubuntu@j:~$ sudo mysql --default-character-set=utf8
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.28-0ubuntu4 (Ubuntu)

Copyright (c) 2000, 2022, 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> SELECT "é" AS letter;
+--------+
| letter |
+--------+
| é |
+--------+
1 row in set (0.00 sec)

mysql> ^DBye

---

ubuntu@j:~$ sudo mysql --default-character-set=utf8mb4
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.28-0ubuntu4 (Ubuntu)

Copyright (c) 2000, 2022, 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> SELECT "é" AS letter;
+--------+
| letter |
+--------+
| é |
+--------+
1 row in set (0.00 sec)

mysql> ^DBye

So whatever the issue is, it is fixed in between.

[Christian Ehrhardt  (paelzer)]

8.0.29-0ubuntu0.20.04.3 in Focal is ok as well.

[Sergio Durigan Junior (sergiodj)]

I could not reproduce this on Bionic anymore:

# apt policy mysql-server
mysql-server:
  Installed: 5.7.40-0ubuntu0.18.04.1
  Candidate: 5.7.40-0ubuntu0.18.04.1
  Version table:
 *** 5.7.40-0ubuntu0.18.04.1 500
        500 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages
        100 /var/lib/dpkg/status
     5.7.21-1ubuntu1 500
        500 http://archive.ubuntu.com/ubuntu bionic/main amd64 Packages

But I was able to determine that the problem affected mysql-client, and not mysql-server. There were a bunch of mysql-5.7 package updates since this bug was reported (due to security reasons), and I believe one of those updates fixed this problem indirectly.

Marking as Fix Released.