Debian Codegraph @

mysql-test/suite/innodb_undo/r/truncate_recover_03.result

raw
1 #
2 # WL#6965: Truncate UNDO logs during recovery.
3 # Crash after ddl log, before truncate
4 #
5 # While the purge thread is turned off, perform enough DML to cause
6 # undo tablespace sizes grow beyond the truncation threshold.
7 # Then inject a crash at ib_undo_trunc_before_truncate and check that recovery occurs
8 # and that the truncation is completed if it was started.
9 # Create 2 explicit undo tablespaces.
10 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
11 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
12 # Make the 2 implicit undo tablespaces inactive so they get truncated.
13 SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
14 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
15 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
16 # Make the 2 implicit undo tablespaces active.
17 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
18 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
19 # Get rid of the explcit undo tablespaces.
20 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
21 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
22 DROP UNDO TABLESPACE undo_003;
23 DROP UNDO TABLESPACE undo_004;
24 SET GLOBAL innodb_purge_rseg_truncate_frequency=default;
25 # The two undo tablespaces to truncate at shutdown will be innodb_undo_001 and innodb_undo_002
26 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
27 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
28 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
29 innodb_undo_001 Undo active 0
30 innodb_undo_002 Undo active 0
31 # Turn off purging so that undo logs build up in the undo tablespaces.
32 SET GLOBAL innodb_purge_stop_now=ON;
33 # Perform enough DML action so that undo tablespace sizes grow beyond
34 # the threshold set by --innodb-max-undo-log-size.
35 create table t1(
36 keyc int,
37 c1 char(255),
38 c2 char(255),
39 c3 char(255),
40 c4 char(255),
41 c5 char(255),
42 c6 char(255),
43 primary key(keyc)) engine = innodb;
44 CREATE PROCEDURE populate_t1()
45 BEGIN
46 DECLARE i INT DEFAULT 1;
47 while (i <= 16000) DO
48 insert into t1 values (i, 'a', 'b', 'c', 'd', 'e', 'f' );
49 SET i = i + 1;
50 END WHILE;
51 END |
52 call populate_t1();
53 delete from t1 where keyc < 10000;
54 update t1 set c1 = 'mysql' where keyc > 10000;
55 update t1 set c2 = 'mysql' where keyc > 10000;
56 update t1 set c3 = 'mysql' where keyc > 10000;
57 update t1 set c4 = 'mysql' where keyc > 10000;
58 update t1 set c5 = 'mysql' where keyc > 10000;
59 update t1 set c6 = 'mysql' where keyc > 10000;
60 # Start a new transaction for both tablespaces to get utilized
61 create table t2 (
62 keyc int,
63 c1 char(255),
64 c2 char(255),
65 c3 char(255),
66 c4 char(255),
67 c5 char(255),
68 c6 char(255),
69 primary key(keyc)) engine = innodb;
70 CREATE PROCEDURE populate_t2()
71 BEGIN
72 DECLARE i INT DEFAULT 1;
73 while (i <= 16000) DO
74 insert into t2 values (i, 'a', 'b', 'c', 'd', 'e', 'f' );
75 SET i = i + 1;
76 END WHILE;
77 END |
78 call populate_t2();
79 delete from t2 where keyc < 10000;
80 update t2 set c1 = 'mysql' where keyc > 10000;
81 update t2 set c2 = 'mysql' where keyc > 10000;
82 update t2 set c3 = 'mysql' where keyc > 10000;
83 update t2 set c4 = 'mysql' where keyc > 10000;
84 update t2 set c5 = 'mysql' where keyc > 10000;
85 update t2 set c6 = 'mysql' where keyc > 10000;
86 drop PROCEDURE populate_t1;
87 drop PROCEDURE populate_t2;
88 drop table t1, t2;
89 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
90 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
91 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
92 innodb_undo_001 Undo active 0
93 innodb_undo_002 Undo active 0
94 # Compare the undo tablespace sizes before and after DML growth.
95 Setting crash point = 'ib_undo_trunc_before_truncate'.
96 SET GLOBAL DEBUG = "+d,ib_undo_trunc_before_truncate";
97 # Do a slow shut down so the undo tablespaces can be truncated.
98 SET GLOBAL innodb_max_undo_log_size=20000000;
99 SET GLOBAL innodb_fast_shutdown=0;
100 SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
101 SET GLOBAL innodb_purge_run_now=ON;
102 Recovery from crash point = 'ib_undo_trunc_before_truncate'.
103 Pattern "ib_undo_trunc_before_truncate" found
104 # restart:
105 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
106 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
107 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
108 innodb_undo_001 Undo active 0
109 innodb_undo_002 Undo active 0
110 #
111 # Cleanup
112 #
113 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
114 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
115 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
116 innodb_undo_001 Undo active 0
117 innodb_undo_002 Undo active 0