Debian Codegraph @

mysql-test/suite/query_rewrite_plugins/t/rpl_skip_rewrite.test

raw
1 # ==== Purpose ====
2 #
3 # Verify that query_rewriter is enabled/disabled for the replication applier
4 # user according to rewriter_enabled_for_threads_without_privilege_checks
5 # and the SKIP_QUERY_REWRITE privilege.
6 #
7 # ==== Requirements ====
8 #
9 # R1. If a replication channel uses PRIVILEGE_CHECKS_USER=NULL, then replicated
10 # statements should be rewritten according to
11 # @@rewriter_enabled_for_threads_without_privilege_checks
12 #
13 # R2. If a replication channel uses a non-NULL PRIVILEGE_CHECKS_USER, then
14 # replicated statements should be never be rewritten if the user has the
15 # SKIP_QUERY_REWRITE privilege
16 #
17 # ==== Implementation ====
18 #
19 # - Create a table.
20 # - Create a rewrite rule for INSERT statements in this table, on the replica.
21 # - Issue matching INSERT statements on the source, and verify that they were
22 # (or were not) rewritten on the replica, in the following scenarios:
23 # - PRIVILEGE_CHECKS_USER is not NULL and user has SKIP_QUERY_REWRITE
24 # - PRIVILEGE_CHECKS_USER is not NULL and the user does not have
25 # SKIP_QUERY_REWRITE
26 # - PRIVILEGE_CHECKS_USER=NULL and
27 # rewriter_enabled_for_threads_without_privilege_checks=ON
28 # - PRIVILEGE_CHECKS_USER=NULL and
29 # rewriter_enabled_for_threads_without_privilege_checks=OFF
30 #
31 # ==== References =====
32 #
33 # WL#14527: System variable to disable query rewrites for system users
34
35 --source include/not_group_replication_plugin.inc
36 --source include/master-slave.inc
37 --source include/xplugin_wait_for_interfaces.inc
38
39 --echo #
40 --echo # Install plugin and add rewrite rule on replica
41 --echo #
42
43 --source include/sync_slave_sql_with_master.inc
44 --source suite/query_rewrite_plugins/include/have_plugin_rewriter.inc
45 --source suite/query_rewrite_plugins/include/install_rewriter.inc
46
47 SELECT PLUGIN_NAME, PLUGIN_TYPE, PLUGIN_VERSION, PLUGIN_TYPE_VERSION
48 FROM information_schema.plugins
49 WHERE plugin_name LIKE 'Rewriter';
50
51 INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement)
52 VALUES ('INSERT INTO test.t1 (a,b) VALUES (?, ?)',
53 'INSERT INTO test.t1 (b,a) VALUES (?, ?)');
54
55 CALL query_rewrite.flush_rewrite_rules();
56
57 --echo #
58 --echo # Replicated INSERTs not rewritten when PRIVILEGE_CHECKS_USER has
59 --echo # SKIP_QUERY_REWRITE privilege
60 --echo #
61
62 connection master;
63 SET @saved_binlog_format=@@global.binlog_format;
64 SET SESSION binlog_format = 'STATEMENT';
65
66 --source include/sync_slave_sql_with_master.inc
67 --source include/stop_slave.inc
68
69 CREATE USER 'replication_applier'@'localhost';
70 GRANT SYSTEM_VARIABLES_ADMIN, REPLICATION_APPLIER, CREATE, INSERT, DELETE, SKIP_QUERY_REWRITE on *.* to 'replication_applier'@'localhost';
71 FLUSH PRIVILEGES;
72 CHANGE REPLICATION SOURCE TO PRIVILEGE_CHECKS_USER='replication_applier'@'localhost';
73 --source include/start_slave.inc
74
75 connection master;
76
77 CREATE TABLE test.t1 ( a INT, b INT , c INT, PRIMARY KEY(a));
78 INSERT INTO test.t1 (a, b) VALUES (1, 2);
79
80 --source include/sync_slave_sql_with_master.inc
81
82 --echo #
83 --echo # Replicated INSERTs are not rewritten, so rows at replica match source
84 --echo #
85
86 --let $assert_text = Replicated INSERT should not be rewritten
87 --let $assert_cond = COUNT(*) = 1 FROM t1 WHERE a = 1 AND b = 2
88 --source include/assert.inc
89
90 --echo #
91 --echo # Replicated INSERTs rewritten when PRIVILEGE_CHECKS_USER does not have
92 --echo # SKIP_QUERY_REWRITE privilege
93 --echo #
94
95 REVOKE SKIP_QUERY_REWRITE ON *.* FROM 'replication_applier'@'localhost';
96 FLUSH PRIVILEGES;
97
98 connection master;
99
100 DELETE FROM test.t1;
101 INSERT INTO test.t1 (a, b) VALUES (3, 4);
102
103 --source include/sync_slave_sql_with_master.inc
104
105 --echo #
106 --echo # Replicated INSERTs are rewritten, so rows at replica do not match source
107 --echo #
108
109 --let $assert_text = Replicated INSERT should be rewritten
110 --let $assert_cond = COUNT(*) = 1 FROM t1 WHERE a = 4 AND b = 3
111 --source include/assert.inc
112
113 --echo #
114 --echo # Replicated INSERTs not rewritten when PRIVILEGE_CHECKS_USER = NULL and
115 --echo # rewriter_enabled_for_threads_without_privilege_checks = OFF
116 --echo #
117
118 SET GLOBAL rewriter_enabled_for_threads_without_privilege_checks = OFF;
119
120 --source include/stop_slave.inc
121 CHANGE REPLICATION SOURCE TO PRIVILEGE_CHECKS_USER = NULL;
122 --source include/start_slave.inc
123
124 connection master;
125
126 DELETE FROM test.t1;
127 INSERT INTO test.t1 (a, b) VALUES (5, 6);
128
129 --source include/sync_slave_sql_with_master.inc
130
131 --echo #
132 --echo # Replicated INSERTs are not rewritten, so rows at replica match source
133 --echo #
134
135 --let $assert_text = Replicated INSERT should not be rewritten
136 --let $assert_cond = COUNT(*) = 1 FROM t1 WHERE a = 5 AND b = 6
137 --source include/assert.inc
138
139 --echo #
140 --echo # Replicated INSERTs rewritten when PRIVILEGE_CHECKS_USER = NULL and
141 --echo # rewriter_enabled_for_threads_without_privilege_checks = ON
142 --echo #
143
144 SET GLOBAL rewriter_enabled_for_threads_without_privilege_checks = ON;
145
146 connection master;
147 DELETE FROM test.t1;
148 INSERT INTO test.t1 (a, b) VALUES (7, 8);
149
150 --source include/sync_slave_sql_with_master.inc
151
152 --echo #
153 --echo # Replicated INSERTs are rewritten, so rows at replica do not match source
154 --echo #
155
156 --let $assert_text = Replicated INSERT should be rewritten
157 --let $assert_cond = COUNT(*) = 1 FROM t1 WHERE a = 8 AND b = 7
158 --source include/assert.inc
159
160 --echo #
161 --echo # Cleanup and uninstall plugin.
162 --echo #
163
164 DROP USER 'replication_applier'@'localhost';
165 SET GLOBAL rewriter_enabled_for_threads_without_privilege_checks = DEFAULT;
166
167 connection master;
168 DROP TABLE test.t1;
169 SET GLOBAL binlog_format=@saved_binlog_format;
170
171 --source include/sync_slave_sql_with_master.inc
172 SET GLOBAL rewriter_enabled_for_threads_without_privilege_checks = DEFAULT;
173 --source suite/query_rewrite_plugins/include/uninstall_rewriter.inc
174 --source include/rpl_end.inc
175 --source include/disconnect_connections.inc