1################################################################## 2# Author: JBM # 3# Date: 2006-02-22 # 4# Purpose: To test changes to mysqlbinlog for row based bin logs # 5# We are using .opt file since we need small binlog size # 6################################################################## 7-- source include/have_binlog_format_row.inc 8-- source include/not_embedded.inc 9-- source include/have_cp932.inc 10-- source include/master-slave.inc 11 12--echo ---Setup Section -- 13 14# we need this for getting fixed timestamps inside of this test 15set timestamp=1000000000; 16 17CREATE TABLE t1(word VARCHAR(20)); 18CREATE TABLE t2(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY); 19--let position= query_get_value(SHOW MASTER STATUS, Position, 1) 20CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5 FLOAT); 21--let stop_position=query_get_value(SHOW MASTER STATUS, Position, 1) 22--let stop_position1=`select $stop_position - 1` 23--let binlog_start_pos=query_get_value(SHOW BINLOG EVENTS LIMIT 1, End_log_pos, 1) 24 25# Test Section 26# Lets start by putting some data into the tables. 27 28INSERT INTO t1 VALUES ("abirvalg"); 29LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; 30LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; 31LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; 32LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; 33LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; 34 35# d1 length 3000 36set @d1 = 'dd1'; 37set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); 38set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); 39set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); 40 41--disable_query_log 42let count=500; 43while ($count) 44{ 45 INSERT INTO t2 VALUES (NULL); 46 eval INSERT INTO t3 VALUES ($count,@d1,'20060222000000','Tested in Texas',$count*2.2); 47 dec $count; 48} 49--enable_query_log 50 51--echo ---Test 1 check table load -- 52 53# Lets Check the tables on the Master 54SELECT COUNT(*) from t1; 55SELECT COUNT(*) from t2; 56SELECT COUNT(*) from t3; 57SELECT * FROM t1 ORDER BY word LIMIT 5; 58SELECT * FROM t2 ORDER BY id LIMIT 5; 59SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5; 60 61# Should have the same on the slave; 62 63sync_slave_with_master; 64SELECT COUNT(*) from t1; 65SELECT COUNT(*) from t2; 66SELECT COUNT(*) from t3; 67SELECT * FROM t1 ORDER BY word LIMIT 5; 68SELECT * FROM t2 ORDER BY id LIMIT 5; 69SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5; 70 71# Okay time to get busy, back to master 72 73connection master; 74 75# simple query to show more in second binlog 76insert into t1 values ("Alas"); 77flush logs; 78 79# delimiters are for easier debugging in future 80--echo --- Test 1 Dump binlog to file -- 81 82# 83# Prepare local temporary file to recreate what we have currently. 84let MYSQLD_DATADIR= `select @@datadir;`; 85--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/master.sql 86 87--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000002 >> $MYSQLTEST_VARDIR/tmp/master.sql 88 89# Now that we have our file, lets get rid of the current database. 90# Cleanup the master and the slave and try to recreate. 91--echo --- Test 1 delete tables, clean master and slave -- 92 93DROP TABLE t1; 94DROP TABLE t2; 95DROP TABLE t3; 96 97sync_slave_with_master; 98stop slave; 99--source include/wait_for_slave_to_stop.inc 100connection master; 101reset master; 102connection slave; 103reset slave; 104start slave; 105--source include/wait_for_slave_to_start.inc 106connection master; 107 108# We should be clean at this point, now we will run in the file from above. 109--echo --- Test 1 Load from Dump binlog file -- 110 111--exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/master.sql" 112 113--echo --- Test 1 Check Load Results -- 114 115# Lets Check the tables on the Master 116SELECT COUNT(*) from t1; 117SELECT COUNT(*) from t2; 118SELECT COUNT(*) from t3; 119SELECT * FROM t1 ORDER BY word LIMIT 5; 120SELECT * FROM t2 ORDER BY id LIMIT 5; 121SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5; 122 123# Should have the same on the slave; 124 125sync_slave_with_master; 126SELECT COUNT(*) from t1; 127SELECT COUNT(*) from t2; 128SELECT COUNT(*) from t3; 129SELECT * FROM t1 ORDER BY word LIMIT 5; 130SELECT * FROM t2 ORDER BY id LIMIT 5; 131SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5; 132connection master; 133 134# We should be gold by the time, so I will get rid of our file. 135 136remove_file $MYSQLTEST_VARDIR/tmp/master.sql; 137 138 139# this test for start-position option 140# By setting this position to 416, we should only get the create of t3 141--echo --- Test 2 position test -- 142--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --start-position=$position --stop-position=$stop_position $MYSQLD_DATADIR/master-bin.000001 143 144# These are tests for remote binlog. 145# They should return the same as previous test. 146 147--echo --- Test 3 First Remote test -- 148 149# This is broken now 150--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 151--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --stop-position=$stop_position --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 152 153--echo --- Test 4 Second Remote test -- 154--exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --to-last-log master-bin.000001 > $MYSQLTEST_VARDIR/tmp/remote.sql 155 156# Now that we have our file, lets get rid of the current database. 157# Cleanup the master and the slave and try to recreate. 158 159DROP TABLE t1; 160DROP TABLE t2; 161DROP TABLE t3; 162 163sync_slave_with_master; 164 165stop slave; 166--source include/wait_for_slave_to_stop.inc 167connection master; 168reset master; 169connection slave; 170reset slave; 171start slave; 172--source include/wait_for_slave_to_start.inc 173connection master; 174 175# We should be clean at this point, now we will run in the file from above. 176 177--exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/remote.sql" 178 179# Lets Check the tables on the Master 180 181SELECT COUNT(*) from t1; 182SELECT COUNT(*) from t2; 183SELECT COUNT(*) from t3; 184SELECT * FROM t1 ORDER BY word LIMIT 5; 185SELECT * FROM t2 ORDER BY id LIMIT 5; 186SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5; 187 188# Should have the same on the slave; 189 190sync_slave_with_master; 191SELECT COUNT(*) from t1; 192SELECT COUNT(*) from t2; 193SELECT COUNT(*) from t3; 194SELECT * FROM t1 ORDER BY word LIMIT 5; 195SELECT * FROM t2 ORDER BY id LIMIT 5; 196SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5; 197connection master; 198 199# We should be gold by the time, so I will get rid of our file. 200 201--remove_file $MYSQLTEST_VARDIR/tmp/remote.sql 202################### End Bug 17654 ###################### 203 204# What is the point of this test? It seems entirely pointless. It 205# might make sense for statement-based replication, but for row-based 206# replication the LOAD DATA INFILE is printed just as empty 207# transactions. /Matz 208 209# LOAD DATA 210--echo --- Test 5 LOAD DATA -- 211--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 212--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --stop-position=$binlog_start_pos --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 213 214# Bug#7853 (mysqlbinlog does not accept input from stdin) 215 216--echo --- Test 6 reading stdin -- 217--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR 218--exec $MYSQL_BINLOG --short-form --stop-position=$stop_position1 - < $MYSQLD_DATADIR/master-bin.000001 219 220--echo --- Test 7 reading stdin w/position -- 221--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR 222--exec $MYSQL_BINLOG --short-form --start-position=$position --stop-position=$stop_position - < $MYSQLD_DATADIR/master-bin.000001 223 224# Bug#16217 (mysql client did not know how not switch its internal charset) 225--echo --- Test 8 switch internal charset -- 226sync_slave_with_master; 227 228stop slave; 229--source include/wait_for_slave_to_stop.inc 230connection master; 231reset master; 232connection slave; 233reset slave; 234start slave; 235--source include/wait_for_slave_to_start.inc 236connection master; 237 238create table t4 (f text character set utf8); 239create table t5 (f text character set cp932); 240--exec $MYSQL --default-character-set=utf8 test -e "insert into t4 values(_utf8'ソ')" 241--exec $MYSQL --default-character-set=cp932 test -e "insert into t5 values(_cp932'�\');" 242flush logs; 243rename table t4 to t04, t5 to t05; 244--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL --default-character-set=utf8 245# original and recovered data must be equal 246select HEX(f) from t04; 247select HEX(f) from t4; 248select HEX(f) from t05; 249select HEX(f) from t5; 250 251# slave should have same 252sync_slave_with_master; 253select HEX(f) from t04; 254select HEX(f) from t4; 255select HEX(f) from t05; 256select HEX(f) from t5; 257 258--echo --- Test cleanup -- 259# clean up 260connection master; 261sync_slave_with_master; 262 263connection master; 264DROP TABLE t1, t2, t3, t04, t05, t4, t5; 265 266# BUG#17654 also test mysqlbinlog to ensure it can read the binlog from a remote server 267# and ensure that the results are the same as if read from a file (the same file). 268 269CREATE TABLE t1 (a INT NOT NULL KEY, b INT); 270INSERT INTO t1 VALUES(1,1); 271SELECT * FROM t1; 272FLUSH LOGS; 273 274--exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 > $MYSQLTEST_VARDIR/tmp/remote.sql 275--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/local.sql 276 277--diff_files $MYSQLTEST_VARDIR/tmp/local.sql $MYSQLTEST_VARDIR/tmp/remote.sql 278--remove_file $MYSQLTEST_VARDIR/tmp/remote.sql 279--remove_file $MYSQLTEST_VARDIR/tmp/local.sql 280DROP TABLE t1; 281 282sync_slave_with_master; 283 284# End of 4.1 tests 285--source include/rpl_end.inc 286