MySQL Bugs: #109705: Flush table deadlock

Bug #109705	Flush table deadlock
Submitted:	19 Jan 2023 15:51	Modified:	25 Jan 2023 13:13
Reporter:	Pedro Ferreira	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S6 (Debug Builds)
Version:	8.0.32	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	flush

Description:
On a brand new database instance run the following statements:

CREATE TABLE t0 (c0 INT);
CREATE SCHEMA s1;
ALTER SCHEMA sys READ ONLY = 1;
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
FLUSH TABLES t0 WITH READ LOCK;

On the last flush statement, the server will hang forever.

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.

Hello Pedro Ferreira,

Thank you for the report and test case.
I quickly tried to reproduce but not seeing any hang etc. Am I missing anything here? Please let me know. I'm attaching the steps for your reference:

--
scl enable devtoolset-11 bash
MYSQL_VERSION="8.0.32"
TARGET=/export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf /export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf bld/
mkdir bld && cd bld
rm -rf CMakeCache.txt
/home/umshastr/work/binaries/utils/cmake-3.19/bin/cmake .. -DWITH_BOOST=../boost -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ -DWITH_PACKAGE_FLAGS=OFF  -DCMAKE_INSTALL_PREFIX=$TARGET -DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON

make -j 16
make install
cd /export/home/tmp/ushastry/src/$MYSQL_VERSION

rm -rf 109711/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/109711 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/109711 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/109711/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency --server-id=1 2>&1 &

-
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.32-debug-asan-ubsan Source distribution

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> CREATE TABLE t0 (c0 INT);
ERROR 1046 (3D000): No database selected
mysql> CREATE SCHEMA s1;
Query OK, 1 row affected (0.01 sec)

mysql> ALTER SCHEMA sys READ ONLY = 1;
Query OK, 1 row affected (0.37 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES t0 WITH READ LOCK;
ERROR 1046 (3D000): No database selected
mysql>
mysql> create database test;
Query OK, 1 row affected (0.02 sec)

mysql> use test
Database changed
mysql> CREATE TABLE t0 (c0 INT);
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE SCHEMA s1;
ERROR 1007 (HY000): Can't create database 's1'; database exists
mysql> ALTER SCHEMA sys READ ONLY = 1;
Query OK, 1 row affected (0.05 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES t0 WITH READ LOCK;
Query OK, 0 rows affected (0.05 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

regards,
Umesh

Hello,

I think this is a race condition. I am able to reproduce it with a script. A simple C program with a MySQL connection may do it.

Hello Pedro Ferreira,

Thank you for the details, I'm still not able to reproduce.
May I request you to please share the script which you are using to reproduce the reported issue? Thank you.

regards,
Umesh

This is what I do to reproduce it.

First, create the database:

bin/mysqld --log-error-verbosity=3 --initialize-insecure --user=mysql --basedir=... --datadir=... --default-authentication-plugin=mysql_native_password

Then start it:

bin/mysqld --no-defaults --port=3306 --mysqlx-port=3307 --slave-parallel-type=LOGICAL_CLOCK --slave-parallel-workers=4 --slave-preserve-commit-order=ON --enforce-gtid-consistency --gtid-mode=ON --binlog-format=ROW --server-id=1 --log-error=... --log-error-verbosity=2 --basedir... --datadir=... --plugin-dir=... --pid-file=... --core-file --default-authentication-plugin=mysql_native_password

Then compile this C++ program

```
#include <mysql.h>
#include <vector>
#include <iostream>

static int
SendQuery(MYSQL *hdl, MYSQL_RES **res, const std::string &query) {
	int ok = 0;

	if ((ok = mysql_query(hdl, query.c_str()))) {
		std::cerr << "Error while processing query: " << mysql_error(hdl) << std::endl;
	} else {
		*res = mysql_store_result(hdl);
		if (mysql_errno(hdl)) {
			std::cerr << "Error while retrieving query result: " << mysql_error(hdl) << std::endl;
		}
	}
	return ok;
}

static int
CleanResult(MYSQL *hdl, MYSQL_RES **res) {
	int ok = 0;

	while (!ok) {
		mysql_free_result(*res);
		if ((ok = mysql_next_result(hdl)) > 0) {
			std::cerr << "Error while retrieving query result: " << mysql_error(hdl) << std::endl;
		} else if (!ok) {
			*res = mysql_store_result(hdl);
			if (mysql_errno(hdl)) {
				std::cerr << "Error while retrieving query result: " << mysql_error(hdl) << std::endl;
				ok = 1;
			}
		}
	}
	*res = nullptr;
	return ok;
}

std::vector<std::string> statements = {"CREATE TABLE t0 (c0 INT);","CREATE SCHEMA s1;",
"ALTER SCHEMA sys READ ONLY = 1;","FLUSH TABLES WITH READ LOCK;","UNLOCK TABLES;",
"FLUSH TABLES t0 WITH READ LOCK;"};

int
main(void) {
	int ok = 0;
	MYSQL *hdl = nullptr;
	MYSQL_RES *res = nullptr;

	if (mysql_library_init(0, nullptr, nullptr)) {
		return 1;
	}

	if (mysql_thread_init()) {
		mysql_library_end();
		return 1;
	}

	if (!(hdl = mysql_init(nullptr))) {
		mysql_thread_end();
		mysql_library_end();
		return 1;
	}
	if (!mysql_real_connect(hdl, "localhost", "root", nullptr, "sys", 3306, nullptr, 0)) {
		mysql_close(hdl);
		mysql_thread_end();
		mysql_library_end();
		return 1;
	}

	for (auto st : statements) {
		(void) SendQuery(hdl, &res, st);
		(void) CleanResult(hdl, &res);
	}

	mysql_close(hdl);
	mysql_thread_end();
	mysql_library_end();
	return 0;
}
```

with 

clang++ -g --std=c++2a -o issue program.cpp -fsanitize=address,undefined -I<mysql build include directory> -L<mysql build library directory> -lmysqlclient

then run it on a fast machine and it should hang on the second FLUSH statement.

After some time hanging I get an assertion error at ut0dbg.cc:99

my_abort();

Thank you, Pedro Ferreira.

regards,
Umesh