Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # This test checks the behavior of REQUIRE_TABLE_PRIMARY_KEY_CHECK.
4 #
5 # ==== Requirements ====
6 #
7 # TC1: If REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM.
8 # You can't replicate a table create/alter query if the privileged
9 # user does not have `SESSION_VARIABLES_ADMIN`
10 # TC2: If REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM.
11 # You can replicate a table create/alter query if the privileged
12 # user has `SESSION_VARIABLES_ADMIN`
13 # TC3.1: If **REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON**:
14 # Verify that you cannot replicate a table creation or alteration query
15 # that results on a table with no primary key whatever the master value
16 # for `sql_require_primary_key`
17 # TC3.2: If **REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON**:
18 # Verify that you can replicate a table creation or alteration query
19 # that results on a table with a primary key.
20 # TC4.1: If **REQUIRE_TABLE_PRIMARY_KEY_CHECK = OFF**:
21 # Verify that you can replicate a table creation or alteration query
22 # that results on a table with no primary key whatever the master value
23 # for `sql_require_primary_key`
24 # TC4.2: If **REQUIRE_TABLE_PRIMARY_KEY_CHECK = OFF**:
25 # Verify that you can replicate a table creation or alteration query
26 # that results on a table with a primary key.
27 # TC5.1: If **REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE**:
28 # Verify that you can replicate a table creation with no primary key
29 # that results in a table with a primary key on the replica
30 #
31 # ==== Implementation ====
32 #
33 # 0. Setup replica with 2 sources where the channels have a privilege user
34 #
35 # # STREAM
36 #
37 # 1. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM.
38 # Create a table on the source
39 # The replica applier user lacks privileges for CREATE/ALTER queries
40 # The replica applier fails while replicating.
41 # 2. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM.
42 # Give the replica applier user SESSION_VARIABLES_ADMIN privileges
43 # The replica applier can now replicate CREATE/ALTER queries
44 #
45 # # ON
46 #
47 # 3. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON.
48 # Revoke privileges.
49 # Create a table on the source (server 1) with no primary key
50 # The replica applier fails while replicating due to the lack of PK
51 # 4. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON.
52 # Create a table on the source (server 1) with a primary key
53 # The replica applier is working properly
54 #
55 # # OFF
56 #
57 # 5. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = OFF.
58 # Create a table on the source (server 1) with a primary key and without
59 # The replica applier applies both
60 #
61 # # GENERATE
62 #
63 # 6. Test REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE.
64 # Test also that multiple channels with different behaviors can coexist
65 # Set the strategy for the channel from server 1 to GENERATE
66 # Set the strategy for the channel from server 3 to ON
67 # Add a new table without a primary key on server 1 and 3
68 # The channel from server 1 shall had a GIPK while the applier on the channel from server 3 fails
69 #
70 # 7. Cleanup
71 #
72 # ==== References ====
73 #
74 # WL#13239: Enable/disable primary key checks on slaves
75 # WL#15419: Make the replica_generate_invisible_primary_key option settable per channel
76 #
77
78 # Only with RBR can the applier privilege work with minimal privileges
79 --source include/have_binlog_format_row.inc
80
81 --echo
82 --echo # 0. Setup replica with 2 sources where the channels have a privilege user
83
84 --source include/have_binlog_format_row_or_statement.inc
85 --source include/have_slave_repository_type_table.inc
86 --source include/force_restart.inc
87 --let $rpl_topology= 1->2,3->2
88 --let $applier_user = 'u'@'localhost'
89 --let $rpl_privilege_checks_user = *:$applier_user
90 --let $rpl_require_row_format= *:1
91 --let $rpl_skip_grant_privilege_checks_user_roles = 1
92 --let $rpl_skip_start_slave = 1
93 --let $rpl_multi_source= 1
94 --source include/rpl_init.inc
95
96 --let $rpl_connection_name= server_2
97 --source include/rpl_connection.inc
98
99 --eval GRANT REPLICATION_APPLIER,CREATE,DROP ON *.* TO $applier_user
100
101 # This test assumes the replica is working using the default: STREAM
102 --let $_pk_key_check_value = `SELECT require_table_primary_key_check FROM performance_schema.replication_applier_configuration WHERE channel_name="channel_1"`
103 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM FOR CHANNEL 'channel_1';
104
105 --echo
106 --echo # 1. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM.
107 --echo # Create a table on the source (server 1)
108 --echo # The replica applier user lacks privileges for CREATE/ALTER queries
109 --echo # The replica applier fails while replicating.
110
111 --let $rpl_connection_name= server_1
112 --source include/rpl_connection.inc
113
114 --let $sql_require_primary_key = `SELECT @@global.sql_require_primary_key`
115 SET @@global.sql_require_primary_key = 1;
116
117 CREATE TABLE t1 (a INT PRIMARY KEY);
118
119 --let $rpl_connection_name= server_2
120 --source include/rpl_connection.inc
121
122 START REPLICA;
123
124 --let $slave_sql_errno = convert_error(ER_SPECIFIC_ACCESS_DENIED_ERROR)
125 --source include/wait_for_slave_sql_error.inc
126
127 --echo
128 --echo # 2. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM.
129 --echo # Give the replica applier user SESSION_VARIABLES_ADMIN privileges
130 --echo # The replica applier can now replicate CREATE/ALTER queries
131
132 STOP REPLICA;
133 --eval GRANT SESSION_VARIABLES_ADMIN ON *.* TO $applier_user
134 START REPLICA;
135
136 --let $rpl_connection_name= server_1
137 --source include/rpl_connection.inc
138
139 --let $sync_slave_connection= server_2
140 --let $rpl_channel_name= 'channel_1'
141 --source include/sync_slave_sql_with_master.inc
142
143 --echo
144 --echo # 3. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON.
145 --echo # Revoke privileges.
146 --echo # Create a table on the source (server 1) with no primary key
147 --echo # The replica applier fails while replicating due to the lack of PK
148
149 STOP REPLICA;
150
151 --eval REVOKE SESSION_VARIABLES_ADMIN ON *.* FROM $applier_user
152
153 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON FOR CHANNEL 'channel_1';
154
155 --let $rpl_connection_name= server_1
156 --source include/rpl_connection.inc
157
158 CREATE TABLE t2 (a INT);
159
160 --let $rpl_connection_name= server_2
161 --source include/rpl_connection.inc
162
163 START REPLICA;
164
165 --let $slave_sql_errno = convert_error(ER_TABLE_WITHOUT_PK)
166 --source include/wait_for_slave_sql_error.inc
167
168 # Cleanup for step 3
169 # Make the source and replica forget about table t2
170
171 --let $rpl_connection_name= server_1
172 --source include/rpl_connection.inc
173
174 DROP TABLE t2;
175 RESET MASTER;
176
177 --let $rpl_connection_name= server_2
178 --source include/rpl_connection.inc
179
180 STOP REPLICA;
181 RESET REPLICA;
182 RESET MASTER;
183
184 --echo
185 --echo # 4. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON.
186 --echo # Create a table on the source (server 1) with a primary key
187 --echo # The replica applier is working properly
188
189 --let $rpl_connection_name= server_1
190 --source include/rpl_connection.inc
191
192 SET @@global.sql_require_primary_key = 0;
193 CREATE TABLE t3 (a INT PRIMARY KEY);
194
195 --let $rpl_connection_name= server_2
196 --source include/rpl_connection.inc
197
198 START REPLICA;
199
200 --let $rpl_connection_name= server_1
201 --source include/rpl_connection.inc
202
203 --let $sync_slave_connection= server_2
204 --let $rpl_channel_name= 'channel_1'
205 --source include/sync_slave_sql_with_master.inc
206
207 --echo
208 --echo # 5. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = OFF.
209 --echo # Create a table on the source (server 1) with a primary key and without
210 --echo # The replica applier applies both
211
212 STOP REPLICA;
213
214 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = OFF FOR CHANNEL 'channel_1';
215
216 START REPLICA;
217
218 --let $rpl_connection_name= server_1
219 --source include/rpl_connection.inc
220
221 SET @@global.sql_require_primary_key = 1;
222 CREATE TABLE t4 (a INT PRIMARY KEY);
223 SET @@global.sql_require_primary_key = 0;
224 CREATE TABLE t5 (a INT);
225
226 --let $sync_slave_connection= server_2
227 --let $rpl_channel_name= 'channel_1'
228 --source include/sync_slave_sql_with_master.inc
229
230 --let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t5';`
231 --let $assert_text= The table t5 contains only 1 column (no PK)
232 --let $assert_cond= $column_count = 1
233 --source include/assert.inc
234
235 --echo
236 --echo # 6. Test REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE.
237 --echo # Test also that multiple channels with different behaviors can coexist
238 --echo # Set the strategy for the channel from server 1 to GENERATE
239 --echo # Set the strategy for the channel from server 3 to ON
240 --echo # Add a new table without a primary key on server 1 and 3
241 --echo # The channel from server 1 shall had a GIPK while the applier on the channel from server 3 fails
242
243 --let $rpl_connection_name= server_2
244 --source include/rpl_connection.inc
245
246 --let $rpl_channel_name= 'channel_1'
247 --source include/stop_slave.inc
248 --let $rpl_channel_name= 'channel_3'
249 --source include/stop_slave.inc
250
251 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE FOR CHANNEL 'channel_1';
252 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON FOR CHANNEL 'channel_3';
253
254 --let $rpl_channel_name= 'channel_1'
255 --source include/start_slave.inc
256 --let $rpl_channel_name= 'channel_3'
257 --source include/start_slave.inc
258
259 --let $rpl_connection_name= server_1
260 --source include/rpl_connection.inc
261
262 SET @@global.sql_require_primary_key = 0;
263 CREATE TABLE t6 (a INT);
264
265 --let $sync_slave_connection= server_2
266 --let $rpl_channel_name= 'channel_1'
267 --source include/sync_slave_sql_with_master.inc
268
269 --let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t6';`
270 --let $assert_text= The table contains 2 columns
271 --let $assert_cond= $column_count = 2
272 --source include/assert.inc
273
274 --let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t6' and column_name='my_row_id';`
275 --let $assert_text= The table contains a generated invisilble primary key
276 --let $assert_cond= $pk_count = 1
277 --source include/assert.inc
278
279 --let $rpl_connection_name= server_3
280 --source include/rpl_connection.inc
281 CREATE TABLE t7 (a INT);
282
283 --let $rpl_connection_name= server_2
284 --source include/rpl_connection.inc
285
286 --let $rpl_channel_name= 'channel_3'
287 --let $slave_sql_errno = convert_error(ER_TABLE_WITHOUT_PK)
288 --source include/wait_for_slave_sql_error.inc
289
290 --echo
291 --echo # 7. Cleanup
292
293 --let $rpl_connection_name= server_1
294 --source include/rpl_connection.inc
295
296 DROP TABLE t1;
297 DROP TABLE t3;
298 DROP TABLE t4;
299 DROP TABLE t5;
300 DROP TABLE t6;
301
302 --replace_result $sql_require_primary_key SQL_REQUIRE_PRIMARY_KEY
303 --eval SET @@global.sql_require_primary_key = $sql_require_primary_key
304
305 --let $rpl_connection_name= server_3
306 --source include/rpl_connection.inc
307
308 DROP TABLE t7;
309
310 --let $rpl_connection_name= server_2
311 --source include/rpl_connection.inc
312
313 CALL mtr.add_suppression(".*Access denied; you need .at least one of. the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege.*");
314 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state.*");
315 CALL mtr.add_suppression(".*Unable to create or change a table without a primary key*");
316
317 --let $rpl_channel_name= 'channel_1'
318 --source include/stop_slave.inc
319 --let $rpl_channel_name= 'channel_3'
320 --source include/stop_slave.inc
321
322 --replace_result $_pk_key_check_value PRIMARY_KEY_CHECK_VALUE
323 --eval CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = $_pk_key_check_value FOR CHANNEL 'channel_1'
324 --replace_result $_pk_key_check_value PRIMARY_KEY_CHECK_VALUE
325 --eval CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = $_pk_key_check_value FOR CHANNEL 'channel_3'
326
327 --eval GRANT SESSION_VARIABLES_ADMIN ON *.* TO $applier_user
328
329 --let $rpl_channel_name= 'channel_1'
330 --source include/start_slave.inc
331 --let $rpl_channel_name= 'channel_3'
332 --source include/start_slave.inc
333
334 --let $rpl_connection_name= server_1
335 --source include/rpl_connection.inc
336
337 --let $sync_slave_connection= server_2
338 --let $rpl_channel_name= 'channel_1'
339 --source include/sync_slave_sql_with_master.inc
340
341 --let $rpl_connection_name= server_3
342 --source include/rpl_connection.inc
343
344 --let $sync_slave_connection= server_2
345 --let $rpl_channel_name= 'channel_3'
346 --source include/sync_slave_sql_with_master.inc
347
348 --let $rpl_skip_sync= 1
349 --source include/rpl_end.inc