Description:
When linking 2 tables with one another using the newly included foreign key feature for the 'ndb' engine, the insert, update and delete operations perform as expected. But after restarting the management server (ndb_mgmd), the foreign key constraint disappears from the table definition, and all the DML queries are processed without the foreign key constraint.
How to repeat:
# Start the Management Server
ndb_mgmd -f <path-to-config.ini> --initial --configdir <path-to-conf>
# Start the Data Nodes
ndbd -c localhost:1186
ndbd -c localhost:1186
# Check Status
stryker@stryker-desktop:~$ ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0, Master)
id=4 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1)
[mysqld(API)] 3 node(s)
id=50 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1)
id=51 (not connected, accepting connect from any host)
id=52 (not connected, accepting connect from any host)
# Create tables using the following script :
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 ;
USE `test` ;
-- -----------------------------------------------------
-- Table `test`.`test_table`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`test_table` (
`id` INT NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = ndbcluster;
-- -----------------------------------------------------
-- Table `test`.`fk_test_table`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`fk_test_table` (
`id` INT NOT NULL ,
`fk_id` INT NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_test_idx` (`fk_id` ASC) ,
CONSTRAINT `fk_test`
FOREIGN KEY (`fk_id` )
REFERENCES `test`.`test_table` (`id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT)
ENGINE = ndbcluster;
# Check the validity of the Foreign Key Constraint
mysql> show create table test_table; show create table fk_test_table;
| test_table | CREATE TABLE `test_table` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)
| fk_test_table | CREATE TABLE `fk_test_table` (
`id` int(11) NOT NULL,
`fk_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_test_idx` (`fk_id`),
CONSTRAINT `fk_test` FOREIGN KEY(`fk_id`) REFERENCES `test_table` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
1 row in set (0.01 sec)
mysql> insert into test_table values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test_table;
+----+
| id |
+----+
| 5 |
| 1 |
| 2 |
| 4 |
| 3 |
+----+
5 rows in set (0.00 sec)
# Foreign Key constraint working as expected
mysql> insert into fk_test_table values (7, 3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into fk_test_table values (8, 8);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (Unknown error code)
# Shutdown the Management Server
stryker@stryker-desktop:~$ ndb_mgm -e shutdown
Connected to Management Server at: localhost:1186
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.
# Restart the Management Server and add the Data Nodes
ndb_mgmd -f <path-to-config.ini> --initial --configdir <path-to-conf>
ndbd -c localhost:1186
ndbd -c localhost:1186
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0, Master)
id=4 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1)
[mysqld(API)] 3 node(s)
id=50 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1)
id=51 (not connected, accepting connect from any host)
id=52 (not connected, accepting connect from any host)
# Check the 'Create Table' statements
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show create table test_table; show create table fk_test_table;
| test_table | CREATE TABLE `test_table` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)
| fk_test_table | CREATE TABLE `fk_test_table` (
`id` int(11) NOT NULL,
`fk_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_test_idx` (`fk_id`) # Foreign key constraint disappeared
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)
# Check the Foreign Key Constraint integrity
mysql> select * from test_table; select * from fk_test_table;
+----+
| id |
+----+
| 5 |
| 1 |
| 2 |
| 4 |
| 3 |
+----+
5 rows in set (0.00 sec)
+----+-------+
| id | fk_id |
+----+-------+
| 7 | 3 |
+----+-------+
1 row in set (0.00 sec)
mysql> insert into fk_test_table values (8, 7);
Query OK, 1 row affected (0.01 sec)
# Value '7' inserted even though there is no such value in the parent table 'test_table'
mysql> select * from fk_test_table;
+----+-------+
| id | fk_id |
+----+-------+
| 8 | 7 |
| 7 | 3 |
+----+-------+
2 rows in set (0.00 sec)
Description: When linking 2 tables with one another using the newly included foreign key feature for the 'ndb' engine, the insert, update and delete operations perform as expected. But after restarting the management server (ndb_mgmd), the foreign key constraint disappears from the table definition, and all the DML queries are processed without the foreign key constraint. How to repeat: # Start the Management Server ndb_mgmd -f <path-to-config.ini> --initial --configdir <path-to-conf> # Start the Data Nodes ndbd -c localhost:1186 ndbd -c localhost:1186 # Check Status stryker@stryker-desktop:~$ ndb_mgm -e show Connected to Management Server at: localhost:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=3 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0, Master) id=4 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1) [mysqld(API)] 3 node(s) id=50 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1) id=51 (not connected, accepting connect from any host) id=52 (not connected, accepting connect from any host) # Create tables using the following script : CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 ; USE `test` ; -- ----------------------------------------------------- -- Table `test`.`test_table` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `test`.`test_table` ( `id` INT NOT NULL , PRIMARY KEY (`id`) ) ENGINE = ndbcluster; -- ----------------------------------------------------- -- Table `test`.`fk_test_table` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `test`.`fk_test_table` ( `id` INT NOT NULL , `fk_id` INT NOT NULL , PRIMARY KEY (`id`) , INDEX `fk_test_idx` (`fk_id` ASC) , CONSTRAINT `fk_test` FOREIGN KEY (`fk_id` ) REFERENCES `test`.`test_table` (`id` ) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = ndbcluster; # Check the validity of the Foreign Key Constraint mysql> show create table test_table; show create table fk_test_table; | test_table | CREATE TABLE `test_table` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | 1 row in set (0.00 sec) | fk_test_table | CREATE TABLE `fk_test_table` ( `id` int(11) NOT NULL, `fk_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `fk_test_idx` (`fk_id`), CONSTRAINT `fk_test` FOREIGN KEY(`fk_id`) REFERENCES `test_table` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | 1 row in set (0.01 sec) mysql> insert into test_table values (1), (2), (3), (4), (5); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from test_table; +----+ | id | +----+ | 5 | | 1 | | 2 | | 4 | | 3 | +----+ 5 rows in set (0.00 sec) # Foreign Key constraint working as expected mysql> insert into fk_test_table values (7, 3); Query OK, 1 row affected (0.00 sec) mysql> insert into fk_test_table values (8, 8); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (Unknown error code) # Shutdown the Management Server stryker@stryker-desktop:~$ ndb_mgm -e shutdown Connected to Management Server at: localhost:1186 3 NDB Cluster node(s) have shutdown. Disconnecting to allow management server to shutdown. # Restart the Management Server and add the Data Nodes ndb_mgmd -f <path-to-config.ini> --initial --configdir <path-to-conf> ndbd -c localhost:1186 ndbd -c localhost:1186 ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=3 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0, Master) id=4 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1) [mysqld(API)] 3 node(s) id=50 @127.0.0.1 (mysql-5.6.10 ndb-7.3.1) id=51 (not connected, accepting connect from any host) id=52 (not connected, accepting connect from any host) # Check the 'Create Table' statements mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show create table test_table; show create table fk_test_table; | test_table | CREATE TABLE `test_table` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | 1 row in set (0.00 sec) | fk_test_table | CREATE TABLE `fk_test_table` ( `id` int(11) NOT NULL, `fk_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `fk_test_idx` (`fk_id`) # Foreign key constraint disappeared ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | 1 row in set (0.00 sec) # Check the Foreign Key Constraint integrity mysql> select * from test_table; select * from fk_test_table; +----+ | id | +----+ | 5 | | 1 | | 2 | | 4 | | 3 | +----+ 5 rows in set (0.00 sec) +----+-------+ | id | fk_id | +----+-------+ | 7 | 3 | +----+-------+ 1 row in set (0.00 sec) mysql> insert into fk_test_table values (8, 7); Query OK, 1 row affected (0.01 sec) # Value '7' inserted even though there is no such value in the parent table 'test_table' mysql> select * from fk_test_table; +----+-------+ | id | fk_id | +----+-------+ | 8 | 7 | | 7 | 3 | +----+-------+ 2 rows in set (0.00 sec)