MySQL Bugs: #95699: NDB Cluster, foreign key constraint fails on table containing LONGTEXT column

Bug #95699	NDB Cluster, foreign key constraint fails on table containing LONGTEXT column
Submitted:	9 Jun 2019 5:38	Modified:	17 Aug 2019 19:21
Reporter:	Chad Phillips	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Documentation	Severity:	S3 (Non-critical)
Version:	Ver 14.14 Distrib 5.7.26-ndb-7.6.10	OS:	Debian (9)
Assigned to:		CPU Architecture:	x86
Tags:	ndb

Description:
What I did:
 - Created 'parent' table with primary key
 - Created 'child' table with primary key, parent_id column, and a LONGTEXT column
 - Attempted to add a foreign key constraint with ON DELETE CASCADE from child.parent_id to parent.id

What I wanted to happen:

The constraint to be created successfully

What actually happened:

Adding the constraint failed with: ERROR 1215 (HY000): Cannot add foreign key constraint

Note that if I remove the LONGTEXT column from the child table, the constraint can be successfully created.

I've reviewed section 3.7.5: Limits Associated with Database Objects in NDB Cluster (https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-limitations-database-...) and section 3.7.1: Noncompliance with SQL Syntax in NDB Cluster (https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-limitations-syntax.ht...) from the manual, and see nothing in there that indicates this operation should fail.

I've also reviewed the Percona blog post related to the "ERROR 1215 (HY000): Cannot add foreign key constraint" error that's received, and nothing in there points to a solution either: https://www.percona.com/blog/2017/04/06/dealing-mysql-error-code-1215-cannot-add-foreign-k...

How to repeat:
mysql> CREATE TABLE `parent` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE TABLE `child` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `parent_id` bigint(20) NOT NULL,
    ->   `data` longtext,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.30 sec)

mysql> ALTER TABLE `child` ADD CONSTRAINT `fk_child_parent`
    -> FOREIGN KEY (`parent_id`)
    ->   REFERENCES `parent` (`id`)
    ->  ON DELETE CASCADE;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql>
mysql>
mysql> ALTER TABLE `child` DROP data;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `child` ADD CONSTRAINT `fk_child_parent`
    -> FOREIGN KEY (`parent_id`)
    ->   REFERENCES `parent` (`id`)
    ->  ON DELETE CASCADE;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
No suggestions

NDB error report archive

Attachment: ndb_error_report_20190608222621.tar.bz2 (application/x-bzip, text), 110.09 KiB.

Hi,
I cannot reproduce this?

node1 [localhost:27510] {msandbox} (test) > CREATE TABLE `parent` (
    ->  `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->  PRIMARY KEY (`id`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.68 sec)

node1 [localhost:27510] {msandbox} (test) > CREATE TABLE `child` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    -> `parent_id` bigint(20) NOT NULL,
    ->  `data` longtext,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.65 sec)

node1 [localhost:27510] {msandbox} (test) > ALTER TABLE `child` ADD CONSTRAINT `fk_child_parent`
    -> FOREIGN KEY (`parent_id`)
    ->   REFERENCES `parent` (`id`)
    ->   ON DELETE CASCADE;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

node1 [localhost:27510] {msandbox} (test) > show create table parent\G
*************************** 1. row ***************************
       Table: parent
Create Table: CREATE TABLE `parent` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

node1 [localhost:27510] {msandbox} (test) > show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) NOT NULL,
  `data` longtext,
  PRIMARY KEY (`id`),
  KEY `fk_child_parent` (`parent_id`),
  CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

node1 [localhost:27510] {msandbox} (test) >

Hm, where do we go from here?

My installation of the cluster is totally stock, and this problem is occurring.

Do the debug logs I sent reveal anything?

The error message received isn't very specific -- how would we go about figuring out what exact condition is triggering the error?

Hi,
Your cluster config is rather basic so I doubt it has to do with that. Can you upload your my.cnf for sql nodes please

This configuration is used on both SQL nodes:

[mysqld]
# run NDB storage engine
ndbcluster
ndb-connectstring=192.168.100.15:1186
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysql_cluster]
# location of management server
ndb-connectstring=192.168.100.15:1186

Hi,

Still can't reproduce this. 

Are you using binaries from Oracle or you are using some other (your distro) binaries?

thanks
Bogdan

I'm using the officially provided Debian packages. These are the lines in my sources.list file that point to the official MySQL repos:

deb http://repo.mysql.com/apt/debian stretch mysql-cluster-7.6
deb http://repo.mysql.com/apt/debian stretch mysql-5.7

List of installed packages:

libmysqlclient-dev/unknown,now 7.6.10-1debian9 amd64 [installed]
libmysqlclient20/unknown,now 7.6.10-1debian9 amd64 [installed,automatic]
mysql-client/unknown,now 7.6.10-1debian9 amd64 [installed]
mysql-cluster-community-client/unknown,now 7.6.10-1debian9 amd64 [installed,automatic]
mysql-cluster-community-data-node/unknown,now 7.6.10-1debian9 amd64 [installed]
mysql-cluster-community-management-server/unknown,now 7.6.10-1debian9 amd64 [installed]
mysql-cluster-community-server/unknown,now 7.6.10-1debian9 amd64 [installed]
mysql-common/unknown,now 7.6.10-1debian9 amd64 [installed,automatic]
mysql-server/unknown,now 7.6.10-1debian9 amd64 [installed]

# aptitude show mysql-cluster-community-server
Package: mysql-cluster-community-server  
Version: 7.6.10-1debian9
New: yes
State: installed
Automatically installed: no
Priority: optional
Section: database
Maintainer: MySQL Release Engineering <mysql-build@oss.oracle.com>
Architecture: amd64
Uncompressed Size: 191 M

Hi,

Verified on OSX mysql-cluster-gpl-7.6.10-macos10.14-x86_64.tar.gz

not sure why / how it works on linux, same version of mccge

select @@version;
+-----------------------------------+
| @@version                         |
+-----------------------------------+
| 5.7.26-ndb-7.6.10-cluster-gpl-log |
+-----------------------------------+

node1 [localhost:27611] {msandbox} ((none)) > use test
Database changed
node1 [localhost:27611] {msandbox} (test) > CREATE TABLE `parent` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.19 sec)

node1 [localhost:27611] {msandbox} (test) > CREATE TABLE `child` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `parent_id` bigint(20) NOT NULL, `data` longtext,PRIMARY KEY (`id`)) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.22 sec)

node1 [localhost:27611] {msandbox} (test) > ALTER TABLE `child` ADD CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`)   REFERENCES `parent` (`id`)  ON DELETE CASCADE;
ERROR 1215 (HY000): Cannot add foreign key constraint
node1 [localhost:27611] {msandbox} (test) > ALTER TABLE `child` DROP data;
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

node1 [localhost:27611] {msandbox} (test) > ALTER TABLE `child` ADD CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`)   REFERENCES `parent` (`id`) ON DELETE CASCADE;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

node1 [localhost:27611] {msandbox} (test) >

This is expected, and is due to the fix for BUG#89511 (NDB 7.3.25, 7.4.24, 7.5.14, 7.6.10, 8.0.16).

The documentation for all affected versions has been updated to reflect this.

Thanks for bringing this to our attention.

Closed.

I've reviewed https://bugs.mysql.com/bug.php?id=89511, and I'm left feeling confused as to why the problem was solved by preventing foreign keys to child tables with a TEXT/BLOB column.

If the problem is a memory leak, isn't there some other method of cleaning up the leak than preventing this functionality? It seems like an awfully big restriction to place on this engine to solve that problem.

Can someone further clarify for me?