MySQL Bugs: #94435: mysql command hangs up and cosume CPU almost 100%

Bug #94435	mysql command hangs up and cosume CPU almost 100%
Submitted:	22 Feb 2019 3:47	Modified:	2 Apr 2019 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

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

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

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?

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

I sincerely glad that you can reproduce!!!

I am waiting for this problem to be fixed.

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.

How is it going?