Debian Codegraph @

mysql-test/suite/ndb_binlog/t/ndb_binlog_check_binlog_index.inc

raw
1 #
2 # ndb_binlog_check_binlog_index
3 #
4 # This include file determines epoch boundaries from a Binlog using the
5 # mysqlbinlog tool
6 # It then compares the calculated boundaries with the contents of
7 # the ndb_binlog_index table
8 # The intention is to prove that :
9 # - for any epoch A
10 # - The mysql.ndb_binlog_index next pos for A is the first binlog location
11 # after the COMMIT event of epoch A
12 # - Therefore the presence of a committed epoch number in a slave cluster's
13 # ndb_apply_status table is taken to mean only that the transaction which
14 # wrote it committed.
15 # - The Slave should resume immediately after the committed transaction
16 # Note that :
17 # - The start position (Position, File) is vaguely defined and can refer
18 # to any position after the previously committed epoch's COMMIT event
19 # and before the epoch's BEGIN event. A test and fix for this
20 # exists, but has been shelved.
21 #
22
23 --disable_query_log
24 set sql_log_bin=0;
25
26 let have_next_pos=query_get_value(select count(1) as have_next_file from information_schema.COLUMNS where table_schema='mysql' and table_name='ndb_binlog_index' and column_name='next_file', have_next_file, 1);
27 if (!$have_next_pos)
28 {
29 --echo Nothing to verify
30 }
31 if ($have_next_pos)
32 {
33
34 let $MYSQLD_DATADIR= `select @@datadir;`;
35 let $tmp_file = $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql;
36 --exec $MYSQL_BINLOG --verbose $MYSQLD_DATADIR/binlog.000001 > $tmp_file
37
38 create table raw_binlog_rows(
39 txt varchar(1000) character set latin1
40 );
41
42 --eval load data local infile '$tmp_file' into table raw_binlog_rows columns terminated by '\n';
43 --remove_file $tmp_file
44
45 #select count(*) FROM raw_binlog_rows;
46
47 create table binlog_stmt_parts_unassoc(
48 txt varchar(1000) character set latin1,
49 line_count int NOT NULL AUTO_INCREMENT PRIMARY KEY,
50 stmt_boundary int,
51 tx_boundary int
52 );
53
54 # Auto incrementing line_count values preserve the order of the raw binlog rows.
55 # Use replace() here to get rid of any unwanted Windows
56 # CRs
57 insert into binlog_stmt_parts_unassoc (txt,stmt_boundary,tx_boundary)
58 select replace(txt, '\r', ''),
59 (txt like '%INSERT%' or # Identify statement boundaries
60 txt like '%UPDATE%' or
61 txt like '%DELETE%' or
62 txt = 'BEGIN' or
63 txt = 'COMMIT'),
64 txt = 'BEGIN' # Transaction boundary
65 from raw_binlog_rows
66 where
67 txt like '###%' OR
68 txt = 'BEGIN' OR
69 txt = 'COMMIT' OR
70 txt like '%# at%'; # Discard stuff we don't care about
71
72 #select * from binlog_stmt_parts_unassoc;
73
74 create table binlog_stmt_parts_assoc(
75 txt varchar(1000) character set latin1,
76 line_count int,
77 stmt_num int,
78 tx_num int,
79 key(stmt_num),
80 key(line_count),
81 key(txt)
82 );
83
84 insert into binlog_stmt_parts_assoc
85 ( select txt,
86 line_count,
87 SUM(stmt_boundary) OVER(ORDER BY line_count) AS stmt_count, # All rows from same stmt will
88 # have same stmt_num
89 SUM(tx_boundary) OVER(ORDER BY line_count) AS tx_count # Same transaction
90 from binlog_stmt_parts_unassoc order by line_count);
91
92 create table apply_status_stmt_nums (stmt_num int primary key);
93
94 insert into apply_status_stmt_nums
95 select stmt_num from binlog_stmt_parts_assoc
96 where txt like '%INSERT INTO mysql.ndb_apply_status%';
97
98 create table relevant_info(
99 txt varchar(1000) character set latin1,
100 tx_num int,
101 line_count int
102 );
103
104 insert into relevant_info
105 # Epoch number to tx_num mapping
106 # ### @2= <epoch>, <tx_num>, <line_count>
107 select bspa.txt, bspa.tx_num, bspa.line_count
108 from
109 binlog_stmt_parts_assoc as bspa,
110 apply_status_stmt_nums
111 where
112 (bspa.stmt_num = apply_status_stmt_nums.stmt_num
113 and
114 bspa.txt like '%@2=%') # Epoch number
115 union
116 # BEGIN and COMMIT event to tx_num and file position mapping
117 # BEGIN # at <offset>, <tx_num>, <line_count>
118 # COMMIT # at <offset>, <tx_num>, <line_count>
119 #
120 select concat(bspa2.txt, " ", bspa1.txt), bspa2.tx_num, bspa1.line_count
121 from
122 binlog_stmt_parts_assoc as bspa1,
123 binlog_stmt_parts_assoc as bspa2
124 where
125 (bspa2.txt = 'BEGIN' and
126 bspa1.line_count = bspa2.line_count - 1) # Line before BEGIN event
127 or
128 (bspa2.txt = 'COMMIT' and
129 bspa1.line_count = bspa2.line_count + 1); # Line after COMMIT event
130
131 #select * from relevant_info order by line_count;
132
133 create table epoch_info (num int NOT NULL AUTO_INCREMENT PRIMARY KEY, epoch bigint, start_pos bigint, next_pos bigint);
134 set @epoch_num=0;
135
136 insert into epoch_info (epoch , start_pos , next_pos)
137 select
138 (right(ri1.txt, length(ri1.txt) - length('### @2='))) + 0, # epoch number
139 (right(ri2.txt, length(ri2.txt) - length('BEGIN # at '))) + 0, # start pos
140 (right(ri3.txt, length(ri3.txt) - length('COMMIT # at '))) + 0 # end pos
141 from
142 relevant_info as ri1, relevant_info as ri2, relevant_info as ri3
143 where
144 ri1.tx_num = ri2.tx_num
145 and
146 ri1.tx_num = ri3.tx_num
147 and
148 ri1.txt like '%@2=%'
149 and
150 ri2.txt like '%BEGIN%'
151 and
152 ri3.txt like '%COMMIT%';
153
154 #select * from epoch_info order by num;
155
156 # Insert dummy row 0 to give start pos of first epoch
157 --let $first_event_pos= query_get_value(SHOW BINLOG EVENTS LIMIT 1, End_log_pos, 1)
158 eval insert into epoch_info values (0,0,0,$first_event_pos);
159
160
161 # Get epoch info where following epoch starts at end of previous epoch
162 create table adjusted_epoch_info (epoch bigint, start_pos bigint);
163
164 insert into adjusted_epoch_info
165 select e2.epoch, e1.next_pos as start_pos
166 from
167 epoch_info as e1, epoch_info as e2
168 where
169 e2.num = e1.num + 1;
170
171 #select * from adjusted_epoch_info;
172
173 # Should not return any rows
174 --echo ---------------------------------------------------------------------------
175 --echo Mismatches between Binlog index next_pos and Binlog COMMIT event pos
176 --echo ---------------------------------------------------------------------------
177
178 select binlog.epoch,
179 binlog.next_pos as calculated_pos,
180 binlog_index.next_position as stored_pos
181 from epoch_info as binlog,
182 mysql.ndb_binlog_index as binlog_index
183 where binlog.epoch = binlog_index.epoch AND
184 binlog.next_pos != binlog_index.next_position;
185
186 --echo Done
187
188 # Following commented out as it is an (understandably) non-deterministic
189 # race
190 #
191 #--echo ----------------------------------------------
192 #--echo Any gaps between epoch n next_pos and epoch n+1 start_pos
193 #--echo ----------------------------------------------
194 ## This indicates that other events (e.g. DDL) were inserted between the end of
195 ## one epoch and the recorded start pos of the next epoch
196 ##select binlog.epoch,
197 ## binlog.start_pos as calculated_start_pos,
198 ## bi.Position as stored_start_pos
199 #select count(1) > 0
200 # from
201 # adjusted_epoch_info as binlog,
202 # mysql.ndb_binlog_index as bi
203 # where
204 # binlog.epoch = bi.epoch
205 # and
206 # binlog.start_pos != bi.Position;
207 #
208 #--echo Done
209
210 # Following is commented out as it is an (understandably) non-deterministic
211 # race
212 #
213 #--echo -----------------------------------------------
214 #--echo Any stored start positions different to BEGIN positions
215 #--echo -----------------------------------------------
216 ## This indicates that other events (e.g. DDL) were inserted between the recorded
217 ## start of an epoch, and the actual start of the transaction (BEGIN)
218 ##
219 ##select binlog.epoch,
220 ## binlog.start_pos as calculated_pos,
221 ## binlog_index.position as stored_pos
222 #select count(1) > 0
223 # from epoch_info as binlog,
224 # mysql.ndb_binlog_index as binlog_index
225 # where binlog.epoch = binlog_index.epoch AND
226 # binlog.start_pos != binlog_index.position;
227 #
228 #--echo Done
229
230 # The following test is no longer relevant as epochs are
231 # not guaranteed adjacent.
232 #
233 #create table bi_offsets (count bigint,
234 # epoch bigint,
235 # start_pos bigint,
236 # next_pos bigint);
237
238 #set @epoch_count=0;
239
240 #insert into bi_offsets
241 # select @epoch_count:=@epoch_count+1,
242 # epoch, Position, next_position
243 # from mysql.ndb_binlog_index
244 # order by epoch asc;
245
246 #select * from bi_offsets order by epoch;
247
248 #--echo ----------------------------------------
249 #--echo Non adjacent epochs in ndb_binlog_index
250 #--echo ----------------------------------------
251
252 #select bio1.count, bio1.epoch, bio1.start_pos, bio1.next_pos,
253 # bio2.count, bio2.epoch, bio2.start_pos, bio2.next_pos
254 # from bi_offsets as bio1, bi_offsets as bio2
255 # where
256 # bio2.count = bio1.count + 1 AND
257 # bio2.start_pos != bio1.next_pos;
258
259 #--echo Done.
260
261 #drop table bi_offsets;
262
263 drop table adjusted_epoch_info;
264 drop table epoch_info;
265 drop table relevant_info;
266 drop table apply_status_stmt_nums;
267 drop table binlog_stmt_parts_assoc;
268 drop table binlog_stmt_parts_unassoc;
269 drop table raw_binlog_rows;
270 }
271 set sql_log_bin=1;
272 --enable_query_log