Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_deferred_cascade.test

raw
1 -- source include/have_ndb.inc
2
3 create table parent (
4 a int primary key auto_increment,
5 b int not null,
6 c int not null,
7 unique(b) using hash,
8 index(c)) engine = ndb;
9
10 create table child (
11 a int primary key auto_increment,
12 b int not null,
13 c int not null,
14 unique(b) using hash,
15 index(c)) engine = ndb;
16
17 create table grandchild (
18 a int primary key auto_increment,
19 b int not null,
20 c int not null,
21 unique(b) using hash,
22 index(c)) engine = ndb;
23
24 #
25 # Test deferred DELETE cascade
26 #
27
28 alter table child add constraint fkname foreign key (a) references parent (a)
29 on delete cascade on update restrict;
30
31 alter table grandchild add constraint fkname_child foreign key (a) references child
32 (a) on delete cascade on update restrict;
33
34 insert into parent values (1,1,1),(2,2,2);
35 insert into child values (1,1,1),(2,2,2);
36 insert into grandchild values (1,1,1),(2,2,2);
37
38 set ndb_deferred_constraints = 0;
39
40 begin;
41 delete from parent where a = 1;
42 select * from child order by 1,2,3;
43 select * from grandchild order by 1,2,3;
44 rollback;
45
46 set ndb_deferred_constraints = 1;
47
48 begin;
49 delete from parent where a = 1;
50 select * from child order by 1,2,3;
51 select * from grandchild order by 1,2,3;
52 commit;
53
54 set ndb_deferred_constraints = 0;
55
56 # the deletes should show now...
57 select * from child order by 1,2,3;
58 select * from grandchild order by 1,2,3;
59
60 drop table grandchild, child, parent;
61
62 create table t1 (
63 a int primary key,
64 b int null,
65 unique(b) using hash) engine = ndb;
66
67 let $i=1024;
68 disable_query_log;
69 while ($i)
70 {
71 eval insert into t1 values ($i, $i - 1);
72 dec $i;
73 }
74 insert into t1 values (0,null);
75 enable_query_log;
76
77 select max(a) into @maxa
78 from t1;
79
80 select min(a) into @mina
81 from t1;
82
83 select count(*) from t1;
84
85 select * from t1 p
86 where not exists (select 1 from t1 c where c.a = p.b);
87
88 alter table t1 add constraint fkname
89 foreign key (b) references t1 (a) on delete cascade on update restrict;
90
91 set ndb_deferred_constraints = 0;
92
93 begin;
94 # either FK or UK can fire...which is timing/config dependent
95 --error 1062, 1452
96 update t1 set b = a + 1;
97 rollback;
98
99 set ndb_deferred_constraints = 1;
100
101 begin;
102 update t1 set b = a + 1;
103 --error 1452
104 commit;
105
106 begin;
107 update t1 set b = a + 1;
108 update t1 set b = null where a = @maxa;
109 commit;
110
111 select count(*) from t1;
112
113 select * from t1 p
114 where not exists (select 1 from t1 c where c.a = p.b);
115
116 set ndb_deferred_constraints = 0;
117
118 begin;
119 delete from t1 where a = @maxa - 1;
120 # only a = 0 should be present
121 select * from t1;
122 rollback;
123
124 set ndb_deferred_constraints = 1;
125
126 begin;
127 delete from t1 where a = @maxa - 5;
128 # all rows still present
129 select count(*) from t1;
130 commit;
131
132 # only 5 rows present
133 select * from t1 order by 1,2;
134
135 # check that accessing them using UK works
136 select * from t1 where b = @maxa;
137 select * from t1 where b = @maxa - 1;
138 select * from t1 where b = @maxa - 2;
139 select * from t1 where b = @maxa - 3;
140
141 drop table t1;