| 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 |