| 1 |
################################################################################ |
| 2 |
# Bug#24916351 GR KEY GENERATION FOR FOREIGN KEY SHOULD BE IGNORED IF PARENT IS |
| 3 |
# NOT YET CREATED |
| 4 |
# Problem: When foreign key checks are disabled, user can create a foreign key |
| 5 |
# in child table by referring a parent table that does not exists. |
| 6 |
# Inserting a tuple into such child table is bringing GR server down. |
| 7 |
# Test Steps: |
| 8 |
# 0> Test requires only one server. |
| 9 |
# 1> Disable foreign key checks. |
| 10 |
# 2> Check that we should be able to |
| 11 |
# 2.1> Create a child table with a foreign key which refers a table |
| 12 |
# which does not exist. |
| 13 |
# 2.2> Insert a value in child table even when the parent table |
| 14 |
# does not exist. |
| 15 |
# 3> Create the parent table. |
| 16 |
# 4> Check that we should be able to insert a value in child table even |
| 17 |
# when that value is not there in the parent table. |
| 18 |
# 5> Enable foreign key checks |
| 19 |
# 6> After restoring the foreign key checks check that old behaviour |
| 20 |
# is restored. |
| 21 |
# 6.1> Throwing error if the value is not present in the parent table |
| 22 |
# 6.2> Should able to insert the value if the value is present in the |
| 23 |
# parent table. |
| 24 |
################################################################################ |
| 25 |
--source include/have_group_replication_plugin.inc |
| 26 |
--source include/group_replication.inc |
| 27 |
|
| 28 |
--echo ######################################################################### |
| 29 |
--echo # 1. Disable foreign key checks |
| 30 |
SET foreign_key_checks=0; |
| 31 |
|
| 32 |
--echo ######################################################################### |
| 33 |
--echo # 2.1. Should be able to create a table with a foreign key |
| 34 |
--echo # which refers a table which does not exist. |
| 35 |
CREATE TABLE child_table (c1 INT PRIMARY KEY, |
| 36 |
FOREIGN KEY (c1) REFERENCES parent_table(c1)); |
| 37 |
|
| 38 |
--echo ######################################################################### |
| 39 |
--echo # 2.2. Should be able to insert a value in child table even |
| 40 |
--echo # when the parent table does not exists because foreign |
| 41 |
--echo # key checks are disabled. |
| 42 |
INSERT INTO child_table VALUES(1); |
| 43 |
|
| 44 |
--echo ######################################################################### |
| 45 |
--echo # 3. Create parent table. |
| 46 |
CREATE TABLE parent_table (c1 INT PRIMARY KEY); |
| 47 |
|
| 48 |
--echo ######################################################################### |
| 49 |
--echo # 4. Should be able to insert a value into child table even |
| 50 |
--echo # when that value is not there in the parent table |
| 51 |
--echo # because foreign key checks are disabled. |
| 52 |
INSERT INTO child_table VALUES(2); |
| 53 |
|
| 54 |
--echo ######################################################################### |
| 55 |
--echo # 5. Enable foreign_key_checks |
| 56 |
SET foreign_key_checks=1; |
| 57 |
|
| 58 |
--echo ######################################################################### |
| 59 |
--echo # 6.1. Foreign key checks are enabled. Now inserting a value into |
| 60 |
--echo # child table that does not exists in parent table should |
| 61 |
--echo # throw error. |
| 62 |
--error ER_NO_REFERENCED_ROW_2 |
| 63 |
INSERT INTO child_table VALUES(3); |
| 64 |
|
| 65 |
--echo ######################################################################### |
| 66 |
--echo # 6.2. Insert a tuple into parent_table. Inserting that value |
| 67 |
--echo # into child table should be allowed now. |
| 68 |
INSERT INTO parent_table VALUES(3); |
| 69 |
INSERT INTO child_table VALUES(3); |
| 70 |
|
| 71 |
--echo ######################################################################### |
| 72 |
--echo # 7. Clean up. |
| 73 |
DROP TABLE child_table; |
| 74 |
DROP TABLE parent_table; |
| 75 |
|
| 76 |
--source include/group_replication_end.inc |