Debian Codegraph @

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

raw
1 ###################################################################
2 #Testing functionality of the WL6658
3 #case1: begin work with INSERT with Triggers
4 #case2: (tab1u) begin transaction with UPDATE
5 #case3: (tab1d) begin transaction with DELETE
6 #case4: (tab1i) Rollback & INSERT
7 #case5: (tab2) partitioned table and procedures
8 #case6: (tab2) SAVEPOINT
9 #case7: (tab7,tab8) pk-fk with ON DELETE CASCADE
10 ###################################################################
11 --source include/no_valgrind_without_big.inc
12 --source include/have_innodb.inc
13 --source include/have_partition.inc
14 --source include/not_embedded.inc
15
16 CREATE TABLE tab1(c1 int,c2 varchar(30), c3 BLOB) ENGINE=InnoDB;
17 CREATE TABLE tab1u LIKE tab1;
18 CREATE TABLE tab1d LIKE tab1;
19 CREATE TABLE tab1i LIKE tab1;
20 CREATE TABLE tab3(c1 int,c2 varchar(30)) ENGINE=InnoDB;
21 CREATE TABLE tab4(c1 int,c2 varchar(30)) ENGINE=InnoDB;
22 CREATE TABLE tab5(c1 int,c2 varchar(30)) ENGINE=InnoDB;
23
24 INSERT INTO tab1u VALUES(1,'Testing the wl6658','Testing the wl6658');
25 INSERT INTO tab1d VALUES(1,'Updated','Updated');
26 INSERT INTO tab4 VALUES(1,'Test for Update');
27 INSERT INTO tab5 VALUES(1,'Test for Delete');
28
29 delimiter |;
30
31 CREATE TRIGGER test_trig BEFORE INSERT ON tab1
32 FOR EACH ROW BEGIN
33 INSERT INTO tab3 VALUES(1,'Inserted From Trigger');
34 UPDATE tab4 SET c2='Updated from Trigger' WHERE c1=1;
35 DELETE FROM tab5;
36 END |
37
38 delimiter ;|
39
40 CREATE TABLE tab2(
41 id INT NOT NULL,
42 store_name VARCHAR(30),
43 parts VARCHAR(30),
44 store_id INT
45 ) ENGINE=InnoDB
46 PARTITION BY LIST(store_id) (
47 PARTITION pNorth VALUES IN (10,20,30),
48 PARTITION pEast VALUES IN (40,50,60),
49 PARTITION pWest VALUES IN (70,80,100)
50 );
51
52 SELECT update_time
53 FROM information_schema.tables WHERE table_name='tab2';
54
55 delimiter |;
56
57 CREATE PROCEDURE proc_wl6658()
58 BEGIN
59 INSERT INTO tab2 VALUES(1,'ORACLE','NUTT',10);
60 INSERT INTO tab2 VALUES(2,'HUAWEI','BOLT',40);
61 COMMIT;
62 END |
63
64 delimiter ;|
65
66 CALL proc_wl6658;
67
68 SELECT * FROM tab2 ORDER BY id,store_id;
69
70 SELECT COUNT(update_time)
71 FROM information_schema.tables WHERE table_name='tab2';
72
73 TRUNCATE TABLE tab2;
74
75 SELECT COUNT(update_time)
76 FROM information_schema.tables WHERE table_name='tab2';
77
78 CREATE TABLE tab7(c1 INT NOT NULL, PRIMARY KEY (c1)) ENGINE=INNODB;
79
80 CREATE TABLE tab8(c1 INT PRIMARY KEY,c2 INT,
81 FOREIGN KEY (c2) REFERENCES tab7(c1) ON DELETE CASCADE )
82 ENGINE=INNODB;
83
84 SELECT table_name,update_time
85 FROM information_schema.tables WHERE table_name IN ('tab7','tab8')
86 GROUP BY table_name ORDER BY table_name;
87
88 INSERT INTO tab7 VALUES(1);
89
90 INSERT INTO tab8 VALUES(1,1);
91
92 SELECT table_name,COUNT(update_time)
93 FROM information_schema.tables WHERE table_name IN ('tab7','tab8')
94 GROUP BY table_name ORDER BY table_name;
95
96 --echo #restart the server
97 --source include/restart_mysqld.inc
98
99 SELECT table_name,update_time
100 FROM information_schema.tables
101 WHERE table_name IN ('tab1','tab2','tab3','tab4','tab5','tab7','tab8')
102 ORDER BY table_name;
103
104 --echo #case1:
105
106 BEGIN WORK;
107
108 INSERT INTO tab1
109 VALUES(1,'Testing the wl6658', 'Testing the wl6658');
110
111 SELECT update_time
112 FROM information_schema.tables WHERE table_name='tab1';
113
114 COMMIT;
115
116 SELECT * FROM tab1;
117 SELECT * FROM tab3;
118 SELECT * FROM tab4;
119 SELECT * FROM tab5;
120
121 SELECT table_name,COUNT(update_time)
122 FROM information_schema.tables
123 WHERE table_name IN ('tab1','tab3','tab4','tab5')
124 GROUP BY table_name ORDER BY table_name;
125
126 --echo Testcase with UPDATE stmt and transaction
127
128 SELECT * FROM tab1u;
129
130 SELECT update_time
131 FROM information_schema.tables WHERE table_name='tab1u';
132
133 --echo #case2:
134 START TRANSACTION;
135
136 UPDATE tab1u SET c2='Updated',c3='Updated' WHERE c1=1;
137
138 SELECT update_time
139 FROM information_schema.tables WHERE table_name='tab1u';
140
141 COMMIT;
142
143 SELECT * FROM tab1u;
144
145 SELECT COUNT(update_time)
146 FROM information_schema.tables WHERE table_name='tab1u';
147
148 SELECT * FROM tab1d;
149
150 SELECT update_time
151 FROM information_schema.tables WHERE table_name='tab1d';
152
153 --echo #case3:
154 START TRANSACTION;
155
156 DELETE FROM tab1d;
157
158 SELECT update_time
159 FROM information_schema.tables WHERE table_name='tab1d';
160
161 COMMIT;
162
163 SELECT * FROM tab1d;
164
165 SELECT COUNT(update_time)
166 FROM information_schema.tables WHERE table_name='tab1d';
167
168 SELECT * FROM tab1i;
169
170 SELECT update_time
171 FROM information_schema.tables WHERE table_name='tab1i';
172
173 --echo #case4:
174 START TRANSACTION;
175
176 INSERT INTO tab1i
177 VALUES(1,'Testing the wl6658', 'Testing the wl6658');
178
179 SELECT update_time
180 FROM information_schema.tables WHERE table_name='tab1i';
181
182 ROLLBACK;
183
184 SELECT * FROM tab1i;
185
186 SELECT update_time
187 FROM information_schema.tables WHERE table_name='tab1i';
188
189 BEGIN WORK;
190 DELETE FROM tab1i;
191 SAVEPOINT A;
192 INSERT INTO tab2 VALUES(1,'Oracle','NUTT',10);
193 INSERT INTO tab2 VALUES(2,'HUAWEI','BOLT',40);
194 SAVEPOINT B;
195 INSERT INTO tab2 VALUES(3,'IBM','NAIL',70);
196 SAVEPOINT C;
197 ROLLBACK to A;
198
199 SELECT * FROM tab2;
200
201 SELECT update_time
202 FROM information_schema.tables WHERE table_name='tab2';
203
204 --echo #execute DDL instead of commit
205 create table tab6(c1 int);
206
207 SELECT update_time
208 FROM information_schema.tables WHERE table_name='tab2';
209
210 START TRANSACTION;
211 DELETE FROM tab7;
212 ROLLBACK;
213
214 SELECT * FROM tab7;
215 SELECT * FROM tab8;
216
217 SELECT table_name,update_time
218 FROM information_schema.tables WHERE table_name IN ('tab7','tab8')
219 GROUP BY table_name ORDER BY table_name;
220
221 DELETE FROM tab7;
222 SELECT * FROM tab7;
223 SELECT * FROM tab8;
224
225 SELECT table_name,COUNT(update_time)
226 FROM information_schema.tables WHERE table_name IN ('tab7','tab8')
227 GROUP BY table_name ORDER BY table_name;
228
229 --echo #cleanup
230 DROP TRIGGER test_trig;
231 DROP TABLE tab1,tab1u,tab1d,tab1i,tab2,tab3,tab4,tab5,tab6,tab8,tab7;
232 DROP PROCEDURE proc_wl6658;