Debian Codegraph @

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

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