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 mysqltest2,
# but doing the ones in database mysqltest
source include/master-slave.inc;
--disable_warnings
drop database if exists mysqltest2;
create database mysqltest2;
drop database if exists mysqltest;
create database mysqltest;
--enable_warnings
save_master_pos;
connection slave;
sync_with_master;
create database mysqltest2;
create table mysqltest2.foo (n int);
insert into mysqltest2.foo values(4);
connection master;
create table mysqltest2.foo (n int);
insert into mysqltest2.foo values(5);
create table mysqltest.bar (m int);
insert into mysqltest.bar values(15);
save_master_pos;
connection slave;
sync_with_master;
select mysqltest2.foo.n,mysqltest.bar.m from mysqltest2.foo,mysqltest.bar;
connection master;
drop database mysqltest;
drop database if exists mysqltest2;
save_master_pos;
connection slave;
sync_with_master;
--error 1008
drop database mysqltest;
drop database mysqltest2;
# 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 mysqltest2;
create database mysqltest;
show databases;
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');
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');
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 mysqltest2;
# 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 mysqltest;
show tables; # should contain master's copied t1&t2, slave's original t3
select * from mysqltest.t1;
select * from mysqltest.t2;
select * from mysqltest.t3;
# Now let's see if replication works
connection master;
insert into mysqltest.t1 values (4, 'four test');
save_master_pos;
connection slave;
sync_with_master;
select * from mysqltest.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 mysqltest;
drop database mysqltest2;
save_master_pos;
connection slave;
sync_with_master;
drop database mysqltest;
drop database mysqltest2;
|
