Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2
3 --disable_warnings
4 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7;
5 drop database if exists mysqltest;
6 --enable_warnings
7
8 #
9 # Transactionc test to show that the NDB
10 # table handler is working properly with
11 # transactions
12 #
13
14 #
15 # Create a normal table with primary key
16 #
17 CREATE TABLE t1 (
18 pk1 INT NOT NULL PRIMARY KEY,
19 attr1 INT NOT NULL
20 ) ENGINE=ndbcluster;
21
22 # insert
23 begin;
24 insert into t1 values(1,1);
25 insert into t1 values(2,2);
26 select count(*) from t1;
27 select * from t1 where pk1 = 1;
28 select t1.attr1 from t1, t1 as t1x where t1.pk1 = t1x.pk1 + 1;
29 rollback;
30
31 select count(*) from t1;
32 select * from t1 where pk1 = 1;
33 select t1.attr1 from t1, t1 as t1x where t1.pk1 = t1x.pk1 + 1;
34
35 begin;
36 insert into t1 values(1,1);
37 insert into t1 values(2,2);
38 commit;
39
40 select count(*) from t1;
41 select * from t1 where pk1 = 1;
42 select t1.attr1 from t1, t1 as t1x where t1.pk1 = t1x.pk1 + 1;
43
44 # update
45 begin;
46 update t1 set attr1 = attr1 * 2;
47 select count(*) from t1;
48 select * from t1 where pk1 = 1;
49 select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2;
50 rollback;
51
52 select count(*) from t1;
53 select * from t1 where pk1 = 1;
54 select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2;
55
56 begin;
57 update t1 set attr1 = attr1 * 2;
58 commit;
59
60 select count(*) from t1;
61 select * from t1 where pk1 = 1;
62 select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2;
63
64 # delete
65 begin;
66 delete from t1 where attr1 = 2;
67 select count(*) from t1;
68 select * from t1 where pk1 = 1;
69 select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2;
70 rollback;
71
72 select count(*) from t1;
73 select * from t1 where pk1 = 1;
74 select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2;
75
76 begin;
77 delete from t1 where attr1 = 2;
78 commit;
79
80 select count(*) from t1;
81 select * from t1 where pk1 = 1;
82 select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2;
83
84 DROP TABLE t1;
85
86 #
87 # Create table without primary key
88 # a hidden primary key column is created by handler
89 #
90 CREATE TABLE t1 (id INT, id2 int) engine=ndbcluster;
91
92 # insert
93 begin;
94 insert into t1 values(1,1);
95 insert into t1 values(2,2);
96 select sum(id) from t1;
97 select * from t1 where id = 1;
98 select t1.id from t1, t1 as t1x where t1.id2 = t1x.id2 + 1;
99 rollback;
100
101 select sum(id) from t1;
102 select * from t1 where id = 1;
103 select t1.id from t1, t1 as t1x where t1.id2 = t1x.id2 + 1;
104
105 begin;
106 insert into t1 values(1,1);
107 insert into t1 values(2,2);
108 commit;
109
110 select sum(id) from t1;
111 select * from t1 where id = 1;
112 select t1.id from t1, t1 as t1x where t1.id2 = t1x.id2 + 1;
113
114 # update
115 begin;
116 update t1 set id = id * 2;
117 select sum(id) from t1;
118 select * from t1 where id = 2;
119 select * from t1, t1 as t1x where t1x.id = t1.id - 2;
120 rollback;
121
122 select sum(id) from t1;
123 select * from t1 where id = 2;
124 select * from t1, t1 as t1x where t1x.id = t1.id - 2;
125
126 begin;
127 update t1 set id = id * 2;
128 commit;
129
130 select sum(id) from t1;
131 select * from t1 where id = 2;
132 select * from t1, t1 as t1x where t1x.id = t1.id - 2;
133
134 # delete
135
136 DROP TABLE t1;
137
138 #
139 # A more extensive test with a lot more records
140 #
141
142 CREATE TABLE t2 (
143 a bigint unsigned NOT NULL PRIMARY KEY,
144 b int unsigned not null,
145 c int unsigned
146 ) engine=ndbcluster;
147
148 CREATE TABLE t3 (
149 a bigint unsigned NOT NULL,
150 b bigint unsigned not null,
151 c bigint unsigned,
152 PRIMARY KEY(a)
153 ) engine=ndbcluster;
154
155 CREATE TABLE t4 (
156 a bigint unsigned NOT NULL,
157 b bigint unsigned not null,
158 c bigint unsigned NOT NULL,
159 d int unsigned,
160 PRIMARY KEY(a, b, c)
161 ) engine=ndbcluster;
162
163
164 #
165 # insert records into tables and rollback
166 #
167 let $1=100;
168 disable_query_log;
169 begin;
170 while ($1)
171 {
172 eval insert into t2 values($1, $1+9, 5);
173 eval insert into t3 values($1, $1+9, 5);
174 eval insert into t4 values($1, $1+9, 5, $1+26000);
175 dec $1;
176 }
177 rollback;
178 enable_query_log;
179
180 select count(*) from t2;
181 select count(*) from t3;
182 select count(*) from t4;
183
184 #
185 # insert records into tables and commit;
186 #
187 let $1=100;
188 disable_query_log;
189 begin;
190 while ($1)
191 {
192 eval insert into t2 values($1, $1+9, 5);
193 eval insert into t3 values($1, $1+9, 5);
194 eval insert into t4 values($1, $1+9, 5, $1+26000);
195 dec $1;
196 }
197 commit;
198 enable_query_log;
199
200 select count(*) from t2;
201 select count(*) from t3;
202 select count(*) from t4;
203
204 #
205 # delete every other record in the tables
206 #
207 let $1=100;
208 disable_query_log;
209 while ($1)
210 {
211 eval delete from t2 where a=$1;
212 eval delete from t3 where a=$1;
213 eval delete from t4 where a=$1 and b=$1+9 and c=5;
214 dec $1;
215 dec $1;
216 }
217 enable_query_log;
218
219 #
220 # update records and rollback
221 #
222 begin;
223 let $1=100;
224 disable_query_log;
225 while ($1)
226 {
227 eval update t2 set c=$1 where a=$1;
228 eval update t3 set c=7 where a=$1 and b=$1+9 and c=5;
229 eval update t4 set d=$1+21987 where a=$1 and b=$1+9 and c=5;
230 dec $1;
231 dec $1;
232 }
233 rollback;
234 enable_query_log;
235
236 #
237 # update records and commit
238 #
239 begin;
240 let $1=100;
241 disable_query_log;
242 while ($1)
243 {
244 eval update t2 set c=$1 where a=$1;
245 eval update t3 set c=7 where a=$1 and b=$1+9 and c=5;
246 eval update t4 set d=$1+21987 where a=$1 and b=$1+9 and c=5;
247 dec $1;
248 dec $1;
249 }
250 rollback;
251 enable_query_log;
252
253 drop table t2;
254 drop table t3;
255 drop table t4;
256
257 #
258 # Test multiple databases in one transaction
259 #
260
261 CREATE TABLE t1 (
262 pk1 INT NOT NULL PRIMARY KEY,
263 attr1 INT NOT NULL
264 ) ENGINE=ndbcluster;
265
266 create database mysqltest;
267 use mysqltest;
268
269 CREATE TABLE t2 (
270 a bigint unsigned NOT NULL PRIMARY KEY,
271 b int unsigned not null,
272 c int unsigned
273 ) engine=ndbcluster;
274
275 begin;
276 insert into test.t1 values(1,1);
277 insert into t2 values(1,1,1);
278 insert into test.t1 values(2,2);
279 insert into t2 values(2,2,2);
280 select count(*) from test.t1;
281 select count(*) from t2;
282 select * from test.t1 where pk1 = 1;
283 select * from t2 where a = 1;
284 select test.t1.attr1
285 from test.t1, test.t1 as t1x where test.t1.pk1 = t1x.pk1 + 1;
286 select t2.a
287 from t2, t2 as t2x where t2.a = t2x.a + 1;
288 select test.t1.pk1, a from test.t1,t2 where b > test.t1.attr1;
289 rollback;
290
291 select count(*) from test.t1;
292 select count(*) from t2;
293
294 drop table test.t1, t2;
295 drop database mysqltest;
296
297 # End of 4.1 tests