Bug #94435 mysql command hangs up and cosume CPU almost 100%
Submitted: 22 Feb 3:47 Modified: 2 Apr 9:24
Reporter: Masaaki HIROSE Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.7.25 OS:Linux
Assigned to: CPU Architecture:Any

[22 Feb 3:47] Masaaki HIROSE
Description:
mysql command hangs up, cosumes CPU (user%) almost 100% and cannot recover untill kill mysql command process.

I've confirmed to reproduce this problem with MySQL Community Edition 5.7.25 binary packages which provided by Oracle (https://dev.mysql.com/downloads/mysql/) on the following distributions.

- Ubuntu 18.10, mysql-community-client 5.7.25-1ubuntu18.10 
- Ubuntu 18.04, mysql-community-client 5.7.25-1ubuntu18.04
- Ubuntu 16.04, mysql-community-client 5.7.25-1ubuntu16.04
- CentOS 7.6, mysql-community-client-5.7.25-1.el7.x86_64
- Fedora 29, mysql-community-client-5.7.25-1.fc29.x86_64
- Oracle Linux 7.6, mysql-community-client-5.7.25-1.el7.x86_64

How to repeat:
1. connect to mysqld on *same host* via TCP/IP (not unix domain socket) with --disable-reconnect --sigint-ignore options

$ mysql --disable-reconnect --sigint-ignore -uroot -h127.0.0.1

2. type Control-C or kill -INT <PID of mysql> from another terminal

mysql> ^C

3. stop mysqld from another terminal

$ sudo service mysql stop

4. do query

mysql> select 1;

I expected that mysql returns "Lost connection to MySQL server during query" immediately, but this query does not return forever and consume almost 100% CPU time.

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
28191 hirose31  20   0   32504   6080   5476 R  99.7  0.3   0:17.94 mysql
[25 Feb 21:49] Bogdan Kecman
Hi,

Can you share more data as 5.7.25 on centos 7

$ mysql --disable-reconnect --sigint-ignore -umsandbox -pmsandbox -h127.0.0.1 -P5725 --protocol=tcp
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 3
Server version: 5.7.25 MySQL Community Server (GPL)

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> ^C
mysql> select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

I got lost... immediately upon select

all best
Bogdan
[26 Feb 7:02] Masaaki HIROSE
Hi,

I create a test instance on EC2 with AMI: CentOS Linux 7 x86_64 HVM EBS ENA 1901_01-b7ee8a69-ee97-4a49-9e68-afaee216db2e-ami-05713873c6794f575.4 (ami-045f38c93733dd48d).

after boot, install mysql57 community packages and start mysqld.

$ sudo rpm -Uvh mysql80-community-release-el7-2.noarch.rpm
$ sudo yum-config-manager --disable mysql80-community
$ sudo yum-config-manager --enable mysql57-community
$ sudo yum install mysql-community-server
$ sudo service mysqld start

$ rpm -qa | grep mysql | sort
mysql80-community-release-el7-2.noarch
mysql-community-client-5.7.25-1.el7.x86_64
mysql-community-common-5.7.25-1.el7.x86_64
mysql-community-libs-5.7.25-1.el7.x86_64
mysql-community-libs-compat-5.7.25-1.el7.x86_64
mysql-community-server-5.7.25-1.el7.x86_64

$ cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)

$ mysql --disable-reconnect --sigint-ignore -uscott -ptiger -h127.0.0.1 -P3306 --protocol=tcp

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25

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> ^C
mysql> select 1;
(hangs up)

I've taken screencast: http://www.irori.org/tmp/94435.gif
Could you see it?
[26 Feb 10:22] Bogdan Kecman
Hi,
Thanks, weird, if I'm running mysql in gdb it does not happen, if mysqld is running on other port it does not happen ... this is a weird one, but verified.

all best
Bogdan
[26 Feb 11:47] Masaaki HIROSE
I sincerely glad that you can reproduce!!!

I am waiting for this problem to be fixed.
[27 Feb 4:54] Masaaki HIROSE
Hi,

IMHO, the root cause of this problem may be race conditions around vio_ssl_read called via mysql_real_query (libmysqlclient), so potentially all programs using libmysqlclient may be affected.

- mysql* client programs
- MySQL drivers for programming languages (Ruby, Perl and so on)
- products linking libmysqlclient.so (PowerDNS, MyDNS or in-house one)

At first, I've found this problem with MyDNS.

And sending signal is not only SIGINT but arbitrary signals.
I've checked SIGTERM, ALRM, USR1 with my small PoC code:

https://gist.github.com/hirose31/3440065a7bdc9d77f1c70ec8bc007ad5#file-infinite-loop-c

In this code, set small value (eg: 10) to wait-timeout and mysqld disconnects after wait-timeout seconds. (not down mysqld)

In summary, the generalized reproduction procedure is as follows.

1. connect to mysqld on *same host* via TCP/IP (not unix domain socket) without reconnect option
2. recieve arbitrary signal (and handle it, do not exit)
3. disconnect from mysqld
  - stop mysqld
  - exceed wait-timeout or interactive_timeout
  - and so on...
# It can reproduce that 3 (disconnect from mysqld) and then 2 (recieve signal)
4. do query (mysql_real_query() or mysql_ping())
5. -> entering infinite loop and consume almost 100% CPU time

I think these issues are the same cause.
https://bugs.mysql.com/bug.php?id=88428
https://bugs.mysql.com/bug.php?id=92394

First I've reported in category "MySQL Server: C API (client library)".
https://bugs.mysql.com/bug.php?id=94219

But the assignee does/can not try to reproduce it at all, I gave up...

I know MySQL 8 uses OpenSSL instead of yaSSL, so this problem does not occur with libmysqlclient of MySQL 8. But MySQL 5.7 is living until 2020/10.

I hope this problem will be fixed or appropriate workarounds will be presented.
[2 Apr 9:24] Masaaki HIROSE
How is it going?