Debian Codegraph @

mysql-test/suite/group_replication/t/gr_foreign_checks_disabled.test

raw
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