1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
# This one assumes we are ignoring updates on tables in database foo, but doing
# the ones in database bar
source include/master-slave.inc;
drop database if exists foo;
create database foo;
drop database if exists bar;
create database bar;
save_master_pos;
connection slave;
sync_with_master;
create database foo;
drop table if exists foo.foo;
create table foo.foo (n int);
insert into foo.foo values(4);
connection master;
drop table if exists foo.foo;
create table foo.foo (n int);
insert into foo.foo values(5);
drop table if exists bar.bar;
create table bar.bar (m int);
insert into bar.bar values(15);
save_master_pos;
connection slave;
sync_with_master;
select foo.foo.n,bar.bar.m from foo.foo,bar.bar;
connection master;
drop database bar;
drop database if exists foo;
save_master_pos;
connection slave;
sync_with_master;
--error 1008
drop database bar;
drop database foo;
# Now let's test load data from master
# First create some databases and tables on the master
connection master;
set sql_log_bin = 0;
create database foo;
create database bar;
show databases;
create table foo.t1(n int, s char(20));
create table foo.t2(n int, s text);
insert into foo.t1 values (1, 'one'), (2, 'two'), (3, 'three');
insert into foo.t2 values (11, 'eleven'), (12, 'twelve'), (13, 'thirteen');
create table bar.t1(n int, s char(20));
create table bar.t2(n int, s text);
insert into bar.t1 values (1, 'one bar'), (2, 'two bar'), (3, 'three bar');
insert into bar.t2 values (11, 'eleven bar'), (12, 'twelve bar'),
(13, 'thirteen bar');
set sql_log_bin = 1;
save_master_pos;
connection slave;
sync_with_master;
# This should show that the slave is empty at this point
show databases;
# Create foo and foo2 on slave; we expect that LOAD DATA FROM MASTER will
# neither touch database foo nor foo2.
create database foo;
create table foo.t1(n int, s char(20));
insert into foo.t1 values (1, 'original foo.t1');
create table foo.t3(n int, s char(20));
insert into foo.t3 values (1, 'original foo.t3');
create database foo2;
create table foo2.t1(n int, s char(20));
insert into foo2.t1 values (1, 'original foo2.t1');
# Create bar, and bar.t1, to check that it gets replaced,
# and bar.t3 to check that it is not touched (there is no bar.t3 on master)
create database bar;
create table bar.t1(n int, s char(20));
insert into bar.t1 values (1, 'original bar.t1');
create table bar.t3(n int, s char(20));
insert into bar.t3 values (1, 'original bar.t3');
load data from master;
# Now let's check if we have the right tables and the right data in them
show databases;
use foo;
# LOAD DATA FROM MASTER uses only replicate_*_db rules to decide which databases
# have to be copied. So it thinks "foo" has to be copied. Before 4.0.16 it would
# first drop "foo", then create "foo". 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 "foo" on
# the slave.
show tables; # should be t1 & t3
select * from t1; # should be slave's original
use foo2;
show tables; # should be t1
select * from t1; # should be slave's original
use bar;
show tables; # should contain master's copied t1&t2, slave's original t3
select * from bar.t1;
select * from bar.t2;
select * from bar.t3;
# Now let's see if replication works
connection master;
insert into bar.t1 values (4, 'four bar');
save_master_pos;
connection slave;
sync_with_master;
select * from bar.t1;
# Check that LOAD DATA FROM MASTER reports the error if it can't drop a
# table to be overwritten.
# DISABLED FOR NOW AS chmod IS NOT PORTABLE ON NON-UNIX
# insert into bar.t1 values(10, 'should be there');
# flush tables;
# system chmod 500 var/slave-data/bar/;
# --error 6
# load data from master; # should fail (errno 13)
# system chmod 700 var/slave-data/bar/;
# select * from bar.t1; # should contain the row (10, ...)
# Check that LOAD TABLE FROM MASTER fails if the table exists on slave
--error 1050
load table bar.t1 from master;
drop table bar.t1;
load table bar.t1 from master;
# as LOAD DATA FROM MASTER failed it did not restart slave threads
# DISABLED FOR NOW
# start slave;
# Now time for cleanup
connection master;
drop database bar;
drop database foo;
save_master_pos;
connection slave;
sync_with_master;
drop database foo;
drop database foo2;
|
