Debian Codegraph @

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

raw
1 #
2 # Ensure that executing row-injected events (i.e. via BINLOG statments and
3 # row-based binlog events) uses historical partitions. That is, for tables
4 # which use system versioning and system_time partitions, MDEV-35096 reported
5 # that row-injected events would not be stored into the correct historical
6 # partition. This test considers both use cases of row-injected events.
7 #
8 # The test setup creates a system-versioned table with system_time-based
9 # partitioning and fills the table up with enough records that bypass the size
10 # limit of each historical partition.
11 #
12 # To test BINLOG statements, a series of BINLOG statements are used to delete
13 # all the records in the test tables, and the resulting partitions are analyzed
14 # to ensure that they match the partition specification. The BINLOG events
15 # were collected by running an original set of delete statements on the table
16 # data, and taking their binlog data from mysqlbinlog. Note these binary log
17 # events are actually Update events, because system versioning just archives
18 # the rows, rather than deleting them.
19 #
20 # To test row-based event replication, a slave replicates the master's
21 # events, and the partitions are compared between the slave and master for
22 # consistency.
23 #
24 # Note that the TIMESTAMP of this test is fixed so the BINLOG statements can
25 # identify the correct rows to delete (system versioning adds implicit fields
26 # `row_start` and `row_end`, which are automatically populated using the current
27 # timestamp).
28 #
29 #
30 # References:
31 # MDEV-35096: History is stored in different partitions on different nodes
32 # when using SYSTEM VERSION
33 #
34 --source include/have_64bit_timestamp.inc
35 --source include/have_binlog_format_row.inc
36 --source include/have_innodb.inc
37 --source include/have_partition.inc
38 --source include/master-slave.inc
39
40 --echo #
41 --echo # Initialize system-versioned and partitioned table and its data
42 --connection master
43
44 # Fix the timestamp for the system versioned row_start and row_end fields, so
45 # the later hard-coded BINLOG base64 data can find the rows.
46 SET timestamp=UNIX_TIMESTAMP('2025-01-01 01:00:00.000000');
47 RESET MASTER;
48
49 create table t1 (x int) engine=InnoDB with system versioning partition by system_time limit 3 partitions 5;
50 insert into t1 values(1);
51 insert into t1 values(2);
52 insert into t1 values(3);
53 insert into t1 values(4);
54 insert into t1 values(5);
55 --let $master_total_size= `select count(*) from t1`
56 --let $master_p0_size= `select count(*) from t1 partition (p0)`
57 --let $master_p1_size= `select count(*) from t1 partition (p1)`
58 --let $master_p2_size= `select count(*) from t1 partition (p2)`
59
60 --echo # Verifying master partitions are correct after data insertion..
61 if ($master_total_size != 5)
62 {
63 --echo # Master t1 count: $master_total_size
64 --die Master table t1 should have 5 entries
65 }
66 if ($master_p0_size)
67 {
68 --echo # Master t1,p0 count: $master_p0_size
69 --die Master t1 partition p0 should be empty
70 }
71 if ($master_p1_size)
72 {
73 --echo # Master t1,p1 count: $master_p1_size
74 --die Master t1 partition p1 should be empty
75 }
76 if ($master_p2_size)
77 {
78 --echo # Master t1,p2 count: $master_p2_size
79 --die Master t1 partition p2 should be empty
80 }
81 --echo # .. done
82
83 --sync_slave_with_master
84
85 --connection slave
86 --let $slave_total_size= `select count(*) from t1`
87 --let $slave_p0_size= `select count(*) from t1 partition (p0)`
88 --let $slave_p1_size= `select count(*) from t1 partition (p1)`
89 --let $slave_p2_size= `select count(*) from t1 partition (p2)`
90
91 --echo # Verifying partitions of master and slave match on data setup..
92 if ($slave_total_size != $master_total_size)
93 {
94 --connection master
95 select count(*) from t0;
96 --connection slave
97 select count(*) from t1;
98 --die Size of t1 differs between master and slave
99 }
100 if ($slave_p0_size != $master_p0_size)
101 {
102 --connection master
103 select count(*) from t1 partition (p0);
104 --connection slave
105 select count(*) from t1 partition (p0);
106 --die Size of t1 partition p0 differs between master and slave
107 }
108 if ($slave_p1_size != $master_p1_size)
109 {
110 --connection master
111 select count(*) from t1 partition (p1);
112 --connection slave
113 select count(*) from t1 partition (p1);
114 --die Size of t1 partition p1 differs between master and slave
115 }
116 if ($slave_p2_size != $master_p2_size)
117 {
118 --connection master
119 select count(*) from t1 partition (p2);
120 --connection slave
121 select count(*) from t1 partition (p2);
122 --die Size of t1 partition p2 differs between master and slave
123 }
124 --echo # .. done
125
126 --echo #
127 --echo # "Delete" each row -- these are the BINLOG commands generated by
128 --echo # mysqlbinlog from `delete from t1 where x=<n>` statments. Because the
129 --echo # table uses system versioning and system_time partition, the actual
130 --echo # events are updates, with added fields for the `row_start` and `row_end`
131 --echo # columns.
132 --connection master
133
134 --echo # BINLOG for Format Description event
135 BINLOG '
136 APZ0Zw8BAAAA/AAAAAABAAAAAAQAMTEuNy4yLU1hcmlhREItZGVidWctbG9nAAAAAAAAAAAAAAAA
137 AAAAAAAAAAAAAAAAAAAA9nRnEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
138 CgoKAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
139 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
140 AAAAAAAAAAAEEwQADQgICAoKCgGuUmo6
141 ';
142
143 --echo # BINLOG for delete from t1 where x=1;
144 BINLOG '
145 APZ0ZxMBAAAAMQAAAAAAAAAAACEAAAAAAAEABHRlc3QAAnQxAAMDERECBgYBoCHyJQ==
146 APZ0ZxgBAAAASAAAAAAAAAAAACEAAAAAAAEAAwcH+AEAAABndPYAAAAA/////w9CP/gBAAAAZ3T2
147 AAAAAGd09gAAAAAuqwNP
148 ';
149
150 --echo # BINLOG for delete from t1 where x=2;
151 BINLOG '
152 APZ0ZxMBAAAAMQAAAAAAAAAAACEAAAAAAAEABHRlc3QAAnQxAAMDERECBgYBoCHyJQ==
153 APZ0ZxgBAAAASAAAAAAAAAAAACEAAAAAAAEAAwcH+AIAAABndPYAAAAA/////w9CP/gCAAAAZ3T2
154 AAAAAGd09gAAAAAsSeT/
155 ';
156
157
158 --echo # BINLOG for delete from t1 where x=3;
159 BINLOG '
160 APZ0ZxMBAAAAMQAAAAAAAAAAACEAAAAAAAEABHRlc3QAAnQxAAMDERECBgYBoCHyJQ==
161 APZ0ZxgBAAAASAAAAAAAAAAAACEAAAAAAAEAAwcH+AMAAABndPYAAAAA/////w9CP/gDAAAAZ3T2
162 AAAAAGd09gAAAADS6EaQ
163 ';
164
165 --echo # BINLOG for delete from t1 where x=4;
166 BINLOG '
167 APZ0ZxMBAAAAMQAAAAAAAAAAACEAAAAAAAEABHRlc3QAAnQxAAMDERECBgYBoCHyJQ==
168 APZ0ZxgBAAAASAAAAAAAAAAAACEAAAAAAAEAAwcH+AQAAABndPYAAAAA/////w9CP/gEAAAAZ3T2
169 AAAAAGd09gAAAABpi1pF
170 ';
171
172 --echo # BINLOG for delete from t1 where x=5;
173 BINLOG '
174 APZ0ZxMBAAAAMQAAAAAAAAAAACEAAAAAAAEABHRlc3QAAnQxAAMDERECBgYBoCHyJQ==
175 APZ0ZxgBAAAASAAAAAAAAAAAACEAAAAAAAEAAwcH+AUAAABndPYAAAAA/////w9CP/gFAAAAZ3T2
176 AAAAAGd09gAAAACXKvgq
177 ';
178
179 --let $master_total_size= `select count(*) from t1`
180 --let $master_p0_size= `select count(*) from t1 partition (p0)`
181 --let $master_p1_size= `select count(*) from t1 partition (p1)`
182 --let $master_p2_size= `select count(*) from t1 partition (p2)`
183 --echo # Verifying master partitions are correct after deletion BINLOG stmts..
184 if ($master_total_size > 0)
185 {
186 --echo # Master t1 count: $master_total_size
187 --die Master table t1 should have 0 count
188 }
189 if ($master_p0_size != 3)
190 {
191 --echo # Master t1,p0 count: $master_p0_size
192 --die Master t1 partition p0 should have 3 entries
193 }
194 if ($master_p1_size != 2)
195 {
196 --echo # Master t1,p1 count: $master_p1_size
197 --die Master t1 partition p1 should have 2 entries
198 }
199 if ($master_p2_size)
200 {
201 --echo # Master t1,p2 count: $master_p2_size
202 --die Master t1 partition p2 should be empty
203 }
204 --echo # .. done
205 --sync_slave_with_master
206
207 --connection slave
208 --let $slave_total_size= `select count(*) from t1`
209 --let $slave_p0_size= `select count(*) from t1 partition (p0)`
210 --let $slave_p1_size= `select count(*) from t1 partition (p1)`
211 --let $slave_p2_size= `select count(*) from t1 partition (p2)`
212
213 if ($slave_total_size != $master_total_size)
214 {
215 --connection master
216 select count(*) from t1;
217 --connection slave
218 select count(*) from t1;
219 --die Size of t1 differs between master and slave
220 }
221 if ($slave_p0_size != $master_p0_size)
222 {
223 --connection master
224 select count(*) from t1 partition (p0);
225 --connection slave
226 select count(*) from t1 partition (p0);
227 --die Size of t1 partition p0 differs between master and slave
228 }
229 if ($slave_p1_size != $master_p1_size)
230 {
231 --connection master
232 select count(*) from t1 partition (p1);
233 --connection slave
234 select count(*) from t1 partition (p1);
235 --die Size of t1 partition p1 differs between master and slave
236 }
237 if ($slave_p2_size != $master_p2_size)
238 {
239 --connection master
240 select count(*) from t1 partition (p2);
241 --connection slave
242 select count(*) from t1 partition (p2);
243 --die Size of t1 partition p2 differs between master and slave
244 }
245
246 --connection master
247 drop table t1;
248
249 --source include/rpl_end.inc