1# Test for the new options --start-datetime, stop-datetime, 2# and a few others. 3 4# TODO: Need to look at making row based version once new binlog client is complete. 5-- source include/have_binlog_format_mixed_or_statement.inc 6-- source include/binlog_start_pos.inc 7 8set sql_mode=""; 9set global binlog_checksum=NONE; 10--disable_warnings 11drop table if exists t1; 12--enable_warnings 13 14# We need this for getting fixed timestamps inside of this test. 15# I use a date in the past to keep a growing timestamp along the 16# binlog (including the Start_log_event). This test will work 17# unchanged everywhere, because mysql-test-run has fixed TZ, which it 18# exports (so mysqlbinlog has same fixed TZ). 19set @a=UNIX_TIMESTAMP("1970-01-21 15:32:22"); 20set timestamp=@a; 21reset master; 22create table t1 (a int auto_increment not null primary key, b char(3)); 23insert into t1 values(null, "a"); 24insert into t1 values(null, "b"); 25set timestamp=@a+2; 26--let $binlog_pos_760=query_get_value(SHOW MASTER STATUS, Position, 1) 27insert into t1 values(null, "c"); 28--let $binlog_pos_951=query_get_value(SHOW BINLOG EVENTS in 'master-bin.000001' from $binlog_pos_760, Pos, 5) 29set timestamp=@a+4; 30insert into t1 values(null, "d"); 31insert into t1 values(null, "e"); 32 33flush logs; 34set timestamp=@a+1; # this could happen on a slave 35insert into t1 values(null, "f"); 36--let $binlog_pos_135=query_get_value(SHOW BINLOG EVENTS in 'master-bin.000002', Pos, 4) 37--let $binlog_pos_203=query_get_value(SHOW BINLOG EVENTS in 'master-bin.000002', Pos, 5) 38 39# delimiters are for easier debugging in future 40 41--disable_query_log 42select "--- Local --" as ""; 43--enable_query_log 44 45# 46# We should use --short-form everywhere because in other case output will 47# be time dependent (the Start events). Better than nothing. 48# 49let $MYSQLD_DATADIR= `select @@datadir`; 50--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 51--exec $MYSQL_BINLOG --short-form --base64-output=never $MYSQLD_DATADIR/master-bin.000001 52 53--disable_query_log 54select "--- offset --" as ""; 55--enable_query_log 56--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 57--exec $MYSQL_BINLOG --short-form --offset=5 $MYSQLD_DATADIR/master-bin.000001 58--disable_query_log 59select "--- start-position --" as ""; 60--enable_query_log 61let $start_pos= `select @binlog_start_pos + 696`; 62--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 63--exec $MYSQL_BINLOG --short-form --start-position=$start_pos $MYSQLD_DATADIR/master-bin.000001 64--disable_query_log 65select "--- stop-position --" as ""; 66--enable_query_log 67let $stop_pos= `select @binlog_start_pos + 696`; 68--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 69--exec $MYSQL_BINLOG --short-form --stop-position=$stop_pos $MYSQLD_DATADIR/master-bin.000001 70--disable_query_log 71select "--- start and stop positions ---" as ""; 72--enable_query_log 73let $start_pos= `select @binlog_start_pos + 696`; 74let $stop_pos= `select @binlog_start_pos + 857`; 75--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 76--exec $MYSQL_BINLOG --short-form --start-position=$start_pos --stop-position=$stop_pos $MYSQLD_DATADIR/master-bin.000001 77--disable_query_log 78select "--- start-datetime --" as ""; 79--enable_query_log 80--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 81--exec $MYSQL_BINLOG --short-form "--start-datetime=1970-01-21 15:32:24" $MYSQLD_DATADIR/master-bin.000001 82--disable_query_log 83select "--- stop-datetime --" as ""; 84--enable_query_log 85--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 86--exec $MYSQL_BINLOG --short-form "--stop-datetime=1970-01-21 15:32:24" $MYSQLD_DATADIR/master-bin.000001 87 88--disable_query_log 89select "--- Local with 2 binlogs on command line --" as ""; 90--enable_query_log 91 92# This is to verify that some options apply only to first, or last binlog 93 94flush logs; 95--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 96--exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 97 98--disable_query_log 99select "--- offset --" as ""; 100--enable_query_log 101--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 102--exec $MYSQL_BINLOG --short-form --offset=5 $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 103--disable_query_log 104select "--- start-position --" as ""; 105--enable_query_log 106let $start_pos= `select @binlog_start_pos + 696`; 107--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 108--exec $MYSQL_BINLOG --short-form --start-position=$start_pos $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 109--disable_query_log 110select "--- stop-position --" as ""; 111--enable_query_log 112let $stop_pos= `select @binlog_start_pos + 134`; 113--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 114--exec $MYSQL_BINLOG --short-form --stop-position=$stop_pos $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 115--disable_query_log 116select "--- start-datetime --" as ""; 117--enable_query_log 118--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 119--exec $MYSQL_BINLOG --short-form "--start-datetime=1970-01-21 15:32:24" $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 120--disable_query_log 121select "--- stop-datetime --" as ""; 122--enable_query_log 123--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 124--exec $MYSQL_BINLOG --short-form "--stop-datetime=1970-01-21 15:32:24" $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 125 126--disable_query_log 127select "--- Remote --" as ""; 128--enable_query_log 129 130--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 131--exec $MYSQL_BINLOG --short-form --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 132 133--disable_query_log 134select "--- offset --" as ""; 135--enable_query_log 136--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 137--exec $MYSQL_BINLOG --short-form --offset=5 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 138--disable_query_log 139select "--- start-position --" as ""; 140--enable_query_log 141let $start_pos= `select @binlog_start_pos + 696`; 142--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 143--exec $MYSQL_BINLOG --short-form --start-position=$start_pos --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 144--disable_query_log 145select "--- stop-position --" as ""; 146--enable_query_log 147let $stop_pos= `select @binlog_start_pos + 696`; 148--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 149--exec $MYSQL_BINLOG --short-form --stop-position=$stop_pos --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 150--disable_query_log 151select "--- start and stop positions ---" as ""; 152--enable_query_log 153let $start_pos= `select @binlog_start_pos + 696`; 154let $stop_pos= `select @binlog_start_pos + 812`; 155--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 156--exec $MYSQL_BINLOG --short-form --start-position=$start_pos --stop-position $stop_pos --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 157--disable_query_log 158select "--- start-datetime --" as ""; 159--enable_query_log 160--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 161--exec $MYSQL_BINLOG --short-form "--start-datetime=1970-01-21 15:32:24" --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 162--disable_query_log 163select "--- stop-datetime --" as ""; 164--enable_query_log 165--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 166--exec $MYSQL_BINLOG --short-form "--stop-datetime=1970-01-21 15:32:24" --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 167 168--disable_query_log 169select "--- Remote with 2 binlogs on command line --" as ""; 170--enable_query_log 171 172--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 173--exec $MYSQL_BINLOG --short-form --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 174 175--disable_query_log 176select "--- offset --" as ""; 177--enable_query_log 178--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 179--exec $MYSQL_BINLOG --short-form --offset=5 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 180--disable_query_log 181select "--- start-position --" as ""; 182--enable_query_log 183let $start_pos= `select @binlog_start_pos + 696`; 184--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 185--exec $MYSQL_BINLOG --short-form --start-position=$start_pos --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 186--disable_query_log 187select "--- stop-position --" as ""; 188--enable_query_log 189let $stop_pos= `select @binlog_start_pos + 109`; 190--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 191--exec $MYSQL_BINLOG --short-form --stop-position=$stop_pos --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 192--disable_query_log 193select "--- start-datetime --" as ""; 194--enable_query_log 195--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 196--exec $MYSQL_BINLOG --short-form "--start-datetime=19700121153224" --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 197--disable_query_log 198select "--- stop-datetime --" as ""; 199--enable_query_log 200--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 201--exec $MYSQL_BINLOG --short-form "--stop-datetime=1970/01/21 15@32@24" --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 202 203--disable_query_log 204select "--- to-last-log --" as ""; 205--enable_query_log 206 207--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 208--exec $MYSQL_BINLOG --short-form --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --to-last-log master-bin.000001 209 210# clean up 211--disable_query_log 212select "--- end of test --" as ""; 213--enable_query_log 214drop table t1; 215 216set global binlog_checksum=default; 217# End of 4.1 tests 218