rpl000009.test 5.34 KB
Newer Older
1 2
# This one assumes we are ignoring updates on tables in database mysqltest2,
# but doing the ones in database mysqltest
unknown's avatar
unknown committed
3

4
source include/master-slave.inc;
5
--disable_warnings
unknown's avatar
unknown committed
6
drop database if exists mysqltest;
7
drop database if exists mysqltest2;
unknown's avatar
unknown committed
8 9
drop database if exists mysqltest3;
connection slave;
10
drop database if exists mysqltest;
unknown's avatar
unknown committed
11 12 13 14
drop database if exists mysqltest2;
drop database if exists mysqltest3;
connection master;
create database mysqltest2;
15 16 17
create database mysqltest;
--enable_warnings

unknown's avatar
unknown committed
18
save_master_pos;
unknown's avatar
unknown committed
19
connection slave;
unknown's avatar
unknown committed
20
sync_with_master;
21
create database mysqltest2;
22 23
create table mysqltest2.foo (n int);
insert into mysqltest2.foo values(4);
unknown's avatar
unknown committed
24
connection master;
25 26 27 28
create table mysqltest2.foo (n int);
insert into mysqltest2.foo values(5);
create table mysqltest.bar (m int);
insert into mysqltest.bar values(15);
unknown's avatar
unknown committed
29
save_master_pos;
unknown's avatar
unknown committed
30
connection slave;
unknown's avatar
unknown committed
31
sync_with_master;
32
select mysqltest2.foo.n,mysqltest.bar.m from mysqltest2.foo,mysqltest.bar;
33
connection master;
34
drop database mysqltest;
unknown's avatar
unknown committed
35
drop database if exists mysqltest2;
unknown's avatar
unknown committed
36 37 38
save_master_pos;
connection slave;
sync_with_master;
39
--error 1008
unknown's avatar
unknown committed
40
drop database mysqltest;
unknown's avatar
unknown committed
41
drop database mysqltest2;
42

unknown's avatar
unknown committed
43 44 45
# Now let's test load data from master

# First create some databases and tables on the master
46 47 48

connection master;
set sql_log_bin = 0;
49 50
create database mysqltest2;
create database mysqltest;
51
show databases;
52 53 54 55
create table mysqltest2.t1(n int, s char(20));
create table mysqltest2.t2(n int, s text);
insert into mysqltest2.t1 values (1, 'one'), (2, 'two'), (3, 'three'); 
insert into mysqltest2.t2 values (11, 'eleven'), (12, 'twelve'), (13, 'thirteen'); 
56

57 58 59 60 61
create table mysqltest.t1(n int, s char(20));
create table mysqltest.t2(n int, s text);
insert into mysqltest.t1 values (1, 'one test'), (2, 'two test'), (3, 'three test'); 
insert into mysqltest.t2 values (11, 'eleven test'), (12, 'twelve test'),
 (13, 'thirteen test'); 
62 63 64 65 66
set sql_log_bin = 1;
save_master_pos;
connection slave;
sync_with_master;

unknown's avatar
unknown committed
67
# This should show that the slave is empty at this point
68
show databases;
unknown's avatar
unknown committed
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
# Create mysqltest2 and mysqltest3 on slave; we expect that LOAD DATA FROM
# MASTER will neither touch database mysqltest nor mysqltest3
create database mysqltest2;
create table mysqltest2.t1(n int, s char(20));
insert into mysqltest2.t1 values (1, 'original foo.t1');
create table mysqltest2.t3(n int, s char(20));
insert into mysqltest2.t3 values (1, 'original foo.t3');
create database mysqltest3;
create table mysqltest3.t1(n int, s char(20));
insert into mysqltest3.t1 values (1, 'original foo2.t1');

# Create mysqltest, and mysqltest.t1, to check that it gets replaced,
# and mysqltest.t3 to check that it is not touched (there is no
# mysqltest.t3 on master)
create database mysqltest;
create table mysqltest.t1(n int, s char(20));
insert into mysqltest.t1 values (1, 'original bar.t1');
create table mysqltest.t3(n int, s char(20));
insert into mysqltest.t3 values (1, 'original bar.t3');
88

89 90
load data from master;

unknown's avatar
unknown committed
91
# Now let's check if we have the right tables and the right data in them
92
show databases;
93
use mysqltest2;
unknown's avatar
unknown committed
94 95 96 97 98 99 100 101

# LOAD DATA FROM MASTER uses only replicate_*_db rules to decide which
# databases have to be copied. So it thinks "mysqltest" has to be
# copied. Before 4.0.16 it would first drop "mysqltest", then create
# "mysqltest". This "drop" is a bug; in that case t3 would disappear.  So
# here the effect of this bug (BUG#1248) would be to leave an empty
# "mysqltest" on the slave.

102 103
show tables; # should be t1 & t3
select * from t1; # should be slave's original
unknown's avatar
unknown committed
104
use mysqltest3;
105 106
show tables; # should be t1
select * from t1; # should be slave's original
107
use mysqltest;
108
show tables; # should contain master's copied t1&t2, slave's original t3
109 110
select * from mysqltest.t1;
select * from mysqltest.t2;
unknown's avatar
unknown committed
111
select * from mysqltest.t3;
112

unknown's avatar
unknown committed
113
# Now let's see if replication works
114
connection master;
115
insert into mysqltest.t1 values (4, 'four test');
116 117 118
save_master_pos;
connection slave;
sync_with_master;
119
select * from mysqltest.t1;
120

121 122 123 124 125 126 127 128 129
# Check that LOAD DATA FROM MASTER is able to create master.info
# if needed (if RESET SLAVE was used before), before writing to it (BUG#2922).

stop slave;
reset slave;
load data from master;
start slave;
# see if replication coordinates were restored fine
connection master;
130
insert into mysqltest.t1 values (5, 'five bar');
131 132 133
save_master_pos;
connection slave;
sync_with_master;
134
select * from mysqltest.t1;
135

136 137
# Check that LOAD DATA FROM MASTER reports the error if it can't drop a
# table to be overwritten.
138
# DISABLED FOR NOW AS chmod IS NOT PORTABLE ON NON-UNIX
unknown's avatar
unknown committed
139
# insert into mysqltest.t1 values(10, 'should be there');
140
# flush tables;
unknown's avatar
unknown committed
141
# system chmod 500 var/slave-data/mysqltest/;
142 143
# --error 6
# load data from master;  # should fail (errno 13)
unknown's avatar
unknown committed
144 145
# system chmod 700 var/slave-data/mysqltest/;
# select * from mysqltest.t1; # should contain the row (10, ...)
146 147 148 149


# Check that LOAD TABLE FROM MASTER fails if the table exists on slave
--error 1050
unknown's avatar
unknown committed
150 151 152 153 154 155 156
load table mysqltest.t1 from master;
drop table mysqltest.t1;
load table mysqltest.t1 from master;

# Check what happens when requestion not existing table
#
--error 1188
157 158 159
load table bar.t1 from master;

# as LOAD DATA FROM MASTER failed it did not restart slave threads
160 161
# DISABLED FOR NOW
# start slave;
162

unknown's avatar
unknown committed
163
# Now time for cleanup
164
connection master;
165 166
drop database mysqltest;
drop database mysqltest2;
167 168 169
save_master_pos;
connection slave;
sync_with_master;
unknown's avatar
unknown committed
170
# These has to be droped on slave as they are not replicated
unknown's avatar
unknown committed
171
drop database mysqltest2;
unknown's avatar
unknown committed
172
drop database mysqltest3;