Debian Codegraph @

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

raw
1 ###################################################################
2 #Testing functionality of the WL6658
3 #case1: when autocommit off begin work with INSERT with Triggers
4 #case2: when autocommit off begin transaction with UPDATE
5 #case3: when autocommit off begin transaction with DELETE
6 #case4: when autocommit off Rollback & INSERT
7 #case5: when autocommit off with partition table and procedures
8 #case6: when autocommit off with SAVEPOINTs & DDL
9 #case7: when autocommit off pk-fk with ON DELETE CASCADE
10 ###################################################################
11 --source include/no_valgrind_without_big.inc
12
13 --echo #create base table
14 CREATE TABLE tab1(c1 int,c2 varchar(30), c3 BLOB) ENGINE=InnoDB;
15 CREATE TABLE tab3(c1 int,c2 varchar(30)) ENGINE=InnoDB;
16 CREATE TABLE tab4(c1 int,c2 varchar(30)) ENGINE=InnoDB;
17 CREATE TABLE tab5(c1 int,c2 varchar(30)) ENGINE=InnoDB;
18
19 --echo #insert some base records
20 INSERT INTO tab4 VALUES(1,'Test for Update');
21 INSERT INTO tab5 VALUES(1,'Test for Delete');
22
23 delimiter |;
24
25 --echo #create a trigger
26 CREATE TRIGGER test_trig BEFORE INSERT ON tab1
27 FOR EACH ROW BEGIN
28 INSERT INTO tab3 VALUES(1,'Inserted From Trigger');
29 UPDATE tab4 SET c2='Updated from Trigger' WHERE c1=1;
30 DELETE FROM tab5;
31 END |
32
33 delimiter ;|
34
35 --echo #restart the server
36 --source include/restart_mysqld.inc
37
38 --echo check the update_time Before DML, whether it is NULL
39 SELECT table_name,update_time
40 FROM information_schema.tables
41 WHERE table_name IN ('tab1','tab3','tab4','tab5');
42
43 SET AUTOCOMMIT=OFF;
44
45 --echo #case1:
46
47 BEGIN WORK;
48
49 INSERT INTO tab1
50 VALUES(1,'Testing the wl6658', 'Testing the wl6658');
51
52 --echo check the update_time Before commit, whether it is NULL
53 SELECT table_name,update_time
54 FROM information_schema.tables WHERE table_name='tab1';
55
56 COMMIT;
57
58 --echo #check the record is inserted
59 SELECT * FROM tab1;
60
61 --echo #check the record is inserted
62 SELECT * FROM tab3;
63
64 --echo #check the record is updated
65 SELECT * FROM tab4;
66
67 --echo #check no record exists
68 SELECT * FROM tab5;
69
70 --echo check the update_time After Commit, whether it is not NULL
71 --sorted_result
72 SET information_schema_stats_expiry=0;
73 SELECT table_name,COUNT(update_time)
74 FROM information_schema.tables
75 WHERE table_name IN ('tab1','tab3','tab4','tab5')
76 GROUP BY table_schema, table_name;
77
78 --echo #restart the server
79 --source include/restart_mysqld.inc
80
81 SET information_schema_stats_expiry=0;
82 --echo Testcase with UPDATE stmt and transaction
83
84 --echo #check the record is existing
85 SELECT * FROM tab1;
86
87 --echo check the update_time Before DML, whether it is NULL
88 SELECT table_name,update_time
89 FROM information_schema.tables WHERE table_name='tab1';
90
91 SET AUTOCOMMIT=OFF;
92
93 --echo #case2:
94 START TRANSACTION;
95
96 UPDATE tab1 SET c2='Updated',c3='Updated' WHERE c1=1;
97
98 --echo check the update_time Before commit, whether it is NULL
99 SELECT table_name,update_time
100 FROM information_schema.tables WHERE table_name='tab1';
101
102 COMMIT;
103
104 --echo #check the record is updated
105 SELECT * FROM tab1;
106
107 --echo check the update_time After Commit, whether it is not NULL
108 SELECT table_name,COUNT(update_time)
109 FROM information_schema.tables WHERE table_name='tab1';
110
111 --echo #restart the server
112 --source include/restart_mysqld.inc
113
114 SET information_schema_stats_expiry=0;
115 --echo #check the record is existing
116 SELECT * FROM tab1;
117
118 --echo check the update_time Before DML, whether it is NULL
119 SELECT table_name,update_time
120 FROM information_schema.tables WHERE table_name='tab1';
121
122 SET AUTOCOMMIT=OFF;
123
124 --echo #case3:
125 START TRANSACTION;
126
127 DELETE FROM tab1;
128
129 --echo check the update_time Before commit, whether it is NULL
130 SELECT table_name,update_time
131 FROM information_schema.tables WHERE table_name='tab1';
132
133 COMMIT;
134
135 --echo #check the record is deleted
136 SELECT * FROM tab1;
137
138 --echo check the update_time After Commit, whether it is not NULL
139 SELECT table_name,COUNT(update_time)
140 FROM information_schema.tables WHERE table_name='tab1';
141
142 --echo #restart the server
143 --source include/restart_mysqld.inc
144 SET information_schema_stats_expiry=0;
145
146 --echo #check no records are existing
147 SELECT * FROM tab1;
148
149 --echo check the update_time Before DML, whether it is NULL
150 SELECT table_name,update_time
151 FROM information_schema.tables WHERE table_name='tab1';
152
153 SET AUTOCOMMIT=OFF;
154
155 --echo #case4:
156 START TRANSACTION;
157
158 INSERT INTO tab1
159 VALUES(1,'Testing the wl6658', 'Testing the wl6658');
160
161 --echo check the update_time Before Rollback, whether it is NULL
162 SELECT table_name,update_time
163 FROM information_schema.tables WHERE table_name='tab1';
164
165 ROLLBACK;
166
167 --echo #check no record is inserted.
168 SELECT * FROM tab1;
169
170 --echo check the update_time After Rollback, whether it is NULL
171 SELECT table_name,update_time
172 FROM information_schema.tables WHERE table_name='tab1';
173
174 CREATE TABLE tab2(
175 id INT NOT NULL,
176 store_name VARCHAR(30),
177 parts VARCHAR(30),
178 store_id INT
179 ) ENGINE=InnoDB
180 PARTITION BY LIST(store_id) (
181 PARTITION pNorth VALUES IN (10,20,30),
182 PARTITION pEast VALUES IN (40,50,60),
183 PARTITION pWest VALUES IN (70,80,100)
184 );
185
186 --echo check the update_time Before DML, whether it is NULL
187 SELECT table_name,update_time
188 FROM information_schema.tables WHERE table_name='tab2';
189
190 --echo #case5:
191
192 delimiter |;
193
194 --echo #create proc with DML
195 CREATE PROCEDURE proc_wl6658()
196 BEGIN
197 INSERT INTO tab2 VALUES(1,'ORACLE','NUTT',10);
198 INSERT INTO tab2 VALUES(2,'HUAWEI','BOLT',40);
199 COMMIT;
200 END |
201
202 delimiter ;|
203
204 CALL proc_wl6658;
205
206 --echo #check the records are inserted
207 SELECT * FROM tab2 ORDER BY id,store_id;
208
209 --echo check the update_time After Commit, whether it is not NULL
210 SELECT table_name,COUNT(update_time)
211 FROM information_schema.tables WHERE table_name='tab2';
212
213 --echo #delete all records
214 TRUNCATE TABLE tab2;
215
216 --echo #restart the server
217 --source include/restart_mysqld.inc
218 SET information_schema_stats_expiry=0;
219 --echo #case6:
220
221 SET AUTOCOMMIT=off;
222
223 BEGIN WORK;
224 INSERT INTO tab2 VALUES(1,'Oracle','NUTT',10);
225 SAVEPOINT A;
226 INSERT INTO tab2 VALUES(2,'HUAWEI','BOLT',40);
227 SAVEPOINT B;
228 INSERT INTO tab2 VALUES(3,'IBM','NAIL',70);
229 SAVEPOINT C;
230 ROLLBACK to A;
231
232 --echo #check 1 record is inserted
233 SELECT * FROM tab2;
234
235 --echo check the update_time Before DML, whether it is NULL
236 SELECT table_name,update_time
237 FROM information_schema.tables WHERE table_name='tab2';
238
239 --echo #execute DDL instead of commit
240 create table tab6(c1 int);
241
242 --echo check the update_time After Commit, whether it is not NULL
243 SELECT table_name,COUNT(update_time)
244 FROM information_schema.tables WHERE table_name='tab2';
245
246 --echo #case7:
247 --echo #create some base tables
248
249 --echo set the flag to default
250 SET AUTOCOMMIT=Default;
251
252 CREATE TABLE tab7(c1 INT NOT NULL, PRIMARY KEY (c1)) ENGINE=INNODB;
253
254 CREATE TABLE tab8(c1 INT PRIMARY KEY,c2 INT,
255 FOREIGN KEY (c2) REFERENCES tab7(c1) ON DELETE CASCADE )
256 ENGINE=INNODB;
257
258 --echo check the update_time Before DML, whether it is NULL
259 SELECT table_name,update_time
260 FROM information_schema.tables WHERE table_name='tab7';
261
262 --echo check the update_time Before DML, whether it is NULL
263 SELECT table_name,update_time
264 FROM information_schema.tables WHERE table_name='tab8';
265
266 INSERT INTO tab7 VALUES(1);
267
268 INSERT INTO tab8 VALUES(1,1);
269
270 --echo #check the record is inserted
271 SELECT * FROM tab7;
272
273 --echo #check the record is inserted
274 SELECT * FROM tab8;
275
276 --echo check the update_time After Autocommit, whether it is not NULL
277 SELECT table_name,COUNT(update_time)
278 FROM information_schema.tables WHERE table_name='tab7';
279
280 --echo check the update_time After Autocommit, whether it is not NULL
281 SELECT table_name,COUNT(update_time)
282 FROM information_schema.tables WHERE table_name='tab8';
283
284 --echo #restart the server
285 --source include/restart_mysqld.inc
286 SET information_schema_stats_expiry=0;
287
288 SET AUTOCOMMIT=off;
289
290 START TRANSACTION;
291
292 DELETE FROM tab7;
293
294 ROLLBACK;
295
296 --echo #check record exist
297 SELECT * FROM tab7;
298
299 --echo #check record exist
300 SELECT * FROM tab8;
301
302 --echo check the update_time After Rollback, whether it is NULL
303 SELECT table_name,update_time
304 FROM information_schema.tables WHERE table_name='tab7';
305
306 --echo check the update_time After Rollback, whether it is NULL
307 SELECT table_name,update_time
308 FROM information_schema.tables WHERE table_name='tab8';
309
310 START TRANSACTION;
311
312 DELETE FROM tab7;
313
314 COMMIT;
315
316 --echo #check no record exist
317 SELECT * FROM tab7;
318
319 --echo #check no record exist
320 SELECT * FROM tab8;
321
322 --echo check the update_time After Commit, whether it is not NULL
323 SELECT table_name,COUNT(update_time)
324 FROM information_schema.tables WHERE table_name='tab7';
325
326 --echo check the update_time After Commit, whether it is not NULL
327 SELECT table_name,COUNT(update_time)
328 FROM information_schema.tables WHERE table_name='tab8';
329
330 --echo #cleanup
331 DROP TRIGGER test_trig;
332 DROP TABLE tab1,tab2,tab3,tab4,tab5,tab6,tab8,tab7;
333 DROP PROCEDURE proc_wl6658;