Debian Codegraph @

mysql-test/suite/innodb/r/undo_log_temp_table.result

raw
1 set global innodb_file_per_table = off;
2 use test;
3 create temporary table t1
4 (a int, b char(100), c char(100)) engine = innodb;
5 create procedure populate_t1()
6 begin
7 declare i int default 1;
8 while (i <= 2000) DO
9 insert into t1 values (i, 'a', 'b');
10 set i = i + 1;
11 end while;
12 end|
13 begin;
14 call populate_t1();
15 commit;
16 select count(*) from t1;
17 count(*)
18 2000
19 begin;
20 call populate_t1();
21 rollback;
22 select count(*) from t1;
23 count(*)
24 2000
25 truncate table t1;
26 select count(*) from t1;
27 count(*)
28 0
29 begin;
30 call populate_t1();
31 commit;
32 select count(*) from t1;
33 count(*)
34 2000
35 delete from t1 where a < 500;
36 select count(*) from t1;
37 count(*)
38 1501
39 update t1 set b = 'innodb' where b = 'a';
40 update t1 set c = 'mysql' where c = 'b' and a < 1000;
41 begin;
42 update t1 set c = 'sqldb' where c = 'mysql' and a < 1000;
43 select count(*) from t1 where c = 'sqldb';
44 count(*)
45 500
46 rollback;
47 drop procedure populate_t1;
48 drop table t1;
49 create table t1
50 (a int, b char(100), c char(100)) engine = innodb;
51 create procedure populate_t1()
52 begin
53 declare i int default 1;
54 while (i <= 2000) DO
55 insert into t1 values (i, 'a', 'b');
56 set i = i + 1;
57 end while;
58 end|
59 begin;
60 call populate_t1();
61 commit;
62 select count(*) from t1;
63 count(*)
64 2000
65 begin;
66 call populate_t1();
67 rollback;
68 select count(*) from t1;
69 count(*)
70 2000
71 truncate table t1;
72 select count(*) from t1;
73 count(*)
74 0
75 begin;
76 call populate_t1();
77 commit;
78 select count(*) from t1;
79 count(*)
80 2000
81 delete from t1 where a < 500;
82 select count(*) from t1;
83 count(*)
84 1501
85 update t1 set b = 'innodb' where b = 'a';
86 update t1 set c = 'mysql' where c = 'b' and a < 1000;
87 begin;
88 update t1 set c = 'sqldb' where c = 'mysql' and a < 1000;
89 select count(*) from t1 where c = 'sqldb';
90 count(*)
91 500
92 rollback;
93 drop procedure populate_t1;
94 drop table t1;
95 use test;
96 create temporary table t1
97 (a int, b char(100), c char(100)) engine = innodb;
98 create table t2
99 (a int, b char(100), c char(100)) engine = innodb;
100 create procedure populate_t1_t2()
101 begin
102 declare i int default 1;
103 while (i <= 2000) DO
104 insert into t1 values (i, 'a', 'b');
105 insert into t2 values (i, 'a', 'b');
106 set i = i + 1;
107 end while;
108 end|
109 create procedure populate_t1_t2_small()
110 begin
111 declare i int default 1;
112 while (i <= 200) DO
113 insert into t1 values (i, 'a', 'b');
114 insert into t2 values (i, 'a', 'b');
115 set i = i + 1;
116 end while;
117 end|
118 begin;
119 call populate_t1_t2();
120 commit;
121 select count(*) from t1;
122 count(*)
123 2000
124 select count(*) from t2;
125 count(*)
126 2000
127 begin;
128 delete from t1 where a <= 100;
129 delete from t2 where a > 1900;
130 select count(*) from t1;
131 count(*)
132 1900
133 select count(*) from t2;
134 count(*)
135 1900
136 commit;
137 begin;
138 update t1 set b = 'innodb' where b = 'a' and a <= 1000;
139 update t2 set b = 'myisam' where c = 'b' and a > 1000;
140 commit;
141 delete from t1 where b = 'a';
142 delete from t2 where c = 'myisam';
143 select count(*) from t1;
144 count(*)
145 900
146 select count(*) from t2;
147 count(*)
148 1900
149 insert into t1 values (1000000, 'mysql', 'db');
150 begin;
151 call populate_t1_t2_small();
152 select count(*) from t1;
153 count(*)
154 1101
155 select count(*) from t2;
156 count(*)
157 2100
158 rollback;
159 select count(*) from t1;
160 count(*)
161 901
162 select count(*) from t2;
163 count(*)
164 1900
165 drop table t2;
166 drop table t1;
167 drop procedure populate_t1_t2;
168 drop procedure populate_t1_t2_small;
169 # The use of temporary tables in XA transactions is only permitted when
170 # xa_detach_on_prepare is OFF.
171 SET @saved_xa_detach_on_prepare = @@SESSION.xa_detach_on_prepare;
172 SET SESSION xa_detach_on_prepare = OFF;
173 create temporary table t1 (i int) engine=innodb;
174 create table t2 (i int) engine=innodb;
175 insert into t1 values (1);
176 insert into t2 values (101);
177 select * from t1;
178 i
179 1
180 select * from t2;
181 i
182 101
183 xa start 'tx1';
184 insert into t1 values (2), (3);
185 insert into t2 values (202), (303);
186 select * from t1;
187 i
188 1
189 2
190 3
191 select * from t2;
192 i
193 101
194 202
195 303
196 xa end 'tx1';
197 xa prepare 'tx1';
198 xa commit 'tx1';
199 select * from t1;
200 i
201 1
202 2
203 3
204 select * from t2;
205 i
206 101
207 202
208 303
209 xa start 'tx1';
210 insert into t1 values (2), (3);
211 insert into t2 values (202), (303);
212 select * from t1;
213 i
214 1
215 2
216 3
217 2
218 3
219 select * from t2;
220 i
221 101
222 202
223 303
224 202
225 303
226 xa end 'tx1';
227 xa prepare 'tx1';
228 xa rollback 'tx1';
229 select * from t1;
230 i
231 1
232 2
233 3
234 select * from t2;
235 i
236 101
237 202
238 303
239 xa start 'tx1';
240 insert into t1 values (2), (3);
241 select * from t1;
242 i
243 1
244 2
245 3
246 2
247 3
248 xa end 'tx1';
249 xa prepare 'tx1';
250 xa commit 'tx1';
251 select * from t1;
252 i
253 1
254 2
255 3
256 2
257 3
258 drop table t1;
259 drop table t2;
260 SET SESSION xa_detach_on_prepare = @saved_xa_detach_on_prepare;
261 set global innodb_file_per_table=default;