Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/innodb_row_format.inc
3 --source include/test_db_charset_latin1.inc
4
5 ####################################################################
6 # TC to test temp-table DML optimization changes for correctness #
7 # Sceanrio covered in single testcase : #
8 # - Tables with row format(redundant,dynamic,compact) #
9 # - Table with primary,composite,prefix,secondary index #
10 # - Insert/delete/update with transactioons #
11 # - Transaction with commit,rollback,savepoint statements #
12 # - Concurrency by execution of two clients creating tables with #
13 # same names #
14 # - Inserting data using #
15 # - Insert into .. , Load data infile..,insert ignore #
16 # - Insert into .. on duplicate update #
17 # - Check basic delete and update [ignore] #
18 # - Check constraints like duplicate key,default value #
19 # - Alter add column , add primary key #
20 # - with prepare and execute statement #
21 ####################################################################
22
23 # Create procedure to perform
24 # 1. Create temp table with row types , index , sufficent data types
25 # 2. Perform DML with transaction
26 delimiter |;
27 create procedure populate_tables()
28 begin
29 declare n int default 20;
30 declare inner_loop int default 100;
31
32 create temporary table t5(c1 int not null,
33 c2 int not null,
34 c3 char(255) not null,
35 c4 text(6000) not null,
36 c5 blob(6000) not null,
37 c6 varchar(2000) not null,
38 c7 varchar(2000) not null,
39 c8 datetime,
40 c9 decimal(6,3),
41 primary key (c1),
42 index (c3,c4(50),c5(50)),
43 index (c2))
44 engine=innodb;
45
46 create temporary table t6 ( a int ) engine = innodb;
47 insert into t6 values (50),(100),(150),(190);
48
49 while (n > 0) do
50 start transaction;
51 insert ignore into t5 values(n,n,repeat(concat(' tc3_',n),30),
52 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
53 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
54 now(),(100.55+n));
55
56 if (n > 10) then
57 commit;
58 else
59 delete from t5 where c1 > 10 ;
60
61 rollback;
62 start transaction;
63 update t5 set c1 = c1 + 1000 where c1 > 10;
64 rollback;
65 end if;
66
67 start transaction;
68 insert ignore into t5 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
69 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
70 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
71 now(),(100.55+n+inner_loop));
72
73 delete from t5 where c1 between 100 and 110;
74
75 update t5 set c1 = c1+1 where c1>110;
76
77 savepoint a;
78
79 insert ignore into t5 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
80 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
81 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
82 now(),(100.55+n+inner_loop));
83 savepoint b;
84
85 insert into t5 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
86 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
87 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
88 now(),(100.55+n+inner_loop));
89 savepoint c;
90 rollback to b;
91 rollback to a;
92 commit;
93 commit;
94 rollback;
95 set n = n - 1;
96 end while;
97 end|
98 delimiter ;|
99
100 # Create two client for concurrent execution
101 connect (con1,localhost,root,,);
102 --send call populate_tables();
103
104 connect (con2,localhost,root,,);
105 --send call populate_tables();
106
107 connection con1;
108 --reap
109 # 20 rows expected
110 select c1 from t5;
111
112 connection con2;
113 --reap
114 # 20 rows expected
115 select c1 from t5;
116
117 connection con1;
118
119 set autocommit = 0;
120 # Check duplicate key constraint + insert ignore
121 --error ER_DUP_ENTRY
122 insert into t5 values (20,1,'a','a','a','a','a',now(),100.55);
123 insert ignore into t5 values (20,1,'a','a','a','a','a',now(),100.55);
124
125 # check rollback due to duplicate value in second record of insert
126 --error ER_DUP_ENTRY
127 insert into t5 values (1,1,'a','a','a','a','a',now(),100.55),
128 (20,1,'a','a','a','a','a',now(),100.55);
129
130 set autocommit = 1;
131
132 select c1,c2 from t5 where c1 in (20,1);
133
134 #replace statement
135 replace into t5 values (20,1,'a','a','a','a','a',now(),100.55);
136 # verify row is replaced from (20,20) to (20,1)
137 select c1,c2,c3,c4,c5,c6,c7,c9 from t5 where c1 = 20;
138
139 # Update ignore. statement is ignored as 20 value exists
140 update ignore t5 set c1 = 20 where c1 = 140 ;
141 # see record 140 is present as last update ignored
142 select count(*) from t5 where c1 = 140;
143
144 --replace_result $MYSQLTEST_VARDIR VARDIR
145 eval select * into outfile "$MYSQLTEST_VARDIR/tmp/t5.outfile" from t5;
146
147 create temporary table temp_1 engine = innodb as select * from t5 where 1=2;
148 select count(*) from temp_1;
149
150 --replace_result $MYSQLTEST_VARDIR VARDIR
151 eval load data infile '$MYSQLTEST_VARDIR/tmp/t5.outfile' into table temp_1;
152 select count(*) from temp_1;
153 --remove_file $MYSQLTEST_VARDIR/tmp/t5.outfile
154
155 # Alter table to add column and primary key
156 alter table temp_1 add column c10 int default 99 ,
157 add column c11 varchar(100) default 'test';
158 alter table temp_1 add primary key (c1);
159 insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55);
160 select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
161 select count(*) from temp_1 where c10 = 99 and c11 like 'test';
162 # insert on duplicate key update
163 insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55)
164 on duplicate key update c1=-2,c2=-2;
165 select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
166
167 disconnect con1;
168 disconnect con2;
169 connection default;
170 drop procedure populate_tables;
171
172 # case 2 - with prepare and execute
173 let $prep_loop= 5;
174 create temporary table prep_1(c1 int not null,
175 c2 int not null,
176 c3 char(255) not null,
177 c4 text(6000) not null,
178 c5 blob(6000) not null,
179 c6 varchar(2000) not null,
180 c7 varchar(2000) not null,
181 c8 datetime,
182 c9 decimal(6,3),
183 index (c3,c4(50),c5(50)),
184 index (c2))
185 engine=innodb;
186 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+?))";
187 set @var = 5;
188 set @var_static = 5;
189 while ($prep_loop>0)
190 {
191 eval EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
192 eval EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
193 dec $prep_loop;
194 set @var = @var - 1;
195 }
196 select c1,left(c3,15) from prep_1 order by c1 ;
197 select count(*) from prep_1;
198
199 PREPARE stm_1 FROM "UPDATE prep_1 SET c1 = c1 + 1";
200 EXECUTE stm_1;
201 EXECUTE stm_1;
202 select c1,left(c3,15) from prep_1 order by c1 ;
203 select count(*) from prep_1;
204
205 PREPARE stm_2 FROM "DELETE FROM prep_1 ORDER BY c1 LIMIT 1";
206 EXECUTE stm_2;
207 EXECUTE stm_2;
208 select c1,left(c3,15) from prep_1 order by c1 ;
209 select count(*) from prep_1;
210
211 drop prepare stm;
212 drop prepare stm_1;
213 drop prepare stm_2;
214
215 --source include/test_db_charset_restore.inc