Debian Codegraph @

mysql-test/suite/rpl/t/rpl_explicit_undo_tablespaces.test

raw
1 # *************************************************************
2 # wl#9509: Test the functionality of explicit undo tablespaces.
3 # Show that CREATE/ALTER/DROP UNDO TABLESPACE do not replicate.
4 # *************************************************************
5 --source include/master-slave.inc
6
7 --disable_query_log
8 call mtr.add_suppression("Cannot drop undo tablespace \\'undo_003\\' because it is active. Please do: ALTER UNDO TABLESPACE undo_003 SET INACTIVE");
9 call mtr.add_suppression("Cannot create tablespace undo_003 because the directory is not a valid location. The UNDO DATAFILE location must be in a known directory");
10 call mtr.add_suppression("\\[InnoDB\\] Log writer is waiting for checkpointer to to catch up lag: 9433600 bytes.*");
11 --enable_query_log
12
13 --echo [Connection Master]
14 --connection master
15
16 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
17 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
18
19 # Check metadata info on master
20 SELECT name,space_type,state
21 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
22 WHERE space_type = 'Undo' ORDER BY name;
23
24 --source include/sync_slave_sql_with_master.inc
25 --echo [Connection Slave]
26
27 # Check metadata info on slave
28 SELECT name,space_type,state
29 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
30 WHERE space_type = 'Undo' ORDER BY name;
31
32 --echo [Connection Master]
33 --connection master
34
35 # Check currently active undo tablespace, cannot be dropped
36 --error ER_DROP_FILEGROUP_FAILED
37 DROP UNDO TABLESPACE undo_003;
38 SHOW WARNINGS;
39
40 --source include/sync_slave_sql_with_master.inc
41 --echo [Connection Slave]
42
43 # Check currently active undo tablespace, cannot be dropped
44 --error ER_TABLESPACE_MISSING_WITH_NAME
45 DROP UNDO TABLESPACE undo_003;
46 SHOW WARNINGS;
47
48 --echo [Connection Master]
49 --connection master
50
51 # Check implicit undo tablespace inactive
52 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
53 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
54
55 # Wait until implicit undo tablespace become empty
56 let $inactive_undo_space = innodb_undo_001;
57 source include/wait_until_undo_space_is_empty.inc;
58 let $inactive_undo_space = innodb_undo_002;
59 source include/wait_until_undo_space_is_empty.inc;
60
61 # Check metadata info on master
62 SELECT name,space_type,state
63 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
64 WHERE space_type = 'Undo' ORDER BY name;
65
66 --source include/sync_slave_sql_with_master.inc
67 --echo [Connection Slave]
68
69 # Show that the slave has only 2 implicit undo tablespaces.
70 SELECT name,space_type,state
71 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
72 WHERE space_type = 'Undo' ORDER BY name;
73
74 # Show that they cannot be ade inactive because there always needs to be
75 # at least 2 active undo tablespaces so that they can be implicitly
76 # truncated one at a time..
77 --error ER_DISALLOWED_OPERATION
78 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
79 --error ER_DISALLOWED_OPERATION
80 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
81
82 # Check metadata info on slave
83 SELECT name,space_type,state
84 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
85 WHERE space_type = 'Undo' ORDER BY name;
86
87 --echo [Connection Master]
88 --connection master
89
90 # Create tables while implicit undo tablespaces are inactive
91 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
92 CREATE TABLE tab1(c1 int, c2 varchar(10)) TABLESPACE=ts1;
93 INSERT INTO tab1 VALUES(1, 'VISH');
94 CREATE TABLE tab2(c1 int , c2 varchar(10)) Engine=InnoDB;
95 INSERT INTO tab2 VALUES(2, 'VISH');
96 CREATE INDEX ix1 ON tab1(c2) USING BTREE;
97 CREATE INDEX ix2 ON tab2(c2) ;
98
99 --source include/sync_slave_sql_with_master.inc
100 --echo [Connection Slave]
101
102 # Check the tables on slave
103 SHOW CREATE TABLE tab1;
104 SHOW CREATE TABLE tab2;
105
106 SELECT * FROM tab1;
107 SELECT * FROM tab2;
108
109 --echo [Connection Master]
110 --connection master
111
112 # Check that there are always 2 undo tablespaces in the active state.
113 --error ER_DISALLOWED_OPERATION
114 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
115 SHOW WARNINGS;
116 --error ER_DISALLOWED_OPERATION
117 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
118 SHOW WARNINGS;
119
120 # Check that implicit undo tablespaces can not be dropped
121 --error ER_WRONG_TABLESPACE_NAME
122 DROP UNDO TABLESPACE innodb_undo_001;
123
124 # Check metadata info on master
125 SELECT name,space_type,state
126 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
127 WHERE space_type = 'Undo' ORDER BY name;
128
129 --source include/sync_slave_sql_with_master.inc
130 --echo [Connection Slave]
131
132 --error ER_TABLESPACE_MISSING_WITH_NAME
133 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
134 SHOW WARNINGS;
135
136 # Check metadata info on slave
137 SELECT name,space_type,state
138 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
139 WHERE space_type = 'Undo' ORDER BY name;
140
141 # Check implicit undo tablespaces can not be dropped
142 --error ER_WRONG_TABLESPACE_NAME
143 DROP UNDO TABLESPACE innodb_undo_001;
144
145 --echo [Connection Master]
146 --connection master
147
148 # Check implicit undo tablespace active
149 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
150 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
151
152 # Check metadata info on master
153 SELECT name,space_type,state
154 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
155 WHERE space_type = 'Undo' ORDER BY name;
156
157 # Clean up
158 DROP TABLE tab1;
159 DROP table tab2;
160 DROP TABLESPACE ts1;
161
162 # Check explicit undo tablespace inactive
163 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
164 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
165
166 # Wait until the implicit undo tablespaces become empty
167 let $inactive_undo_space = undo_003;
168 source include/wait_until_undo_space_is_empty.inc;
169 let $inactive_undo_space = undo_004;
170 source include/wait_until_undo_space_is_empty.inc;
171
172 # Drop one of the two explicit undo tablespaces
173 DROP UNDO TABLESPACE undo_003;
174
175 # Check metadata info on master
176 SELECT name,space_type,state
177 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
178 WHERE space_type = 'Undo';
179
180 # Check relative paths disallow on master
181 --error ER_WRONG_FILE_NAME
182 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE '../undo_003.ibu';
183 --replace_result \\ /
184 SHOW WARNINGS;
185
186 --source include/sync_slave_sql_with_master.inc
187 --echo [Connection Slave]
188
189 # Check metadata info on master
190 SELECT name,space_type,state
191 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
192 WHERE space_type = 'Undo';
193
194 # Check relative paths disallow on slave
195 --error ER_WRONG_FILE_NAME
196 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE '../undo_003.ibu';
197 --replace_result \\ /
198 SHOW WARNINGS;
199
200 --echo [Connection Master]
201 --connection master
202
203 # Drop the second explicit undo tablespace
204 DROP UNDO TABLESPACE undo_004;
205
206 # Check metadata info on master
207 SELECT name,space_type,state
208 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
209 WHERE space_type = 'Undo';
210
211 --source include/sync_slave_sql_with_master.inc
212 --echo [Connection Slave]
213
214 # Check metadata info on slave
215 SELECT name,space_type,state
216 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
217 WHERE space_type = 'Undo';
218
219 --source include/rpl_end.inc