Debian Codegraph @

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

raw
1 # === Purpose ===
2 #
3 # This test validates that when enabled in a replica, the option
4 # sql_generate_invisible_primary_key
5 # does not affect the tables being created by the replication threads.
6 # If a table is created in the source with no primary key it shall remain with
7 # no primary key in the replica
8 #
9 # To get this behavior on the replica the user must use
10 # CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE;
11 # If a table is created in the source with no primary key it shall have a a
12 # primary key in the replica
13 # ==== Requirements ====
14 #
15 # R1. sql_generate_invisible_primary_key does not affect replication threads
16 # R2. REQUIRE_TABLE_PRIMARY_KEY_CHECK=GENERATE makes replication applier threads
17 # generate primary keys for replicated CREATE TABLE statements that don't
18 # contain one
19 #
20 # === Implementation ====
21 #
22 # 1. In the replica enable, in the session and globally:
23 # - sql_generate_invisible_primary_key
24 # 2. Create a table without a primary key on the source
25 # The table should not have a primary key when replicated
26 # 3. Create a table without a primary key on the replica
27 # The table should have a generated primary key
28 # 4. Enable the creation of GIPKs in the replica by setting
29 # REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE
30 # 5. Create a table without a primary key on the source
31 # The table should have a generated primary key on the replica
32 # This still applies if sql_generate_invisible_primary_key = OFF
33 # 6. Test error scenarios where source changes conflict with the replica schema
34 # Case 1: A column is renamed to have the same name as the replica GIPK
35 # Case 2: A PK is added in the source for a table that as a GIPK in the replica
36 # Case 3: A table is created that already has a my_row_id column
37 # 7. Cleanup
38 #
39 # === References ===
40 #
41 # Bug#34092605: sql_generate_invisible_primary_key makes replica diverge
42 # WL#14639: Row-based replication between tables that differ in the existence of a generated invisible primary key
43 # WL#15419: Make the replica_generate_invisible_primary_key option settable per channel
44 #
45
46 --echo
47 --echo ##############################################################
48 --echo # 1. In the replica enable, in the session and globally:
49 --echo # - sql_generate_invisible_primary_key
50
51 --source include/not_have_privilege_checks_user.inc
52 --let $rpl_skip_start_slave = 1
53 --source include/master-slave.inc
54
55 --source include/rpl_connection_slave.inc
56
57 --let $sysvars_to_save = [ "GLOBAL.sql_generate_invisible_primary_key" ]
58 --source include/save_sysvars.inc
59
60 SET GLOBAL sql_generate_invisible_primary_key = ON;
61 SET SESSION sql_generate_invisible_primary_key = ON;
62
63 --echo
64 --echo ##############################################################
65 --echo # 2. Create a table without a primary key on the source
66 --echo # The table should not have a primary key when replicated
67
68 --source include/rpl_connection_master.inc
69
70 CREATE TABLE t1(a int); # No primary key
71
72 --source include/rpl_connection_slave.inc
73
74 --source include/start_slave.inc
75
76 --source include/rpl_connection_master.inc
77
78 --source include/sync_slave_sql_with_master.inc
79
80 --let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t1';`
81 --let $assert_text= The table only contains a column
82 --let $assert_cond= $column_count = 1
83 --source include/assert.inc
84
85 --let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t1' and column_name='my_row_id';`
86 --let $assert_text= The table does not contain a generated invisilble primary key
87 --let $assert_cond= $pk_count = 0
88 --source include/assert.inc
89
90 --echo
91 --echo ##############################################################
92 --echo # 3. Create a table without a primary key on the replica
93 --echo # The table should have a generated primary key
94
95 CREATE TABLE t2(a int); # No primary key
96
97 SHOW CREATE TABLE t2;
98
99 --let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t2';`
100 --let $assert_text= The table contains 2 columns
101 --let $assert_cond= $column_count = 2
102 --source include/assert.inc
103
104 --let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t2' and column_name='my_row_id';`
105 --let $assert_text= The table contains a generated invisilble primary key
106 --let $assert_cond= $pk_count = 1
107 --source include/assert.inc
108
109
110 --echo
111 --echo ##############################################################
112 --echo # 4. Enable the creation of GIPKs in the replica by setting
113 --echo # REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE
114
115 # Store the default value
116 --let $_pk_key_check_value = `SELECT require_table_primary_key_check FROM performance_schema.replication_applier_configuration WHERE channel_name=""`
117
118 --source include/stop_slave_sql.inc
119
120 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE;
121
122 --source include/start_slave_sql.inc
123
124 --echo
125 --echo ##############################################################
126 --echo # 5. Create a table without a primary key on the source
127 --echo # The table should have a generated primary key on the replica
128 --echo # This still applies if sql_generate_invisible_primary_key = OFF
129
130 --source include/rpl_connection_master.inc
131
132 CREATE TABLE t3(a int); # No primary key
133
134 --source include/sync_slave_sql_with_master.inc
135
136 --let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t3';`
137 --let $assert_text= The table only contains an extra column
138 --let $assert_cond= $column_count = 2
139 --source include/assert.inc
140
141 --let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t3' and column_name='my_row_id';`
142 --let $assert_text= The table contains a generated invisilble primary key
143 --let $assert_cond= $pk_count = 1
144 --source include/assert.inc
145
146 # Disable sql_generate_invisible_primary_key
147
148 SET GLOBAL sql_generate_invisible_primary_key = OFF;
149
150 --source include/restart_slave_sql.inc
151
152 --source include/rpl_connection_master.inc
153
154 CREATE TABLE t4(a int); # No primary key
155
156 --source include/sync_slave_sql_with_master.inc
157
158 --let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t4';`
159 --let $assert_text= The table only contains an extra column
160 --let $assert_cond= $column_count = 2
161 --source include/assert.inc
162
163 --let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t4' and column_name='my_row_id';`
164 --let $assert_text= The table contains a generated invisilble primary key
165 --let $assert_cond= $pk_count = 1
166 --source include/assert.inc
167
168 --echo
169 --echo ##############################################################
170 --echo # 6. Test error scenarios where source changes conflict with the replica schema
171 --echo # Case 1: A column is renamed to have the same name as the replica GIPK
172 --echo # Case 2: A PK is added in the source for a table that as a GIPK in the replica
173
174 # Case 1: A column is renamed to have the same name as the replica GIPK
175
176 --source include/rpl_connection_master.inc
177
178 ALTER TABLE t4 RENAME COLUMN a TO my_row_id;
179
180 --source include/rpl_connection_slave.inc
181
182 --let $slave_sql_errno= convert_error(ER_DUP_FIELDNAME)
183 --source include/wait_for_slave_sql_error.inc
184
185 # Case 2: A PK is added in the source for a table that as a GIPK in the replica
186
187 --source include/rpl_connection_master.inc
188
189 RESET MASTER;
190
191 CREATE TABLE t5(a int); # No primary key
192
193 ALTER TABLE t5 ADD PRIMARY KEY `k` (a);
194
195 --source include/rpl_connection_slave.inc
196
197 --source include/stop_slave.inc
198 RESET MASTER;
199 --source include/rpl_reset_slave.inc
200 START REPLICA;
201
202 --let $slave_sql_errno= convert_error(ER_MULTIPLE_PRI_KEY)
203 --source include/wait_for_slave_sql_error.inc
204
205 # Case 3: A table is created that already has a my_row_id column
206
207 --source include/rpl_connection_master.inc
208
209 RESET MASTER;
210
211 CREATE TABLE t6 (my_row_id TEXT);
212
213 --source include/rpl_connection_slave.inc
214
215 --source include/stop_slave.inc
216 RESET MASTER;
217 --source include/rpl_reset_slave.inc
218 START REPLICA;
219
220 --let $slave_sql_errno= convert_error(ER_GIPK_COLUMN_EXISTS)
221 --source include/wait_for_slave_sql_error.inc
222
223 --echo
224 --echo ##############################################################
225 --echo # 7. Cleanup
226
227 --source include/rpl_connection_master.inc
228
229 RESET MASTER;
230
231 --source include/rpl_connection_slave.inc
232
233 --source include/stop_slave.inc
234
235 RESET MASTER;
236
237 --source include/rpl_reset_slave.inc
238
239 --replace_result $_pk_key_check_value PRIMARY_KEY_CHECK_VALUE
240 --eval CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = $_pk_key_check_value
241
242 --source include/start_slave.inc
243
244 --source include/restore_sysvars.inc
245
246 DROP TABLE t2;
247
248 let $messages =
249 Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Duplicate column name 'my_row_id''
250 Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Multiple primary key defined' on query.
251 Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Failed to generate invisible primary key. Column 'my_row_id' already exists.'
252 Replica SQL for channel .*: ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state.
253 Replica SQL for channel '': Error 'Duplicate column name 'my_row_id'' on query.
254 Replica: Duplicate column name 'my_row_id' Error_code:
255 Replica SQL for channel '': Error 'Multiple primary key defined' on query.
256 Replica: Multiple primary key defined Error_code:
257 Replica SQL for channel '': Error 'Failed to generate invisible primary key. Column 'my_row_id' already exists.'
258 Replica: Failed to generate invisible primary key. Column 'my_row_id' already exists.
259 ;
260 --let $suppress_on_current_connection = 1
261 --source include/suppress_messages.inc
262
263 --source include/rpl_connection_master.inc
264
265 DROP TABLE t1;
266 DROP TABLE t3;
267 DROP TABLE t4;
268 DROP TABLE t5;
269 --source include/disable_binlog.inc
270 DROP TABLE t6;
271 --source include/restore_binlog.inc
272
273 --source include/rpl_end.inc