Debian Codegraph @

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

raw
1 ### This test checks whether limiting the number of ###
2 ### concurrent immediate trigger operations is executed ###
3 ### correctly and it eases the resource usage like job buffer ###
4
5 -- source include/have_ndb.inc
6
7 ### Test a table with many immediate triggers in breadth
8
9 create table t1(
10 c1 int not null primary key,
11 c2 int not null,
12 c3 int not null,
13 c4 int not null,
14 c5 int not null,
15 c6 int not null,
16 c7 int not null,
17 c8 int not null,
18 c9 int not null,
19 c10 int not null,
20 c11 int not null,
21 c12 int not null,
22 c13 int not null,
23 c14 int not null,
24 c15 int not null,
25 c16 int not null,
26 c17 int not null,
27 c18 int not null,
28 c19 int not null,
29 c20 int not null,
30 c21 int not null,
31 c22 int not null,
32 c23 int not null,
33 c24 int not null,
34 c25 int not null,
35 c26 int not null,
36 c27 int not null,
37 c28 int not null,
38 c29 int not null,
39 c30 int not null,
40 c31 int not null,
41 c32 int not null,
42 unique key uk2(c2),
43 unique key uk3(c3),
44 unique key uk4(c4),
45 unique key uk5(c5),
46 unique key uk6(c6),
47 unique key uk7(c7),
48 unique key uk8(c8),
49 unique key uk9(c9),
50 unique key uk10(c10),
51 unique key uk11(c11),
52 unique key uk12(c12),
53 unique key uk13(c13),
54 unique key uk14(c14),
55 unique key uk15(c15),
56 unique key uk16(c16),
57 unique key uk17(c17),
58 unique key uk18(c18),
59 unique key uk19(c19),
60 unique key uk20(c20),
61 unique key uk21(c21),
62 unique key uk22(c22),
63 unique key uk23(c23),
64 unique key uk24(c24)
65 ) engine=ndbcluster;
66
67 let $max_rows = 3000;
68
69 --disable_query_log
70 let $loops = $max_rows;
71 while ($loops)
72 {
73 eval insert into t1 values
74 ($loops, $loops, $loops, $loops, $loops, $loops, $loops, $loops,
75 $loops, $loops, $loops, $loops, $loops, $loops, $loops, $loops,
76 $loops, $loops, $loops, $loops, $loops, $loops, $loops, $loops,
77 $loops, $loops, $loops, $loops, $loops, $loops, $loops, $loops);
78 dec $loops;
79 }
80 --enable_query_log
81
82 --error 0,ER_GET_TEMPORARY_ERRMSG
83 eval update t1 set c2=$max_rows+c2, c3=$max_rows+c3, c4=$max_rows+c4,
84 c5=$max_rows+c5, c6=$max_rows+c6, c7=$max_rows+c7, c8=$max_rows+c8,
85 c9=$max_rows+c9, c10=$max_rows+c10, c11=$max_rows+c11, c12=$max_rows+c12,
86 c13=$max_rows+c13, c14=$max_rows+c14, c15=$max_rows+c15, c16=$max_rows+c16,
87 c17=$max_rows+c17, c18=$max_rows+c18, c19=$max_rows+c19, c20=$max_rows+c20,
88 c21=$max_rows+c21, c22=$max_rows+c22, c23=$max_rows+c23, c24=$max_rows+c24,
89 c25=$max_rows+c25, c26=$max_rows+c26, c27=$max_rows+c27, c28=$max_rows+c28,
90 c29=$max_rows+c29, c30=$max_rows+c30, c31=$max_rows+c31, c32=$max_rows+c32;
91 show warnings;
92
93 eval update t1 set c2=$max_rows+c2, c3=$max_rows+c3, c4=$max_rows+c4,
94 c5=$max_rows+c5, c6=$max_rows+c6, c7=$max_rows+c7, c8=$max_rows+c8,
95 c9=$max_rows+c9, c10=$max_rows+c10, c11=$max_rows+c11, c12=$max_rows+c12,
96 c13=$max_rows+c13, c14=$max_rows+c14, c15=$max_rows+c15, c16=$max_rows+c16,
97 c17=$max_rows+c17, c18=$max_rows+c18, c19=$max_rows+c19, c20=$max_rows+c20,
98 c21=$max_rows+c21, c22=$max_rows+c22, c23=$max_rows+c23, c24=$max_rows+c24,
99 c25=$max_rows+c25, c26=$max_rows+c26, c27=$max_rows+c27, c28=$max_rows+c28,
100 c29=$max_rows+c29, c30=$max_rows+c30, c31=$max_rows+c31, c32=$max_rows+c32
101 where c1 < 500;
102
103 --error 0,ER_GET_TEMPORARY_ERRMSG
104 delete from t1;
105 delete from t1 where c1 < 100;
106 drop table t1;
107
108
109 ### Test a table with many immediate triggers in breadth and depth
110
111 create table parent(
112 c1 int not null primary key,
113 c2 int not null,
114 c3 int not null,
115 c4 int not null,
116 c5 int not null,
117 unique key uk2(c2),
118 unique key uk3(c3),
119 unique key uk4(c4),
120 unique key uk5(c5)
121 ) engine=ndbcluster;
122
123 create table child(
124 c1 int not null primary key,
125 c2 int not null,
126 c3 int not null,
127 c4 int,
128 c5 int,
129 unique key uk2(c2),
130 unique key uk3(c3),
131 key k4(c4),
132 key k5(c5),
133 foreign key fk2(c2) references parent(c1) on update restrict on delete cascade,
134 foreign key fk3(c3) references parent(c1) on update restrict on delete cascade
135 ) comment='NDB_TABLE=FULLY_REPLICATED=1' engine=ndbcluster;
136
137 create table grandchild(
138 c1 int not null primary key,
139 c2 int,
140 c3 int,
141 c4 int,
142 c5 int not null,
143 unique key uk2(c2),
144 unique key uk3(c3),
145 key k4(c4),
146 key k5(c5),
147 foreign key fk4(c2) references child(c2) on update restrict on delete cascade,
148 foreign key fk5(c3) references child(c3) on update restrict on delete cascade
149 ) engine=ndbcluster;
150
151
152 --disable_query_log
153 let $loops = $max_rows;
154 while ($loops)
155 {
156 eval insert into parent values ($loops, $loops, $loops, $loops, $loops);
157 dec $loops;
158 }
159
160 let $loops = $max_rows;
161 while ($loops)
162 {
163 eval insert into child values ($loops, $loops, $loops, $loops, $loops);
164 dec $loops;
165 }
166
167 let $loops = $max_rows;
168 while ($loops)
169 {
170 eval insert into grandchild values ($loops, $loops, $loops, $loops, $loops);
171 dec $loops;
172 }
173 --enable_query_log
174
175 # Immediate triggers
176 set ndb_deferred_constraints = 0;
177
178 eval update parent set c2=$max_rows+c2, c3=$max_rows+c3,
179 c4=$max_rows+c4, c5=$max_rows+c5
180 where c1 < 1500;
181
182 select * from parent where c1=1;
183 select * from child where c1=1;
184
185 # It is not possible to delete many rows because trigger ops
186 # running in parallel are limited.
187 begin;
188 --error 0,ER_LOCK_WAIT_TIMEOUT,ER_GET_TEMPORARY_ERRMSG
189 eval delete from parent;
190 rollback;
191
192 select count(*) from parent;
193 select count(*) from child;
194 select count(*) from grandchild;
195
196 # Split the table delete into batches
197
198 eval delete from parent where c1<1500;
199 eval delete from parent where c1<3001;
200
201 select count(*) from parent;
202 select count(*) from child;
203 select count(*) from grandchild;
204
205
206 drop table grandchild;
207 drop table child;
208 drop table parent;