create table t1 (n int not null auto_increment primary key);

insert into t1 values(NULL);

insert into t1 values(2);

select n from t1;

connection master;

drop table t1;

# End of 4.1 tests

insert into t1 values(1);

sync_slave_with_master;

stop slave;

start slave;

connection master;

insert into t1 values(2);

#let slave catch up

--source include/have_binlog_format_mixed_or_statement.inc

source include/master-slave.inc;

connection master;

create table t2(n int);

# exist in this connection.

drop table if exists t1,t2;

# End of 4.1 tests

master_password='',master_port=$MASTER_MYPORT, MASTER_CONNECT_RETRY=7;

source include/show_slave_status2.inc;

start slave;

sync_with_master;

source include/show_slave_status2.inc;

connection master;

create table mysqltest.t2 (n int);

insert into mysqltest.t2 values (45);

rename table mysqltest.t2 to mysqltest.t3, mysqltest.t1 to mysqltest.t2;

select * from mysqltest.t2;

select * from mysqltest.t3;

connection master;

drop database mysqltest;

# End of 4.1 tests

select * from t1;

use test1;

select * from t1;

SELECT hex(bit1) FROM test.t1 ORDER BY bit1;

SELECT hex(bit2) from test.t1 ORDER BY bit2;

SELECT hex(bit3) from test.t1 ORDER BY bit3;

SELECT oSupp, sSuppD, GSuppDf, VNotSupp, x034 FROM test.t1;

SELECT hex(bit1) FROM test.t1 ORDER BY bit1;

SELECT hex(bit2) from test.t1 ORDER BY bit2;

SELECT hex(bit1) from test.t1 ORDER BY bit1;

SELECT hex(bit2) from test.t1 ORDER BY bit2;

SELECT hex(bit3) from test.t1 ORDER BY bit3;

SELECT oSupp, sSuppD, GSuppDf, VNotSupp, x034

FROM test.t1

ORDER BY oSupp, sSuppD, GSuppDf, VNotSupp, x034;

SELECT a, hex(b) FROM test.t2 ORDER BY a,b;

SELECT * FROM test.t3 ORDER BY a,b;

SELECT a, hex(b) FROM test.t2 ORDER BY a,b;

SELECT * FROM test.t3 ORDER BY a,b;

# Add suppression for expected warnings in slaves error log

call mtr.add_suppression("Failed during slave I/O thread initialization");

# Set debug flags on slave to force errors to occur

SET GLOBAL debug="d,simulate_io_slave_error_on_init,simulate_sql_slave_error_on_init";

select * from t1;

connection master;

drop table t1;

# End of 4.1 tests

#############################################################

--source include/have_innodb.inc

# Set up circular ring and new names for servers

--echo *** Set up circular ring by schema A->B->C->D->A ***

--source include/circular_rpl_for_4_hosts_init.inc

--echo *** Create "wider" table on slave ***

sync_slave_with_master;

STOP SLAVE;

RESET SLAVE;

eval CREATE TABLE t1 (

--echo *** Start replication ***

connection slave;

START SLAVE;

--echo *** Insert data on master and display it. ***

connection master;

delete from mysql.user where user=_binary'rpl_do_grant';

delete from mysql.db where user=_binary'rpl_do_grant';

flush privileges;

# if these DELETE did nothing on the master, we need to do them manually on the

# slave.

delete from mysql.user where user=_binary'rpl_ignore_grant';

connection master;

grant select on *.* to rpl_do_grant@localhost;

grant drop on test.* to rpl_do_grant@localhost;

show grants for rpl_do_grant@localhost;

# test replication of SET PASSWORD

connection master;

set password for rpl_do_grant@localhost=password("does it work?");

select password<>_binary'' from mysql.user where user=_binary'rpl_do_grant';

#

set sql_mode='ANSI_QUOTES';

set password for rpl_do_grant@localhost=password('does it work?');

set sql_mode='';

select password<>'' from mysql.user where user='rpl_do_grant';

create table t1 (a int);

--error 1051

drop table t1, t2;

# End of 4.1 tests

--error 1146

select * from v1;

drop view v2, v3;

--error 1146

select * from v1;

--error 1146

eval change master to master_host="127.0.0.1",master_port=$SLAVE_MYPORT,master_user="root";

start slave;

# now we test it

# log-slave-updates and circul repl

#

stop slave;

create table t2 (n int); # create one ignored event

connection slave;

select * from t3;

start slave;

# BUG#13023 is that Exec_master_log_pos may stay too low "forever":

create table t5 (n int);

create table t6 (n int);

connection slave;

# cleanup

stop slave;

reset slave;

drop table t1,t2,t3,t4,t5,t6;

# End of 4.1 tests

--error 1022, ER_DUP_ENTRY

insert into t1 values (1),(1);

insert into t4 values (1),(2);

# as the t1 table is ignored on the slave, the slave should be able to sync

# check that the table has been ignored, because otherwise the test is nonsense

show tables like 't1';

show tables like 't4';

SELECT * FROM test.t4 ORDER BY a;

connection master;

drop table t1;

# Now test that even critical errors (connection killed)

# are ignored if rules allow it.

--error 0,1053,2013

reap;

connection master1;

SELECT * FROM test.t4 ORDER BY a;

connection master1;

DROP TABLE test.t4;

# End of 4.1 tests

# Adding comment for force manual merge 5.0 -> wl1012. delete me if needed

# Testing if "flush logs" command bouncing resulting in logs created in a loop

# in case of bi-directional replication

-- source include/master-slave.inc

let $MYSQLD_DATADIR= `select @@datadir`;

--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR/

connection slave;

--disable_warnings

stop slave;

--enable_warnings

--replace_result $MASTER_MYPORT MASTER_PORT

eval change master to master_host='127.0.0.1',master_user='root',

master_password='',master_port=$MASTER_MYPORT;

start slave;

#

# Start replication slave -> master

#

connection master;

--disable_warnings

stop slave;

--enable_warnings

--replace_result $SLAVE_MYPORT SLAVE_PORT

eval change master to master_host='127.0.0.1',master_user='root',

# restart replication for the next testcase

stop slave;

reset slave;

connection master;

reset master;

drop table t1;

connection slave;

start slave;

# testcase with INSERT SELECT

connection master;

select hex(a) from t1;

select hex(a) from t1;

connection master;

LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE t1;

connection slave;

source include/show_slave_status.inc;

connection slave;

SELECT COUNT(*) FROM mysqltest.t1;

# Now lets check the slave to see what we have :-)

SHOW DATABASES;

# Test logging on slave;

select count(*) from test.t1; # check that LOAD was replicated

source include/show_binlog_events.inc;

--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR

eval load data local infile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1;

--remove_file $MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile

select a,count(*) from t1 group by a;

connection master;

drop table t1;

# End of 4.1 tests

# See if master_pos_wait(,,timeout)

# Terminates with "timeout expired" (-1)

source include/master-slave.inc;

# Ask for a master log that has certainly not been reached yet

# timeout= 2 seconds

select master_pos_wait('master-bin.999999',0,2);

drop table t1;

sync_slave_with_master;

stop slave;

connection master;

# Test replication of timestamp

save_master_pos;

connection slave;

start slave;

sync_with_master;

select * from t2;

show create table t3;

connection master;

drop table t1,t2;

# End of 4.1 tests

insert into a values(2),(3);

delete a alias FROM a alias where alias.i=2;

select * from a;

use mysqltest_to;

select * from a;

# BUG#3461

connection master; # cleanup

drop table t1;

# If the machine is so fast that slave syncs before OPTIMIZE

# starts, this test wil demonstrate nothing but will pass.

create table `t1` (`f1` LONGTEXT) ENGINE=MyISAM;

INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1023');

eval select count(*) from `$db`.`t1` /* must be 1 */;

SHOW STATUS LIKE 'Slave_running';

select * from information_schema.session_status where variable_name= 'SLAVE_RUNNING';

connection master;

eval drop database $db;

#

# Bug #23755: Replicated event larger that max_allowed_packet infinitely re-transmits

###########################################################

source include/master-slave.inc;

#reset master;

#connection master;

set @var1='from-master-3';

execute stmt2 using @var1;

SELECT * FROM t1 ORDER BY n;

connection master;

drop table t1;

stop slave;

# End of 4.1 tests

#

EXECUTE stmt_d_1;

--echo

--echo

--echo # Connection: slave

--echo

--echo

SELECT * FROM t1;

use test;

DROP TABLE t1;

--echo

--echo

--echo # Connection: slave

--echo

--echo

STOP SLAVE;

source include/master-slave.inc;

connection slave;

stop slave;

connection master;

# This will generate a master's binlog > 10 bytes

create table t1 (a int);

update t1 set m=20 where m=16;

delete from t1 where m=17;

create table t11 select * from t1;

select * from t1 ORDER BY m;

select * from t2;

--error 1146

select * from t11;

connection master;

drop table if exists t1,t2,t11;

# show slave status, just to see of it prints replicate-do-table

--replace_result $MASTER_MYPORT MASTER_PORT

--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 #

reset master;

connection slave;

start slave;

connection master;

#

#restart slave skipping one event

set global sql_slave_skip_counter=1;

start slave;

connection master;

create table t4 select * from temp_table;

source include/show_binary_logs.inc;

source include/show_master_status.inc;

select * from t4;

source include/show_slave_status2.inc;

--disable_query_log

set @storage_engine = @@global.storage_engine;

STOP SLAVE;

SET GLOBAL storage_engine=memory;

START SLAVE;

--enable_query_log

--source include/reset_master_and_slave.inc

sync_slave_with_master;

# Here we reset the value of the default storage engine

STOP SLAVE;

SET GLOBAL storage_engine=@storage_engine;

START SLAVE;

--enable_ps_protocol

# BUG#22864 (Rollback following CREATE ... SELECT discards 'CREATE

--echo ================ BUG#22864 ================

connection slave;

STOP SLAVE;

RESET SLAVE;

connection master;

RESET MASTER;

connection slave;

START SLAVE;

connection master;

SET AUTOCOMMIT=0;

CREATE TABLE t1 (a INT);

# Some tests with temporary tables

connection slave;

STOP SLAVE;

RESET SLAVE;

connection master;

connection slave;

START SLAVE;

connection master;

CREATE TABLE t1 (a INT);

#(the server was started with skip-slave-start)

--disable_warnings

stop slave;

--enable_warnings

connection master;

reset master;

connection slave;

reset slave;

start slave;

connection master;

# We should be clean at this point, now we will run in the file from above.

# this test for position option

--disable_query_log

select "--- Test 2 position test --" as "";

--enable_query_log

# This is broken now

--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR

--disable_query_log

select "--- Test 4 Second Remote test --" as "";

--disable_warnings

stop slave;

--enable_warnings

connection master;

reset master;

connection slave;

reset slave;

start slave;

connection master;

# We should be clean at this point, now we will run in the file from above.

select "--- Test 7 reading stdin w/position --" as "";

--enable_query_log

--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR

# Bug#16217 (mysql client did not know how not switch its internal charset)

--disable_query_log

select "--- Test 8 switch internal charset --" as "";

--disable_warnings

stop slave;

--enable_warnings

connection master;

reset master;

connection slave;

reset slave;

start slave;

connection master;

create table t4 (f text character set utf8);

INSERT INTO test.t2 VALUES(NULL,'NEW'),(NULL,'NEW'),(NULL,'NEW'),(NULL,'NEW');

SELECT * FROM t2 ORDER BY a;

SELECT * FROM t2 ORDER BY a;

connection master;

connection master;

call test.p2(2);

SELECT * FROM t2 ORDER BY a;

SELECT * FROM t2 ORDER BY a;

connection master;

call test.p2(3);

SELECT * FROM t2 ORDER BY a;

SELECT * FROM t2 ORDER BY a;

##Used for debugging

let $message=< ---- Slave selects-- >;

--source include/show_msg.inc

SELECT * FROM test.t1 ORDER BY id;

SELECT * FROM test.t2 ORDER BY id2;

let $message=< ---- Slave selects-- >;

--source include/show_msg.inc

SELECT * FROM test.t3 ORDER BY id3;

connection master;

let $message=< ---- Slave selects-- >;

--source include/show_msg.inc

SELECT * FROM test.t2;

# Cleanup

CALL test.p1('a');

SELECT * FROM test.t2 ORDER BY a;

SELECT * FROM test.t2 ORDER BY a;

connection master;

truncate test.t2;

# this next call fails, but should not

call test.p1('b');

select * from test.t2 ORDER BY a;

SELECT * FROM test.t2 ORDER BY a;

connection master;

truncate test.t2;

SELECT * FROM test.t2 ORDER BY a;

SELECT * FROM test.t2 ORDER BY a;

# Cleanup

CALL test.p2();

SELECT * FROM test.t1 ORDER BY a;

show tables;

connection master;

CALL test.p4();

SELECT * FROM test.t2 ORDER BY a;

SELECT * FROM test.t2 ORDER BY a;

# Cleanup

SELECT * FROM test.t1 ORDER BY id;

#show binlog events;

SELECT * FROM test.t1 ORDER BY id;

connection master;

select * from test.t2;

select * from test.t3;

select * from test.t2;

select * from test.t3;

connection master;

SELECT * FROM test.t1 ORDER BY id;

connection master;

SELECT * FROM test.t1 ORDER BY id;

SELECT * FROM test.t1 ORDER BY id;

# Cleanup

DELETE FROM test.t2 WHERE id = 1;

connection master;

# time to dump the databases and so we can see if they match

reset master;

# replicate ourselves

stop slave;

--replace_result $SLAVE_MYPORT SLAVE_PORT

eval change master to master_port=$SLAVE_MYPORT;

start slave;

insert into t1 values (1);

save_master_pos;

sync_with_master;

# 'drop table t1' executed twice, so an error in the slave.err

# (not critical).

stop slave;

drop table t1;

set @@session.sql_mode=default;

insert into t1 values('1'||'2', 2);

select * from t1 where b<3 order by a;

select * from t1 where b<3 order by a;

connection master;

# if the slave does the next sync_with_master fine, then it means it accepts the

insert into t1 values(null);

insert into t2 select 2,a from t1 where a is null;

select * from t2 order by b;

select * from t2 order by b;

connection master;

drop table t1,t2;

#

# Bug #29878 Garbage data generation when executing SESSION_USER() on a slave.

--disable_warnings

INSERT INTO t1(data) VALUES(SESSION_USER());

--enable_warnings

SELECT length(data) < 100 FROM t1;

connection master;

drop table t1;

INSERT INTO t1 VALUES ('����������������','F0');

select "--- on master ---";

select hex(a),b from t1 order by b;

use mysqltest1;

select "--- on slave ---";

select hex(a),b from t1 order by b;

connection master;

drop database mysqltest1;

# End of 4.1 tests

connection slave;

source include/have_innodb.inc;

STOP SLAVE;

--echo **** On Master ****

connection master;

# Stop when reaching the the first table map event.

START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=762;

--replace_result $MASTER_MYPORT MASTER_PORT

--replace_column 1 # 8 # 9 # 23 # 33 # 35 # 36 #

query_vertical SHOW SLAVE STATUS;

# changed.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

sync_with_master;

# These values should be what was inserted, not what was

SELECT * FROM t2;

STOP SLAVE;

RESET SLAVE;

connection master;

RESET MASTER;

connection slave;

START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=106;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

sync_with_master;

--replace_result $MASTER_MYPORT MASTER_PORT

--replace_column 1 # 8 # 9 # 23 # 33 # 35 # 36 #

--echo

--echo ---> Synchronizing slave with master...

--echo

--echo ---> connection: master

--echo

--echo ---> Synchronizing slave with master...

--echo ---> connection: master

DROP PROCEDURE p1;

DROP FUNCTION f1;

--connection master

CALL test.p1();

SELECT * FROM test.t1 ORDER BY a;

SELECT * FROM test.t2 ORDER BY a;

SELECT * FROM test.t1 ORDER BY a;

SELECT * FROM test.t2 ORDER BY a;

USE test;

SHOW TABLES;

#SELECT * FROM test.t3;

USE test;

SHOW TABLES;

#SELECT * FROM test.t3;

SELECT * FROM test.t1 ORDER BY a;

SELECT * FROM test.t2 ORDER BY a;

#SELECT * FROM test.t3;

SELECT * FROM test.t1 ORDER BY a;

SELECT * FROM test.t2 ORDER BY a;

#SELECT * FROM test.t3;

insert into t1 values (4),(NULL);

sync_slave_with_master;

stop slave;

start slave;

connection master;

insert into t1 values (NULL),(NULL);

flush logs;

connection master;

grant replication slave on *.* to replssl@localhost require ssl;

create table t1 (t int);

#syncing with master

#trying to use this user without ssl

stop slave;

change master to master_user='replssl',master_password='';

start slave;

#showing that replication could work with ssl params

stop slave;

--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR

eval change master to master_ssl=1 , master_ssl_ca ='$MYSQL_TEST_DIR/std_data/cacert.pem', master_ssl_cert='$MYSQL_TEST_DIR/std_data/client-cert.pem', master_ssl_key='$MYSQL_TEST_DIR/std_data/client-key.pem';

start slave;

#avoiding unneeded sleeps

connection master;

#checking that replication is ok

select * from t1;

#checking if replication works without ssl also performing clean up

stop slave;

change master to master_user='root',master_password='', master_ssl=0;

start slave;

connection master;

drop user replssl@localhost;

drop table t1;

--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR $MASTER_MYPORT MASTER_MYPORT

--replace_column 1 # 6 # 7 # 8 # 9 # 10 # 11 # 16 # 22 # 23 # 33 # 35 # 36 #

query_vertical show slave status;

# Start replication with ssl_verify_server_cert turned on

connection slave;

stop slave;

--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR

eval change master to

master_host="localhost",

master_ssl_key='$MYSQL_TEST_DIR/std_data/client-key.pem',

master_ssl_verify_server_cert=1;

start slave;

connection master;

create table t1 (t int);

create table t1(n int);

sync_slave_with_master;

stop slave;

connection master;

let $1=5000;

disable_query_log;

connection master;

drop table t1;

#

# Test is dependent on binlog positions

# Stop slave before it starts replication. Also sync with master

# to avoid nondeterministic behaviour.

--echo [on slave]

connect (master2,localhost,root,,);

connection master2;

drop table if exists t1,t2;

# Test need anonymous user when connection are made as "zedjzlcsjhd"

source include/add_anonymous_users.inc;

-- source include/master-slave.inc

# Clean up old slave's binlogs.

# test the slave immediately writes DROP TEMPORARY TABLE this_old_table).

# We wait for the slave to have written all he wants to the binlog

# (otherwise RESET MASTER may come too early).

reset master;

connection master;

--echo

--echo ---> Synchronizing slave with master...

--echo

--echo ---> connection: master

--echo

--echo ---> Synchronizing slave with master...

--echo ---> connection: master

DROP VIEW v1;

DROP TABLE t1;

--connection master

#