MySQL Bugs: #100493: Cross schema foreign key blocks actions while stored functions are running

Bug #100493	Cross schema foreign key blocks actions while stored functions are running
Submitted:	11 Aug 2020 9:43	Modified:	12 Aug 2020 6:02
Reporter:	Andre Scheidtmann	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Debian (18.04.)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

we have one database per tenant and one shared database which is used by all tenants.

There is a foreign key in each tenant database which points to a table in the shared database.

And there is a long running stored functions which is inside the tenants database which involves queries against the tenant database and the shared database.

Scince MySQL 8 we have got the problem that while the long running stored function is running we can´t add or drop other cross schema foreign keys.

The processlist shows a metadata lock for the ALTER TABLE.

How to repeat:
# Setup

DROP DATABASE IF EXISTS shared;
CREATE DATABASE shared;
CREATE TABLE shared.shared_table (SharedID INT PRIMARY KEY);
INSERT INTO shared.shared_table VALUES(1);

DROP DATABASE IF EXISTS a;
CREATE DATABASE a;
CREATE TABLE a.t (ID INT, SharedID INT);
ALTER TABLE a.t ADD CONSTRAINT FK_a_SharedID FOREIGN KEY (SharedID) REFERENCES shared.shared_table (SharedID) ON UPDATE CASCADE ON DELETE CASCADE;

DROP DATABASE IF EXISTS b;
CREATE DATABASE b;
CREATE TABLE b.t (ID INT, SharedID INT);
ALTER TABLE b.t ADD CONSTRAINT FK_b_SharedID FOREIGN KEY (SharedID) REFERENCES shared.shared_table (SharedID) ON UPDATE CASCADE ON DELETE CASCADE;

DELIMITER |
DROP FUNCTION IF EXISTS a.test|
CREATE FUNCTION a.test()
RETURNS VARCHAR (255) READS SQL DATA
BEGIN
DECLARE result BOOL;
  SELECT SLEEP(60) FROM shared.shared_table INTO @blackhole;

  RETURN '';
END|

DELIMITER ;

# Session A

SELECT a.test();

# Session B

ALTER TABLE b.t DROP FOREIGN KEY FK_b_SharedID;

Hi Mr. Scheidtmann,

Thank you for your bug report.

However, we need additional info.

First of all, we would like to know whether you experience the same problem with 8.0.21. You are using an old release and there are hundreds of bugs fixed interim.

Second, if and when you get a lock with 8.0.21, please send us a full output from InnoDB status, including all the locks being held.

# MTR repro testcase

CREATE DATABASE shared;
CREATE TABLE shared.shared_table (SharedID INT PRIMARY KEY);
INSERT INTO shared.shared_table VALUES(1);

CREATE DATABASE a;
CREATE TABLE a.t (ID INT, SharedID INT);
ALTER TABLE a.t ADD CONSTRAINT FK_a_SharedID FOREIGN KEY (SharedID) REFERENCES shared.shared_table (SharedID) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE DATABASE b;
CREATE TABLE b.t (ID INT, SharedID INT);
ALTER TABLE b.t ADD CONSTRAINT FK_b_SharedID FOREIGN KEY (SharedID) REFERENCES shared.shared_table (SharedID) ON UPDATE CASCADE ON DELETE CASCADE;

DELIMITER |;
CREATE FUNCTION a.test()
RETURNS VARCHAR (255) READS SQL DATA
BEGIN
DECLARE result BOOL;
  SELECT SLEEP(20) FROM shared.shared_table INTO @blackhole;

  RETURN '';
END|

DELIMITER ;|

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

# Session A
--connect(conA, localhost, root,,)
--connection conA
--send SELECT a.test();

# Session B
--connect(conB, localhost, root,,)
--connection conB
--send ALTER TABLE b.t DROP FOREIGN KEY FK_b_SharedID;

# Default connection
--connection default

USE performance_schema;
SELECT thread_id, processlist_command, processlist_state, processlist_info, object_type, object_schema, object_name, lock_type, lock_status  FROM threads JOIN metadata_locks ON (metadata_locks.owner_thread\
_id = threads.thread_id) WHERE (object_schema = "shared");

--connection conA
--reap

--connection conB
--reap

--connection default
--disconnect conA
--disconnect conB

DROP DATABASE a;
DROP DATABASE b;
DROP DATABASE shared;

Output from P_S shows that the alter is hanging on a metadata lock:

SELECT thread_id, processlist_command, processlist_state, processlist_info, object_type, object_schema, object_name, lock_type, lock_status  FROM threads JOIN metadata_locks ON (metadata_locks.owner_thread_id = threads.thread_id) WHERE (object_schema = "shared");
thread_id       processlist_command     processlist_state       processlist_info        object_type     object_schema   object_name     lock_type       lock_status
49      Query   User sleep      SELECT SLEEP(20) FROM shared.shared_table INTO @blackhole       COLUMN STATISTICS       shared  shared_table    SHARED_READ     GRANTED
49      Query   User sleep      SELECT SLEEP(20) FROM shared.shared_table INTO @blackhole       TABLE   shared  shared_table    SHARED_READ     GRANTED
49      Query   User sleep      SELECT SLEEP(20) FROM shared.shared_table INTO @blackhole       SCHEMA  shared  NULL    INTENTION_EXCLUSIVE     GRANTED
50      Query   Waiting for table metadata lock ALTER TABLE b.t DROP FOREIGN KEY FK_b_SharedID  SCHEMA  shared  NULL    INTENTION_EXCLUSIVE     GRANTED
50      Query   Waiting for table metadata lock ALTER TABLE b.t DROP FOREIGN KEY FK_b_SharedID  TABLE   shared  shared_table    SHARED_UPGRADABLE       GRANTED
50      Query   Waiting for table metadata lock ALTER TABLE b.t DROP FOREIGN KEY FK_b_SharedID  TABLE   shared  shared_table    EXCLUSIVE       PENDING
a.test()

8.0.18 and later versions behave in the same way.

I just found out that the stored function isn´t even needed to produce this behaviour. You just need to run a long query against the shared table.

#Setup

DROP DATABASE IF EXISTS a;
DROP DATABASE IF EXISTS b;
DROP DATABASE IF EXISTS shared;

CREATE DATABASE shared;
CREATE TABLE shared.shared_table (SharedID INT PRIMARY KEY);
INSERT INTO shared.shared_table VALUES(1);

CREATE DATABASE a;
CREATE TABLE a.t (ID INT, SharedID INT);
ALTER TABLE a.t ADD CONSTRAINT FK_a_SharedID FOREIGN KEY (SharedID) REFERENCES shared.shared_table (SharedID) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE DATABASE b;
CREATE TABLE b.t (ID INT, SharedID INT);
ALTER TABLE b.t ADD CONSTRAINT FK_b_SharedID FOREIGN KEY (SharedID) REFERENCES shared.shared_table (SharedID) ON UPDATE CASCADE ON DELETE CASCADE;

# Session A

SELECT SLEEP(60) FROM shared.shared_table;

# Session B

ALTER TABLE b.t DROP FOREIGN KEY FK_b_SharedID;

Thank you, Erlend.

Posted by developer:
 
Comment from one of the MySQL architects (Dmitry L): unfortunately for the user it looks like the expected result of addition of MDL locking for foreign keys in 8.0. We need to acquire an exclusive lock on the parent table at some point when we add FK, hence concurrent usage of parent table by long running trxes and addition of FKs is problematic.