Bug #108881 FLUSH TABLES deadlock
Submitted: 26 Oct 2022 8:46 Modified: 28 Oct 2022 11:03
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:8.0.31 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:x86 (x86_64)
Tags: flush

[26 Oct 2022 8:46] Pedro Ferreira
Description:
The following C++ program deadlocks sometimes with MySQL 8.0.31. Compile and link it with MySQL C client library.

/*--------------------------------------------------------------------------*/
#include <mysql.h>
#include <thread>
#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;
			}
		}
	}
	return ok;
}

int
main(void) {
	int ok = 0;

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

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

	for (int i = 0 ; i < 20 && !ok; i++) {
		MYSQL *hdl = nullptr;
		MYSQL_RES *res = nullptr;

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

		ok |= SendQuery(hdl, &res, "DROP TABLE IF EXISTS t1;");
		ok |= CleanResult(hdl, &res);
		ok |= SendQuery(hdl, &res, "DROP TABLE IF EXISTS t2;");
		ok |= CleanResult(hdl, &res);
		ok |= SendQuery(hdl, &res, "CREATE TABLE t2 (c0 INT) AS (SELECT 5);");
		ok |= CleanResult(hdl, &res);
		ok |= SendQuery(hdl, &res, "CREATE TABLE t1 LIKE t2;");
		ok |= CleanResult(hdl, &res);
		ok |= SendQuery(hdl, &res, "FLUSH TABLES WITH READ LOCK;");
		ok |= CleanResult(hdl, &res);
		ok |= SendQuery(hdl, &res, "FLUSH TABLES t1 FOR EXPORT;");
		ok |= CleanResult(hdl, &res);

		mysql_close(hdl);
	}
	mysql_thread_end();
	mysql_library_end();
	return ok;
}
/*--------------------------------------------------------------------------*/

I think the issue arises from the consecutive FLUSH commands.

My current MySQL compilation parameters are the same as issue 108148:

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

However, this may be reproducible in the release build.

This is the database creation command:
mysqld --log-error-verbosity=3 --initialize-insecure --user=mysql --basedir=<path to basedir> --datadir=<path to datadir> --default-authentication-plugin=mysql_native_password

This is the command to run the database:
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=logerror.err --log-error-verbosity=2 --basedir=<path to basedir> --datadir=<path to datadir> --plugin-dir=<path to plugindir> --pid-file=mysqld.pid --core-file --default-authentication-plugin=mysql_native_password

How to repeat:
Run the program above.
[28 Oct 2022 11:03] MySQL Verification Team
Hello Pedro Ferreira,

Thank you for the report and test case.

regards,
Umesh