Debian Codegraph @

mysql-test/suite/innodb/t/temp_table_savepoint.test

raw
1 --source include/no_valgrind_without_big.inc
2
3 ####################################################################
4 # TC to test temp-table DML optimization changes for correctness #
5 # Sceanrio covered in single testcase : #
6 # - Tables with row format(redundant,dynamic,compact) #
7 # - Table with primary,composite,prefix,secondary index #
8 # - Insert/delete/update with transactioons #
9 # - Transaction with commit,rollback,savepoint statements #
10 # - Concurrency by execution of two clients creating tables with #
11 # same names #
12 # - Inserting data using #
13 # - Insert into .. , Load data infile..,insert ignore #
14 # - Insert into .. on duplicate update #
15 # - Check basic delete and update [ignore] #
16 # - Check constraints like duplicate key,default value #
17 # - Alter add column , add primary key #
18 # - with prepare and execute statement #
19 ####################################################################
20
21 # run for page size >= 8k
22 --disable_warnings
23 --source include/have_innodb_min_8k.inc
24 --enable_warnings
25
26 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
27
28 # Create procedure to perform
29 # 1. Create temp table with row types , index , sufficent data types
30 # 2. Perform DML with transaction
31 delimiter |;
32 create procedure populate_tables()
33 begin
34 declare n int default 20;
35 declare inner_loop int default 100;
36
37 create temporary table t1(c1 int not null,
38 c2 int not null,
39 c3 char(255) not null,
40 c4 text(6000) not null,
41 c5 blob(6000) not null,
42 c6 varchar(2000) not null,
43 c7 varchar(2000) not null,
44 c8 datetime,
45 c9 decimal(6,3),
46 primary key (c1),
47 index (c3,c4(50),c5(50)),
48 index (c2))
49 engine=innodb row_format=redundant charset latin1;
50
51 create temporary table t2(c1 int not null,
52 c2 int not null,
53 c3 char(255) not null,
54 c4 text(6000) not null,
55 c5 blob(6000) not null,
56 c6 varchar(2000) not null,
57 c7 varchar(2000) not null,
58 c8 datetime,
59 c9 decimal(6,3),
60 primary key (c1),
61 index (c3,c4(50),c5(50)),
62 index (c2))
63 engine=innodb row_format=compact charset latin1;
64
65 create temporary table t4(c1 int not null,
66 c2 int not null,
67 c3 char(255) not null,
68 c4 text(6000) not null,
69 c5 blob(6000) not null,
70 c6 varchar(2000) not null,
71 c7 varchar(2000) not null,
72 c8 datetime,
73 c9 decimal(6,3),
74 primary key (c1),
75 index (c3,c4(50),c5(50)),
76 index (c2))
77 engine=innodb row_format=dynamic charset latin1;
78
79 create temporary table t5(c1 int not null,
80 c2 int not null,
81 c3 char(255) not null,
82 c4 text(6000) not null,
83 c5 blob(6000) not null,
84 c6 varchar(2000) not null,
85 c7 varchar(2000) not null,
86 c8 datetime,
87 c9 decimal(6,3),
88 primary key (c1),
89 index (c3,c4(50),c5(50)),
90 index (c2))
91 engine=innodb charset latin1;
92
93 create temporary table t6 ( a int ) engine = innodb;
94 insert into t6 values (50),(100),(150),(190);
95
96 while (n > 0) do
97 start transaction;
98 insert into t1 values(n,n,repeat(concat(' tc3_',n),30),
99 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
100 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
101 now(),(100.55+n));
102 insert into t2 values(n,n,repeat(concat(' tc3_',n),30),
103 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
104 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
105 now(),(100.55+n));
106 insert into t4 values(n,n,repeat(concat(' tc3_',n),30),
107 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
108 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
109 now(),(100.55+n));
110 insert into t5 values(n,n,repeat(concat(' tc3_',n),30),
111 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
112 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
113 now(),(100.55+n));
114
115 if (n > 10) then
116 commit;
117 else
118 delete from t1 where c1 > 10 ;
119 delete from t2 where c1 > 10 ;
120 delete from t4 where c1 > 10 ;
121 delete from t5 where c1 > 10 ;
122
123 rollback;
124 start transaction;
125 update t1 set c1 = c1 + 1000 where c1 > 10;
126 update t2 set c1 = c1 + 1000 where c1 > 10;
127 update t4 set c1 = c1 + 1000 where c1 > 10;
128 update t5 set c1 = c1 + 1000 where c1 > 10;
129 rollback;
130 end if;
131
132 start transaction;
133 insert into t1 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
134 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
135 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
136 now(),(100.55+n+inner_loop));
137 insert into t2 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
138 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
139 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
140 now(),(100.55+n+inner_loop));
141 insert into t4 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
142 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
143 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
144 now(),(100.55+n+inner_loop));
145 insert into t5 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
146 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
147 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
148 now(),(100.55+n+inner_loop));
149
150 delete from t1 where c1 between 100 and 110;
151 delete from t2 where c1 between 100 and 110;
152 delete from t4 where c1 between 100 and 110;
153 delete from t5 where c1 between 100 and 110;
154
155 update t1 set c1 = c1+1 where c1>110;
156 update t2 set c1 = c1+1 where c1>110;
157 update t4 set c1 = c1+1 where c1>110;
158 update t5 set c1 = c1+1 where c1>110;
159
160 savepoint a;
161
162 insert into t1 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
163 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
164 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
165 now(),(100.55+n+inner_loop));
166 insert into t2 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
167 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
168 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
169 now(),(100.55+n+inner_loop));
170 insert into t4 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
171 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
172 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
173 now(),(100.55+n+inner_loop));
174 insert into t5 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
175 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
176 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
177 now(),(100.55+n+inner_loop));
178 savepoint b;
179
180 insert into t1 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
181 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
182 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
183 now(),(100.55+n+inner_loop));
184 insert into t2 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
185 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
186 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
187 now(),(100.55+n+inner_loop));
188 insert into t4 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
189 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
190 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
191 now(),(100.55+n+inner_loop));
192 insert into t5 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
193 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
194 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
195 now(),(100.55+n+inner_loop));
196 savepoint c;
197 rollback to b;
198 rollback to a;
199 commit;
200 commit;
201 rollback;
202 set n = n - 1;
203 end while;
204 end|
205 delimiter ;|
206
207 # Create two client for concurrent execution
208 connect (con1,localhost,root,,);
209 connect (con2,localhost,root,,);
210
211 --echo #---client 1 : dml operation ---"
212 connection con1;
213 -- disable_query_log
214 # call procedure
215 --send call populate_tables();
216 -- enable_query_log
217
218 --echo #---client 2 : dml operation ---"
219 connection con2;
220 -- disable_query_log
221 # call procedure
222 --send call populate_tables();
223 -- enable_query_log
224
225 # check data of client connection 1
226 --echo # In connection 1
227 connection con1;
228 --reap
229 # 20 rows expected
230 select count(*) from t1;
231 select count(*) from t2;
232 select count(*) from t4;
233 select count(*) from t5;
234 select c1 from t1;
235 select c1 from t2;
236 select c1 from t4;
237 select c1 from t5;
238 # check data of client connection 2
239 --echo # In connection 2
240 connection con2;
241 --reap
242 # 20 rows expected
243 select count(*) from t1;
244 select count(*) from t2;
245 select count(*) from t4;
246 select count(*) from t5;
247 select c1 from t1;
248 select c1 from t2;
249 select c1 from t4;
250 select c1 from t5;
251
252 --echo # In connection 1
253 connection con1;
254
255 set autocommit = 0;
256 # Check duplicate key constraint + insert ignore
257 --error ER_DUP_ENTRY
258 insert into t1 values (20,1,'a','a','a','a','a',now(),100.55);
259 insert ignore into t1 values (20,1,'a','a','a','a','a',now(),100.55);
260 --error ER_DUP_ENTRY
261 insert into t2 values (20,1,'a','a','a','a','a',now(),100.55);
262 insert ignore into t2 values (20,1,'a','a','a','a','a',now(),100.55);
263 --error ER_DUP_ENTRY
264 insert into t4 values (20,1,'a','a','a','a','a',now(),100.55);
265 insert ignore into t4 values (20,1,'a','a','a','a','a',now(),100.55);
266 --error ER_DUP_ENTRY
267 insert into t5 values (20,1,'a','a','a','a','a',now(),100.55);
268 insert ignore into t5 values (20,1,'a','a','a','a','a',now(),100.55);
269
270 # check rollback due to duplicate value in second record of insert
271 --error ER_DUP_ENTRY
272 insert into t1 values (1,1,'a','a','a','a','a',now(),100.55),
273 (20,1,'a','a','a','a','a',now(),100.55);
274 --error ER_DUP_ENTRY
275 insert into t2 values (1,1,'a','a','a','a','a',now(),100.55),
276 (20,1,'a','a','a','a','a',now(),100.55);
277 --error ER_DUP_ENTRY
278 insert into t4 values (1,1,'a','a','a','a','a',now(),100.55),
279 (20,1,'a','a','a','a','a',now(),100.55);
280 --error ER_DUP_ENTRY
281 insert into t5 values (1,1,'a','a','a','a','a',now(),100.55),
282 (20,1,'a','a','a','a','a',now(),100.55);
283
284 set autocommit = 1;
285
286 select c1,c2 from t1 where c1 in (20,1);
287 select c1,c2 from t2 where c1 in (20,1);
288 select c1,c2 from t4 where c1 in (20,1);
289 select c1,c2 from t5 where c1 in (20,1);
290
291 #replace statement
292 replace into t1 values (20,1,'a','a','a','a','a',now(),100.55);
293 replace into t2 values (20,1,'a','a','a','a','a',now(),100.55);
294 replace into t4 values (20,1,'a','a','a','a','a',now(),100.55);
295 replace into t5 values (20,1,'a','a','a','a','a',now(),100.55);
296 # verify row is replaced from (20,20) to (20,1)
297 select c1,c2,c3,c4,c5,c6,c7,c9 from t1 where c1 = 20;
298 select c1,c2,c3,c4,c5,c6,c7,c9 from t2 where c1 = 20;
299 select c1,c2,c3,c4,c5,c6,c7,c9 from t4 where c1 = 20;
300 select c1,c2,c3,c4,c5,c6,c7,c9 from t5 where c1 = 20;
301
302 # Update ignore. statement is ignored as 20 value exists
303 update ignore t1 set c1 = 20 where c1 = 140 ;
304 update ignore t2 set c1 = 20 where c1 = 140 ;
305 update ignore t4 set c1 = 20 where c1 = 140 ;
306 update ignore t5 set c1 = 20 where c1 = 140 ;
307 # see record 140 is present as last update ignored
308 select count(*) from t1 where c1 = 140;
309 select count(*) from t2 where c1 = 140;
310 select count(*) from t4 where c1 = 140;
311 select count(*) from t5 where c1 = 140;
312
313 # Load data infile
314 --echo "running select * into outfile <file> from t1 ;
315 --disable_query_log
316 eval select * into outfile "$MYSQLTEST_VARDIR/tmp/t1.outfile" from t1;
317 --enable_query_log
318 # Create table as select
319 create temporary table temp_1 engine = innodb as select * from t1 where 1=2;
320 select count(*) from temp_1;
321 --echo "running load data infile <file> into temp_1 ;
322 --disable_query_log
323 eval load data infile '$MYSQLTEST_VARDIR/tmp/t1.outfile' into table temp_1;
324 --enable_query_log
325 select count(*) from temp_1;
326
327 # Alter table to add column and primary key
328 alter table temp_1 add column c10 int default 99 ,
329 add column c11 varchar(100) default 'test';
330 alter table temp_1 add primary key (c1);
331 insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55);
332 select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
333 select count(*) from temp_1 where c10 = 99 and c11 like 'test';
334 # insert on duplicate key update
335 insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55)
336 on duplicate key update c1=-2,c2=-2;
337 select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
338
339 #cleanup
340 drop table t1,t2,t4,t5,t6,temp_1;
341 disconnect con1;
342
343 connection con2;
344 drop table t1 ,t2,t4,t5,t6;
345 disconnect con2;
346
347 connection default;
348 drop procedure populate_tables;
349
350
351 # case 2 - with prepare and execute
352 let $prep_loop= 5;
353 create temporary table prep_1(c1 int not null,
354 c2 int not null,
355 c3 char(255) not null,
356 c4 text(6000) not null,
357 c5 blob(6000) not null,
358 c6 varchar(2000) not null,
359 c7 varchar(2000) not null,
360 c8 datetime,
361 c9 decimal(6,3),
362 index (c3,c4(50),c5(50)),
363 index (c2))
364 engine=innodb;
365 PREPARE stm FROM "insert into prep_1 values(?,?,repeat(concat(' tc3_',?),30),repeat(concat(' tc4_',?),800),repeat(concat(' tc_',?),800),repeat(concat(' tc6_',?),245),repeat(concat(' tc7_',?),245),now(),(100.55+?))";
366 set @var = 5;
367 set @var_static = 5;
368 while ($prep_loop>0)
369 {
370 eval EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
371 eval EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
372 dec $prep_loop;
373 set @var = @var - 1;
374 }
375 select c1,left(c3,15) from prep_1 order by c1 ;
376 select count(*) from prep_1;
377
378 PREPARE stm_1 FROM "UPDATE prep_1 SET c1 = c1 + 1";
379 EXECUTE stm_1;
380 EXECUTE stm_1;
381 select c1,left(c3,15) from prep_1 order by c1 ;
382 select count(*) from prep_1;
383
384 PREPARE stm_2 FROM "DELETE FROM prep_1 ORDER BY c1 LIMIT 1";
385 EXECUTE stm_2;
386 EXECUTE stm_2;
387 select c1,left(c3,15) from prep_1 order by c1 ;
388 select count(*) from prep_1;
389
390 drop prepare stm;
391 drop prepare stm_1;
392 drop prepare stm_2;
393 drop table prep_1;
394
395 --remove_file $MYSQLTEST_VARDIR/tmp/t1.outfile