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