Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # The purpose of this test script is to test the behavior of
4 # INSERT/UPDATE/DELETE in a partitioned table with and without
5 # sub_partition for LIST and HASH partition
6 #
7 # ==== Requirements ====
8 #
9 # R1. partition_id written to the binary log when there is partitioning
10 # and no sub-partitioning shall range from 0 to (N-1) for a table with
11 # N partitions.
12 # R2. partition_id written to the binary log when there is subpartitioning
13 # shall range from 0 to (M x N - 1) where M is the number of partition and
14 # N is the number of subpartition.
15 # R3. Statements that modify multiple rows of the same partition/subpartition
16 # shall generate only one event.
17 # R4. Statements that modify multiple rows of different partitions/subpartition
18 # shall generate multiple events, one for every row that moves from one
19 # partition to another.
20 #
21 # ==== Implementation ====
22 #
23 # Create a table with partitions(LIST), and test for (R1), (R3) and (R4)
24 # Create a table with partitions(LIST) and sub_partitions(HASH), and test for (R2)-(R4)
25 # Sync with slave successfully
26 # Drop the tables created
27 #
28 # ==== References ====
29 #
30 # Bug#28712618:INSERT/UPDATE/DELETE WITH MULTIPLE ROWS IN SAME PARTITION CREATE MULTIPLE EVENT
31 # WL#12168:Add Partition Information into the Binary Log
32 #
33 # Also check rpl/rpl_partition_info.test for KEY and RANGE partition.
34
35 --source include/have_debug.inc
36 --source include/have_binlog_format_row.inc
37 --source include/master-slave.inc
38
39 --echo #####1.Verifying the value of partition_id written to the binary log #####
40
41 --let $iteration = 1
42
43 while ($iteration <= 2)
44 {
45 if ($iteration == 1)
46 {
47 CREATE TABLE employees (store_id INT NOT NULL) PARTITION BY LIST (store_id)
48 ( PARTITION pEast VALUES IN (4, 8, 12), PARTITION pWest VALUES IN (3, 6, 9),
49 PARTITION pNorth VALUES IN (5, 10, 15));
50 }
51
52 if ($iteration == 2)
53 {
54 CREATE TABLE employees (store_id INT NOT NULL);
55 ALTER TABLE employees PARTITION BY LIST (store_id) ( PARTITION pEast VALUES IN (4, 8, 12),
56 PARTITION pWest VALUES IN (3, 6, 9),
57 PARTITION pNorth VALUES IN (5, 10, 15));
58 }
59
60 # Check if the partition_id is correctly logged for this event.
61 # It is stored in the third partition so the partition_id should be 2
62 --source include/save_binlog_position.inc
63 INSERT INTO employees VALUES(10);
64
65 --let $expected_partition_id = 2
66 --source suite/rpl/include/rpl_partition_info.inc
67
68 ALTER TABLE employees ADD PARTITION (PARTITION pSouth VALUES IN (7, 14, 28));
69 # Check if the partition_id is correctly logged for this event.
70 # It is stored in the fourth partition so the partition_id should be 3
71
72 --source include/save_binlog_position.inc
73 INSERT INTO employees VALUES(28);
74
75 --let $expected_partition_id = 3
76 --source suite/rpl/include/rpl_partition_info.inc
77
78 --echo #####2.INSERT, UPDATE, DELETE in a partitioned table affecting same and multiple partition #####
79 --echo #####3.Verifying the number of events created when executing statements affecting same and multiple partitions #####
80 --echo -----INSERT-----
81
82 --source include/save_binlog_position.inc
83
84 # Test that only one Write_rows_event is logged
85 --source include/save_binlog_position.inc
86 INSERT INTO employees VALUES(4), (8);
87
88 --let $dont_print_pattern= 1
89 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Write_rows # !Commit
90 --source include/assert_binlog_events.inc
91
92 # Check if the partition_id is correctly logged for this event.
93 --let $expected_partition_id= 0
94 --source suite/rpl/include/rpl_partition_info.inc
95
96 # Test that two Write_rows_events are logged
97 --source include/save_binlog_position.inc
98 INSERT INTO employees VALUES(4), (6);
99
100 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Write_rows # Write_rows # !Commit
101 --source include/assert_binlog_events.inc
102
103 # Check if the partition_id is correctly logged for this event.
104 --let $expected_partition_id= 0;1
105 --source suite/rpl/include/rpl_partition_info.inc
106 --echo -----UPDATE-----
107
108 # Test that one Update_rows_events are logged
109 --source include/save_binlog_position.inc
110 UPDATE employees SET store_id = 9 WHERE store_id = 4 OR store_id = 8 ;
111
112 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Update_rows # !Commit
113 --source include/assert_binlog_events.inc
114
115 # Check if the partition_id is correctly logged for this event.
116 --let $partition_id= 0 1
117 --source suite/rpl/include/rpl_partition_info_update.inc
118
119 # Test that two Update_rows_events are logged
120 --source include/save_binlog_position.inc
121 UPDATE employees SET store_id = 8 WHERE store_id = 6 OR store_id = 10;
122
123 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Update_rows # Update_rows # !Commit
124 --source include/assert_binlog_events.inc
125
126 # Check if the partition_id is correctly logged for this event.
127 --let $partition_id= 1 0;2 0
128 --source suite/rpl/include/rpl_partition_info_update.inc
129
130 --echo -----DELETE-----
131
132 # Test that single Delete_rows_events are logged
133 --source include/save_binlog_position.inc
134 DELETE FROM employees where store_id = 8;
135
136 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Delete_rows # !Commit
137 --source include/assert_binlog_events.inc
138
139 # Check if the partition_id is correctly logged for this event.
140 --let $expected_partition_id= 0
141 --source suite/rpl/include/rpl_partition_info.inc
142
143 #Test that two Delete_rows_events are logged
144 --source include/save_binlog_position.inc
145 DELETE FROM employees where store_id = 9 OR store_id = 28;
146
147 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Delete_rows # Delete_rows # !Commit
148 --source include/assert_binlog_events.inc
149
150 # Check if the partition_id is correctly logged for this event.
151 --let $expected_partition_id= 1;3
152 --source suite/rpl/include/rpl_partition_info.inc
153 --inc $iteration
154 DROP TABLE employees;
155 }
156 --echo #####4.Verifying the value of partition_id written to the binary log in case of sub-partitions######
157
158 CREATE TABLE sp (id INT, age INT) PARTITION BY LIST (age) SUBPARTITION BY HASH (id)
159 SUBPARTITIONS 2 (
160 PARTITION p0 VALUES IN (2, 3, 5, 7, 11),
161 PARTITION p1 VALUES IN (4, 6, 8, 10, 12));
162
163 # Check if the partition_id is correctly logged for this event.
164 --source include/save_binlog_position.inc
165 INSERT INTO sp VALUES(101, 3);
166
167 --let $expected_partition_id = 1
168 --source suite/rpl/include/rpl_partition_info.inc
169
170 INSERT INTO sp VALUES (102, 3);
171 INSERT INTO sp VALUES (107, 4);
172 INSERT INTO sp VALUES (108, 10);
173 --echo #####5.INSERTS, UPDATES, DELETES in a partitioned table affecting same and multiple sub_partition #####
174 --echo #####6.Verifying the number of events created when executing statements affecting single and multiple sub_partitions #####
175
176 --echo -----INSERT-----
177
178 # A single statement writing multiple rows in the same partition
179 # and sub_partition should generate just one row event.
180 --source include/save_binlog_position.inc
181 INSERT INTO sp VALUES(103, 2), (105, 5);
182
183 # Test that only one Write_rows_event is logged
184 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Write_rows # !Commit
185 --source include/assert_binlog_events.inc
186
187 # Check if the partition_id is correctly logged for this event.
188 --let $expected_partition_id = 1
189 --source suite/rpl/include/rpl_partition_info.inc
190
191
192 # A single statement writing multiple rows in the same partition
193 # but different sub_partition should generate more than one rows event .
194 --source include/save_binlog_position.inc
195 INSERT INTO sp VALUES(103, 2), (104, 7);
196
197 # Test that two Write_rows_event is logged
198 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Write_rows # Write_rows # !Commit
199 --source include/assert_binlog_events.inc
200
201 # Check if the partition_id is correctly logged for this event.
202 --let $expected_partition_id = 1;0
203 --source suite/rpl/include/rpl_partition_info.inc
204
205 --echo -----UPDATE-----
206
207 # A single statement writing multiple rows in the same partition
208 # and sub_partition should generate just one row event.
209 --source include/save_binlog_position.inc
210 UPDATE sp SET age=5 WHERE id = 102 OR id=104;
211
212 # Check if the partition_id is correctly logged for this event.
213 --let $partition_id = 0 0
214 --source suite/rpl/include/rpl_partition_info_update.inc
215
216 # Test that only one Update_rows_event is logged
217 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Update_rows # !Commit
218 --source include/assert_binlog_events.inc
219
220 # A single statement writing multiple rows in the same partition
221 # but different sub_partition should generate more than one rows event .
222 --source include/save_binlog_position.inc
223 UPDATE sp SET age=11 WHERE id = 102 OR id=105;
224
225 # Test that two Update_rows_event is logged
226 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Update_rows # Update_rows # !Commit
227 --source include/assert_binlog_events.inc
228
229 # Check if the partition_id is correctly logged for this event.
230 --let $partition_id = 0 0;1 1
231 --source suite/rpl/include/rpl_partition_info_update.inc
232
233 --echo -----DELETE-----
234
235 # A single statement writing multiple rows in the same partition
236 # and sub_partition should generate just one row event.
237 --source include/save_binlog_position.inc
238 DELETE FROM sp WHERE id = 101 OR id=105;
239
240 # Test that only one Delete_rows_event is logged
241 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Delete_rows # !Commit
242 --source include/assert_binlog_events.inc
243
244 # Check if the partition_id is correctly logged for this event.
245 --let $expected_partition_id = 1
246 --source suite/rpl/include/rpl_partition_info.inc
247
248 # A single statement writing multiple rows in the same partition
249 # but different sub_partition should generate more than one rows event .
250 --source include/save_binlog_position.inc
251 DELETE FROM sp WHERE id = 108 OR id=107;
252
253 # Test that two Delete_rows_event is logged
254 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Delete_rows # Delete_rows # !Commit
255 --source include/assert_binlog_events.inc
256
257 # Check if the partition_id is correctly logged for this event.
258 --let $expected_partition_id = 2;3
259 --source suite/rpl/include/rpl_partition_info.inc
260
261 --echo #####7.Sync with slave and check if all the tables are replicated correctly#####
262
263 --source include/sync_slave_sql_with_master.inc
264
265 let $diff_tables= master:sp, slave:sp;
266 --source include/diff_tables.inc
267
268 # Cleanup
269 connection master;
270 DROP TABLE sp;
271 RESET MASTER;
272 --source include/rpl_end.inc