MySQL Bugs: #89511: leak with 'cascade on delete' using the ndb engine

Bug #89511	leak with 'cascade on delete' using the ndb engine
Submitted:	2 Feb 2018 10:08	Modified:	8 Aug 2019 16:59
Reporter:	Paolo Ganci	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	MySQL distrib mysql-5.7.21 ndb-7.5.9, fo	OS:	Red Hat (Linux nevisproxy-rhel7-nb 3.10.0-514.21.2.el7.x86_64 #1 SMP Sun May 28 17:08:21 EDT 2017 x86_64 x86_)
Assigned to:		CPU Architecture:	Any

Description:
If you have two tables and one of them having a foreign key to the other, with an 'ON DELETE CASCADE', and a TEXT column, then you can fill the memory up to 100% and it will never go back, unless you shutdown and reinit the server.

How to repeat:
Create the following tables:
drop table if exists theParent;
drop table if exists theChild;

CREATE TABLE theParent (
    ID VARCHAR(255) NOT NULL,
    OWNER_ID BIGINT(22) NOT NULL DEFAULT 0,
    PRIMARY KEY(ID)
) ENGINE = NDB;

CREATE TABLE theChild (
	CHILD_ID INT NOT NULL AUTO_INCREMENT,
	ID VARCHAR(255) NOT NULL,
	VALUE TEXT,
	PRIMARY KEY(CHILD_ID),
	FOREIGN KEY(ID) REFERENCES theParent(ID) ON DELETE CASCADE
) ENGINE = NDB;

call this endlessly:

insert into theParent (ID, OWNER_ID) values ('abc', 12345);
insert into theChild (ID, VALUE) values ('abc', 'something bigger than 256 kb 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789 0123456789');
delete from theParent where OWNER_ID=12345;

check now the memory-usage (ndb_mgm --ndb_connectstring=localhost:1186 -e 'all report memory') it will never decrease and reach 100% at a certain point. 

Suggested fix:
adding this line:
delete from theChild where ID='abc';

before calling
delete from theParent where OWNER_ID=12345;

and running the endless test again keeps the memory to 0%.

This bug report originates from the problem discussed in the Cluster mail list thread https://lists.mysql.com/cluster/8737 .

Verified for 7.4.19 and 7.6.4.

By check row counts for tables using ndbinfo.memory_per_fragment one can see that blob table for theChild keeps its rows even after the cascade delete.

select distinct fq_name, parent_fq_name, fixed_elem_count
from ndbinfo.memory_per_fragment
where fixed_elem_count > 0;

After instert:

fq_name	parent_fq_name	fixed_elem_count
test/def/NDB$BLOB_20_2	test/def/theChild	5
test/def/theChild	NULL	1
test/def/theParent	NULL	1

After delete, shows that rows remains in blob table:
fq_name	parent_fq_name	fixed_elem_count
test/def/NDB$BLOB_20_2	test/def/theChild	5

Documented fix in the NDB 7.3.25, 7.4.24, 7.5.14, 7.6.10, and 8.0.16 changelogs as follows:

    An NDB table having both a foreign key on another NDB table
    using ON DELETE CASCADE and a TEXT or BLOB column leaked memory.

Closed.

Note that, as a result of this fix, foreign keys are no longer supported for NDB tables where the child table has any TEXT or BLOB columns.