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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
# This one assumes we are ignoring updates on tables in database mysqltest2,
# but doing the ones in database mysqltest
#################################################################
# Change Author: JBM
# Change Date: 2006-02-02
# Change: Added ENGINE=MyISAM
# Reason: LOAD from master is only supported by MyISAM
#################################################################
source include/master-slave.inc;
--disable_warnings
drop database if exists mysqltest;
drop database if exists mysqltest2;
drop database if exists mysqltest3;
connection slave;
drop database if exists mysqltest;
drop database if exists mysqltest2;
drop database if exists mysqltest3;
connection master;
create database mysqltest2;
create database mysqltest;
--enable_warnings
save_master_pos;
connection slave;
sync_with_master;
create database mysqltest2;
create table mysqltest2.foo (n int)ENGINE=MyISAM;
insert into mysqltest2.foo values(4);
connection master;
create table mysqltest2.foo (n int)ENGINE=MyISAM;
insert into mysqltest2.foo values(5);
create table mysqltest.bar (m int)ENGINE=MyISAM;
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))ENGINE=MyISAM;
create table mysqltest2.t2(n int, s text)ENGINE=MyISAM;
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))ENGINE=MyISAM;
create table mysqltest.t2(n int, s text)ENGINE=MyISAM;
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 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))ENGINE=MyISAM;
insert into mysqltest2.t1 values (1, 'original foo.t1');
create table mysqltest2.t3(n int, s char(20))ENGINE=MyISAM;
insert into mysqltest2.t3 values (1, 'original foo.t3');
create database mysqltest3;
create table mysqltest3.t1(n int, s char(20))ENGINE=MyISAM;
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))ENGINE=MyISAM;
insert into mysqltest.t1 values (1, 'original bar.t1');
create table mysqltest.t3(n int, s char(20))ENGINE=MyISAM;
insert into mysqltest.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 "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.
show tables; # should be t1 & t3
select * from t1; # should be slave's original
use mysqltest3;
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 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;
insert into mysqltest.t1 values (5, 'five bar');
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 mysqltest.t1 values(10, 'should be there');
# flush tables;
# system chmod 500 $MYSQLTEST_VARDIR/slave-data/mysqltest/;
# --error 6
# load data from master; # should fail (errno 13)
# system chmod 700 $MYSQLTEST_VARDIR/slave-data/mysqltest/;
# select * from mysqltest.t1; # should contain the row (10, ...)
# Check that LOAD TABLE FROM MASTER fails if the table exists on slave
--error 1050
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
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;
# These have to be dropped on slave because they are not replicated
drop database mysqltest2;
drop database mysqltest3;
# End of 4.1 tests
|
