Debian Codegraph @

mysql-test/suite/innodb_zip/t/undo_log_tmp_table.test

raw
1 --source include/have_innodb_max_16k.inc
2 --source include/big_test.inc
3
4 # Avoid CrashReporter popup on Mac
5 --source include/not_crashrep.inc
6 # run for page size >= 8k
7 --disable_warnings
8 --source include/have_innodb_min_8k.inc
9 --enable_warnings
10
11 ####################################################################
12 # TC to test temp-table undolog changes correctness #
13 # Sceanrio covered in single testcase : #
14 # - Tables with row format(redundant,compressed,dynamic,compact #
15 # - Table with primary,composite,prefix,secondary INDEX #
16 # - Insert/delete/update with transactioons #
17 # - Transaction with COMMIT,rollback,savepoint statements #
18 # - Transaction having temporary table and normal table #
19 # - Concurrency by execution of two clients creating tables with #
20 # same names #
21 # - Inserting data using #
22 # - Insert into .. , Load data infile..,insert ignore #
23 # - Insert into .. on duplicate update #
24 # - Check basic delete and upadte [ignore] #
25 # - Check constraints like duplicate key,default value #
26 # - Alter ADD COLUMN , ADD PRIMARY KEY #
27 # - Flush Tables, logs command #
28 # - Vary innodb_rollback_segments & innodb_redo_log_capacity #
29 # - Verify rseg message from server log #
30 ####################################################################
31
32 call mtr.ADD_suppression(".*Resizing redo log.*");
33 call mtr.ADD_suppression(".*Starting to delete and rewrite redo log files.*");
34 call mtr.ADD_suppression(".*New redo log files created.*");
35 # Save initial VALUES of server variable
36 --disable_query_log
37 let $innodb_file_per_table_orig=`SELECT @@innodb_file_per_table`;
38 --enable_query_log
39
40 # Create procedure to perform
41 # 1. Create temp table with row types , INDEX , sufficent data types
42 # 2. Perform DML with transaction
43 delimiter |;
44 CREATE PROCEDURE populate_tables(IN id VARCHAR(10))
45 begin
46 declare n int default 20;
47 set global innodb_file_per_table=on;
48 DROP TABLE IF EXISTS t1,t2,t3,t4;
49
50 CREATE TEMPORARY TABLE t1_temp(c1 int NOT NULL,
51 c2 int NOT NULL,
52 c3 char(255) NOT NULL,
53 c4 text(600) NOT NULL,
54 c5 blob(600) NOT NULL,
55 c6 varchar(600) NOT NULL,
56 c7 varchar(600) NOT NULL,
57 c8 datetime,
58 c9 decimal(6,3),
59 PRIMARY KEY (c1),
60 INDEX (c3,c4(50),c5(50)),
61 INDEX (c2))
62 charset latin1 ENGINE=InnoDB ROW_FORMAT=redundant;
63
64 set @s = concat("CREATE TABLE t1",id," ( c1 int NOT NULL, c2 int NOT NULL, c3 char(255) NOT NULL, c4 text(600) NOT NULL, c5 blob(600) NOT NULL, c6 varchar(600) NOT NULL, c7 varchar(600) NOT NULL, c8 datetime, c9 decimal(6,3), PRIMARY KEY (c1), INDEX (c3,c4(50),c5(50)), INDEX (c2)) charset latin1 ENGINE=InnoDB ROW_FORMAT=redundant;");
65 PREPARE createTable FROM @s;
66 EXECUTE createTable;
67 DEALLOCATE PREPARE createTable;
68
69
70 CREATE TEMPORARY TABLE t2_temp(c1 int NOT NULL,
71 c2 int NOT NULL,
72 c3 char(255) NOT NULL,
73 c4 text(600) NOT NULL,
74 c5 blob(600) NOT NULL,
75 c6 varchar(600) NOT NULL,
76 c7 varchar(600) NOT NULL,
77 c8 datetime,
78 c9 decimal(6,3),
79 PRIMARY KEY (c1),
80 INDEX (c3,c4(50),c5(50)),
81 INDEX (c2))
82 charset latin1 ENGINE=InnoDB ROW_FORMAT=compact;
83
84 set @s = concat("CREATE TABLE t2",id," (c1 int NOT NULL, c2 int NOT NULL, c3 char(255) NOT NULL, c4 text(600) NOT NULL, c5 blob(600) NOT NULL, c6 varchar(600) NOT NULL, c7 varchar(600) NOT NULL, c8 datetime, c9 decimal(6,3), PRIMARY KEY (c1), INDEX (c3,c4(50),c5(50)), INDEX (c2)) charset latin1 ENGINE=InnoDB ROW_FORMAT=compact;");
85 PREPARE createTable FROM @s;
86 EXECUTE createTable;
87 DEALLOCATE PREPARE createTable;
88
89 set @s = concat("CREATE TABLE t3",id," (c1 int NOT NULL, c2 int NOT NULL, c3 char(255) NOT NULL, c4 text(600) NOT NULL, c5 blob(600) NOT NULL, c6 varchar(600) NOT NULL, c7 varchar(600) NOT NULL, c8 datetime, c9 decimal(6,3), PRIMARY KEY (c1), INDEX (c3,c4(50),c5(50)), INDEX (c2)) charset latin1 ENGINE=InnoDB ROW_FORMAT=compressed key_block_size=4;");
90 PREPARE createTable FROM @s;
91 EXECUTE createTable;
92 DEALLOCATE PREPARE createTable;
93
94 CREATE TEMPORARY TABLE t4_temp(c1 int NOT NULL,
95 c2 int NOT NULL,
96 c3 char(255) NOT NULL,
97 c4 text(600) NOT NULL,
98 c5 blob(600) NOT NULL,
99 c6 varchar(600) NOT NULL,
100 c7 varchar(600) NOT NULL,
101 c8 datetime,
102 c9 decimal(6,3),
103 PRIMARY KEY (c1),
104 INDEX (c3,c4(50),c5(50)),
105 INDEX (c2))
106 charset latin1 ENGINE=InnoDB ROW_FORMAT=dynamic;
107
108 set @s = concat("CREATE TABLE t4",id," (c1 int NOT NULL, c2 int NOT NULL, c3 char(255) NOT NULL, c4 text(600) NOT NULL, c5 blob(600) NOT NULL, c6 varchar(600) NOT NULL, c7 varchar(600) NOT NULL, c8 datetime, c9 decimal(6,3), PRIMARY KEY (c1), INDEX (c3,c4(50),c5(50)), INDEX (c2)) charset latin1 ENGINE=InnoDB ROW_FORMAT=dynamic;");
109 PREPARE createTable FROM @s;
110 EXECUTE createTable;
111 DEALLOCATE PREPARE createTable;
112
113 while (n > 0) do
114 START TRANSACTION;
115 set @s = concat("INSERT INTO t1",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30), REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70), REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70), NOW(),(100.55+",n,"));");
116 PREPARE insertIntoTable FROM @s;
117 EXECUTE insertIntoTable;
118 DEALLOCATE PREPARE insertIntoTable;
119 INSERT INTO t1_temp VALUES(n,n,REPEAT(concat(' tc3_',n),30),
120 REPEAT(concat(' tc4_',n),70),REPEAT(concat(' tc_',n),70),
121 REPEAT(concat(' tc6_',n),70),REPEAT(concat(' tc7_',n),70),
122 NOW(),(100.55+n));
123
124 set @s = concat("INSERT INTO t2",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30), REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70), REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70), NOW(),(100.55+",n,"));");
125 PREPARE insertIntoTable FROM @s;
126 EXECUTE insertIntoTable;
127 DEALLOCATE PREPARE insertIntoTable;
128
129 INSERT INTO t2_temp VALUES(n,n,REPEAT(concat(' tc3_',n),30),
130 REPEAT(concat(' tc4_',n),70),REPEAT(concat(' tc_',n),70),
131 REPEAT(concat(' tc6_',n),70),REPEAT(concat(' tc7_',n),70),
132 NOW(),(100.55+n));
133
134 savepoint a;
135
136 set @s = concat("INSERT INTO t3",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30), REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70), REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70), NOW(),(100.55+",n,"));");
137 PREPARE insertIntoTable FROM @s;
138 EXECUTE insertIntoTable;
139 DEALLOCATE PREPARE insertIntoTable;
140
141 savepoint b;
142
143 set @s = concat("INSERT INTO t4",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30), REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70), REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70), NOW(),(100.55+",n,"));");
144 PREPARE insertIntoTable FROM @s;
145 EXECUTE insertIntoTable;
146 DEALLOCATE PREPARE insertIntoTable;
147
148 INSERT INTO t4_temp VALUES(n,n,REPEAT(concat(' tc3_',n),30),
149 REPEAT(concat(' tc4_',n),70),REPEAT(concat(' tc_',n),70),
150 REPEAT(concat(' tc6_',n),70),REPEAT(concat(' tc7_',n),70),
151 NOW(),(100.55+n));
152
153
154 if (n > 10) then
155 if (n > 10 and n <=12) then
156 ROLLBACK TO SAVEPOINT a;
157 COMMIT;
158 end if;
159 if (n > 12 and n < 15) then
160 ROLLBACK TO SAVEPOINT b;
161 COMMIT;
162 end if;
163 if (n > 15) then
164 COMMIT;
165 end if;
166
167 else
168 if (n > 5) then
169 START TRANSACTION;
170 DELETE FROM t1_temp WHERE c1 > 10 ;
171 DELETE FROM t2_temp WHERE c1 > 10 ;
172 DELETE FROM t4_temp WHERE c1 > 10 ;
173
174 rollback;
175 START TRANSACTION;
176 update t1_temp set c1 = c1 + 1000 WHERE c1 > 10;
177 update t2_temp set c1 = c1 + 1000 WHERE c1 > 10;
178 update t4_temp set c1 = c1 + 1000 WHERE c1 > 10;
179 rollback;
180 end if;
181 end if;
182
183 if (n < 5) then
184 rollback;
185 end if;
186
187 FLUSH logs;
188 ALTER TABLE t1_temp DROP PRIMARY KEY;
189 ALTER TABLE t1_temp ADD PRIMARY KEY (c1,c3(10),c4(10));
190 ALTER TABLE t2_temp DROP PRIMARY KEY;
191 ALTER TABLE t2_temp ADD PRIMARY KEY (c1,c3(10),c4(10));
192 ALTER TABLE t4_temp DROP PRIMARY KEY;
193 ALTER TABLE t4_temp ADD PRIMARY KEY (c1,c3(10),c4(10));
194 FLUSH tables;
195
196 START TRANSACTION;
197 set @s = concat("INSERT INTO t1",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
198 PREPARE insertIntoTable FROM @s;
199 EXECUTE insertIntoTable;
200 DEALLOCATE PREPARE insertIntoTable;
201 INSERT INTO t1_temp VALUES(n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
202 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
203 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
204 NOW(),(100.55+n+100));
205 set @s = concat("INSERT INTO t2",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
206 PREPARE insertIntoTable FROM @s;
207 EXECUTE insertIntoTable;
208 DEALLOCATE PREPARE insertIntoTable;
209 INSERT INTO t2_temp VALUES(n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
210 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
211 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
212 NOW(),(100.55+n+100));
213 set @s = concat("INSERT INTO t3",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
214 PREPARE insertIntoTable FROM @s;
215 EXECUTE insertIntoTable;
216 DEALLOCATE PREPARE insertIntoTable;
217 set @s = concat("INSERT INTO t4",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
218 PREPARE insertIntoTable FROM @s;
219 EXECUTE insertIntoTable;
220 DEALLOCATE PREPARE insertIntoTable;
221 INSERT INTO t4_temp VALUES(n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
222 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
223 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
224 NOW(),(100.55+n+100));
225
226
227 DELETE FROM t1_temp WHERE c1 between 100 and 110;
228 DELETE FROM t2_temp WHERE c1 between 100 and 110;
229 DELETE FROM t4_temp WHERE c1 between 100 and 110;
230
231 update t1_temp set c1 = c1+1 WHERE c1>110;
232 update t2_temp set c1 = c1+1 WHERE c1>110;
233 update t4_temp set c1 = c1+1 WHERE c1>110;
234
235 savepoint a;
236
237 set @s = concat("INSERT INTO t1",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
238 PREPARE insertIntoTable FROM @s;
239 EXECUTE insertIntoTable;
240 DEALLOCATE PREPARE insertIntoTable;
241 INSERT INTO t1_temp VALUES(300+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
242 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
243 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
244 NOW(),(100.55+n+100));
245 set @s = concat("INSERT INTO t2",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
246 PREPARE insertIntoTable FROM @s;
247 EXECUTE insertIntoTable;
248 DEALLOCATE PREPARE insertIntoTable;
249 INSERT INTO t2_temp VALUES(300+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
250 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
251 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
252 NOW(),(100.55+n+100));
253 set @s = concat("INSERT INTO t3",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
254 PREPARE insertIntoTable FROM @s;
255 EXECUTE insertIntoTable;
256 DEALLOCATE PREPARE insertIntoTable;
257 set @s = concat("INSERT INTO t4",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
258 PREPARE insertIntoTable FROM @s;
259 EXECUTE insertIntoTable;
260 DEALLOCATE PREPARE insertIntoTable;
261 INSERT INTO t4_temp VALUES(300+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
262 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
263 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
264 NOW(),(100.55+n+100));
265 savepoint b;
266
267 set @s = concat("INSERT INTO t1",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
268 PREPARE insertIntoTable FROM @s;
269 EXECUTE insertIntoTable;
270 DEALLOCATE PREPARE insertIntoTable;
271 INSERT INTO t1_temp VALUES(400+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
272 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
273 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
274 NOW(),(100.55+n+100));
275 set @s = concat("INSERT INTO t2",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
276 PREPARE insertIntoTable FROM @s;
277 EXECUTE insertIntoTable;
278 DEALLOCATE PREPARE insertIntoTable;
279 INSERT INTO t2_temp VALUES(400+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
280 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
281 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
282 NOW(),(100.55+n+100));
283 set @s = concat("INSERT INTO t3",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
284 PREPARE insertIntoTable FROM @s;
285 EXECUTE insertIntoTable;
286 DEALLOCATE PREPARE insertIntoTable;
287 set @s = concat("INSERT INTO t4",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
288 PREPARE insertIntoTable FROM @s;
289 EXECUTE insertIntoTable;
290 DEALLOCATE PREPARE insertIntoTable;
291 INSERT INTO t4_temp VALUES(400+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
292 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
293 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
294 NOW(),(100.55+n+100));
295 savepoint c;
296 rollback to b;
297 rollback to a;
298 COMMIT;
299 COMMIT;
300 rollback;
301 set n = n - 1;
302 end while;
303 end|
304 delimiter ;|
305
306 # Create two client for concurrent execution
307 connect (con1,localhost,root,,);
308 connect (con2,localhost,root,,);
309
310 --echo #---client 1 : dml operation ---"
311 connection con1;
312 -- disable_query_log
313 eval set global innodb_file_per_table=$innodb_file_per_table_orig;
314
315 -- enable_query_log
316 -- disable_query_log
317 # call procedure
318 --send call populate_tables('_1');
319 -- enable_query_log
320
321 --echo #---client 2 : dml operation ---"
322 connection con2;
323 -- disable_query_log
324 eval set global innodb_file_per_table=$innodb_file_per_table_orig;
325 -- enable_query_log
326 -- disable_query_log
327 # call procedure
328 --send call populate_tables('_2');
329
330 -- enable_query_log
331
332 # check data of client connection 1
333 --echo # In connection 1
334 connection con1;
335 --reap
336 # 20 rows exepceted in 5 tables
337 SELECT count(*) FROM t1_1;
338 SELECT count(*) FROM t2_1;
339 SELECT count(*) FROM t3_1;
340 SELECT count(*) FROM t4_1;
341 SELECT c1 FROM t1_1;
342 SELECT c1 FROM t2_1;
343 SELECT c1 FROM t3_1;
344 SELECT c1 FROM t4_1;
345 SELECT count(*) FROM t1_temp;
346 SELECT count(*) FROM t2_temp;
347 SELECT count(*) FROM t4_temp;
348 SELECT c1 FROM t1_temp;
349 SELECT c1 FROM t2_temp;
350 SELECT c1 FROM t4_temp;
351 # check data of client connection 2
352 --echo # In connection 2
353 connection con2;
354 --reap
355 # 20 rows exepceted in 5 tables
356 SELECT count(*) FROM t1_2;
357 SELECT count(*) FROM t2_2;
358 SELECT count(*) FROM t3_2;
359 SELECT count(*) FROM t4_2;
360 SELECT c1 FROM t1_2;
361 SELECT c1 FROM t2_2;
362 SELECT c1 FROM t3_2;
363 SELECT c1 FROM t4_2;
364 SELECT count(*) FROM t1_temp;
365 SELECT count(*) FROM t2_temp;
366 SELECT count(*) FROM t4_temp;
367 SELECT c1 FROM t1_temp;
368 SELECT c1 FROM t2_temp;
369 SELECT c1 FROM t4_temp;
370
371 --echo # In connection 1
372 connection con1;
373
374 set AUTOCOMMIT = 0;
375 ALTER TABLE t1_temp DROP PRIMARY KEY;
376 ALTER TABLE t1_temp ADD PRIMARY KEY (c1);
377 ALTER TABLE t2_temp DROP PRIMARY KEY;
378 ALTER TABLE t2_temp ADD PRIMARY KEY (c1);
379 ALTER TABLE t4_temp DROP PRIMARY KEY;
380 ALTER TABLE t4_temp ADD PRIMARY KEY (c1);
381 # Check duplicate key constraint + insert ignore
382 --error ER_DUP_ENTRY
383 INSERT INTO t1_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
384 insert ignore into t1_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
385 --error ER_DUP_ENTRY
386 INSERT INTO t2_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
387 insert ignore into t2_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
388 --error ER_DUP_ENTRY
389 INSERT INTO t4_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
390 insert ignore into t4_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
391
392 # check rollback due to duplicate value in second record of insert
393 --error ER_DUP_ENTRY
394 INSERT INTO t1_temp VALUES (1,1,'a','a','a','a','a',NOW(),100.55),
395 (20,1,'a','a','a','a','a',NOW(),100.55);
396 --error ER_DUP_ENTRY
397 INSERT INTO t2_temp VALUES (1,1,'a','a','a','a','a',NOW(),100.55),
398 (20,1,'a','a','a','a','a',NOW(),100.55);
399 --error ER_DUP_ENTRY
400 INSERT INTO t4_temp VALUES (1,1,'a','a','a','a','a',NOW(),100.55),
401 (20,1,'a','a','a','a','a',NOW(),100.55);
402
403 set AUTOCOMMIT = 1;
404
405 SELECT c1,c2 FROM t1_temp WHERE c1 in (20,1);
406 SELECT c1,c2 FROM t2_temp WHERE c1 in (20,1);
407 SELECT c1,c2 FROM t4_temp WHERE c1 in (20,1);
408
409 #replace statement
410 REPLACE INTO t1_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
411 REPLACE INTO t2_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
412 REPLACE INTO t4_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
413 # verify row is replaced FROM (20,20) to (20,1)
414 SELECT c1,c2,c3,c4,c5,c6,c7,c9 FROM t1_temp WHERE c1 = 20;
415 SELECT c1,c2,c3,c4,c5,c6,c7,c9 FROM t2_temp WHERE c1 = 20;
416 SELECT c1,c2,c3,c4,c5,c6,c7,c9 FROM t4_temp WHERE c1 = 20;
417
418 # Update ignore. statement is gonored as 20 value exits
419 update ignore t1_temp set c1 = 20 WHERE c1 = 140 ;
420 update ignore t2_temp set c1 = 20 WHERE c1 = 140 ;
421 update ignore t4_temp set c1 = 20 WHERE c1 = 140 ;
422 # see record 140 is present as last update ignored
423 SELECT count(*) FROM t1_temp WHERE c1 = 140;
424 SELECT count(*) FROM t2_temp WHERE c1 = 140;
425 SELECT count(*) FROM t4_temp WHERE c1 = 140;
426
427 # Alter table to ADD COLUMN and PRIMARY KEY
428 ALTER TABLE t1_temp ADD COLUMN c10 int default 99 ,
429 ADD COLUMN c11 varchar(100) default 'test';
430 ALTER TABLE t1_temp DROP PRIMARY KEY;
431 ALTER TABLE t1_temp ADD PRIMARY KEY (c1);
432 INSERT INTO t1_temp (c1,c2,c3,c4,c5,c6,c7,c8,c9) VALUES (-1,-1,'a','a','a','a','a',NOW(),100.55);
433 SELECT c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 FROM t1_temp WHERE c1 < 0;
434 SELECT count(*) FROM t1_temp WHERE c10 = 99 and c11 like 'test';
435 # insert on duplicate key update
436 INSERT INTO t1_temp (c1,c2,c3,c4,c5,c6,c7,c8,c9) VALUES (-1,-1,'a','a','a','a','a',NOW(),100.55)
437 ON DUPLICATE KEY UPDATE c1=-2,c2=-2;
438 SELECT c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 FROM t1_temp WHERE c1 < 0;
439
440 #
441
442 #cleanup
443 DROP TABLE t1_1 ,t2_1 ,t3_1,t4_1;
444 disconnect con1;
445
446 connection con2;
447 DROP TABLE t1_2 ,t2_2 ,t3_2,t4_2;
448 disconnect con2;
449
450
451 connection default;
452 #
453 ## trying with VALUES innodb_rollback_segments, innodb_redo_log_capacity
454 ##
455 let $restart_parameters = restart: --innodb_rollback_segments=20 --innodb_redo_log_capacity=20M;
456 --source include/restart_mysqld.inc
457
458 # Create two client for concurrent execution
459 connect (con1,localhost,root,,);
460 connect (con2,localhost,root,,);
461 #
462 #
463 connection con1;
464 --send call populate_tables('_1');
465 connection con2;
466 --send call populate_tables('_2');
467 --echo "#connection 1 - verify tables"
468 connection con1;
469 --reap
470 SELECT count(*) FROM t1_1;
471 SELECT count(*) FROM t2_1;
472 SELECT count(*) FROM t3_1;
473 SELECT count(*) FROM t4_1;
474 SELECT c1 FROM t1_1;
475 SELECT c1 FROM t2_1;
476 SELECT c1 FROM t3_1;
477 SELECT c1 FROM t4_1;
478 SELECT count(*) FROM t1_temp;
479 SELECT count(*) FROM t2_temp;
480 SELECT count(*) FROM t4_temp;
481 SELECT c1 FROM t1_temp;
482 SELECT c1 FROM t2_temp;
483 SELECT c1 FROM t4_temp;
484 DROP TABLE t1_1 ,t2_1 ,t3_1,t4_1;
485 disconnect con1;
486 --echo "#connection 2 - verify tables"
487 connection con2;
488 --reap
489 SELECT count(*) FROM t1_2;
490 SELECT count(*) FROM t2_2;
491 SELECT count(*) FROM t3_2;
492 SELECT count(*) FROM t4_2;
493 SELECT c1 FROM t1_2;
494 SELECT c1 FROM t2_2;
495 SELECT c1 FROM t3_2;
496 SELECT c1 FROM t4_2;
497 SELECT count(*) FROM t1_temp;
498 SELECT count(*) FROM t2_temp;
499 SELECT count(*) FROM t4_temp;
500 SELECT c1 FROM t1_temp;
501 SELECT c1 FROM t2_temp;
502 SELECT c1 FROM t4_temp;
503 DROP TABLE t1_2 ,t2_2 ,t3_2,t4_2;
504 disconnect con2;
505
506 connection default;
507 # innodb_rollback_segments > non redo rsegment
508 let $restart_parameters = restart: --innodb_rollback_segments=30 --innodb_redo_log_capacity=20M;
509 --source include/restart_mysqld.inc
510
511 connect (con1,localhost,root,,);
512 connect (con2,localhost,root,,);
513
514 connection con1;
515 --send call populate_tables('_1');
516 connection con2;
517 --send call populate_tables('_2');
518 --echo "#connection 1 - verify tables"
519 connection con1;
520 --reap
521 SELECT count(*) FROM t1_1;
522 SELECT count(*) FROM t2_1;
523 SELECT count(*) FROM t3_1;
524 SELECT count(*) FROM t4_1;
525 SELECT c1 FROM t1_1;
526 SELECT c1 FROM t2_1;
527 SELECT c1 FROM t3_1;
528 SELECT c1 FROM t4_1;
529 SELECT count(*) FROM t1_temp;
530 SELECT count(*) FROM t2_temp;
531 SELECT count(*) FROM t4_temp;
532 SELECT c1 FROM t1_temp;
533 SELECT c1 FROM t2_temp;
534 SELECT c1 FROM t4_temp;
535 DROP TABLE t1_1 ,t2_1 ,t3_1,t4_1;
536 disconnect con1;
537 --echo "#connection 2 - verify tables"
538 connection con2;
539 --reap
540 SELECT count(*) FROM t1_2;
541 SELECT count(*) FROM t2_2;
542 SELECT count(*) FROM t3_2;
543 SELECT count(*) FROM t4_2;
544 SELECT c1 FROM t1_2;
545 SELECT c1 FROM t2_2;
546 SELECT c1 FROM t3_2;
547 SELECT c1 FROM t4_2;
548 SELECT count(*) FROM t1_temp;
549 SELECT count(*) FROM t2_temp;
550 SELECT count(*) FROM t4_temp;
551 SELECT c1 FROM t1_temp;
552 SELECT c1 FROM t2_temp;
553 SELECT c1 FROM t4_temp;
554 DROP TABLE t1_2 ,t2_2 ,t3_2,t4_2;
555 disconnect con2;
556
557 #
558
559 connection default;
560 DROP PROCEDURE populate_tables;
561
562 SHOW TABLES;
563
564 -- disable_query_log
565 eval set global innodb_file_per_table=$innodb_file_per_table_orig;
566 -- enable_query_log
567
568 --let $restart_parameters= restart:
569 --source include/restart_mysqld_no_echo.inc