Debian Codegraph @

mysql-test/suite/rpl/r/rpl_multi_source_basic.result

raw
1 #
2 # set up masters server_1 and server_3 with server_2 being a slave.
3 #.
4 include/rpl_init.inc [topology=1->2,3->2]
5 Warnings:
6 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
7 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
8 Warnings:
9 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
10 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
11 #
12 # Test case 1: 1.a) create a database and table db1.t1 on server_1
13 # and insert values in the table.
14 [connection server_1]
15 CREATE DATABASE db1;
16 CREATE TABLE db1.t1 ( a int);
17 INSERT INTO db1.t1 VALUES (10);
18 INSERT INTO db1.t1 VALUES (9);
19 INSERT INTO db1.t1 VALUES (8);
20 INSERT INTO db1.t1 VALUES (7);
21 INSERT INTO db1.t1 VALUES (6);
22 INSERT INTO db1.t1 VALUES (5);
23 INSERT INTO db1.t1 VALUES (4);
24 INSERT INTO db1.t1 VALUES (3);
25 INSERT INTO db1.t1 VALUES (2);
26 INSERT INTO db1.t1 VALUES (1);
27 SHOW DATABASES;
28 Database
29 db1
30 information_schema
31 mtr
32 mysql
33 performance_schema
34 sys
35 test
36 #
37 # 1.b create database and table db3.t1 on server_3
38 # and insert values in db3.t1
39 [connection server_3]
40 CREATE DATABASE db3;
41 CREATE TABLE db3.t1 (a int);
42 INSERT INTO db3.t1 VALUES (3*10);
43 INSERT INTO db3.t1 VALUES (3*9);
44 INSERT INTO db3.t1 VALUES (3*8);
45 INSERT INTO db3.t1 VALUES (3*7);
46 INSERT INTO db3.t1 VALUES (3*6);
47 INSERT INTO db3.t1 VALUES (3*5);
48 INSERT INTO db3.t1 VALUES (3*4);
49 INSERT INTO db3.t1 VALUES (3*3);
50 INSERT INTO db3.t1 VALUES (3*2);
51 INSERT INTO db3.t1 VALUES (3*1);
52 SHOW DATABASES;
53 Database
54 db3
55 information_schema
56 mtr
57 mysql
58 performance_schema
59 sys
60 test
61 #
62 # 1.c sync the slave (server_2) with both masters (server_1 and server_3)
63 #
64 [connection server_1]
65 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
66 [connection server_3]
67 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_3']
68 #
69 # 1.d Check that slave has replicated from both masters.
70 # Slave should have databases db1 and db3 and tables.
71 SHOW DATABASES;
72 Database
73 db1
74 db3
75 information_schema
76 mtr
77 mysql
78 performance_schema
79 sys
80 test
81 include/assert.inc [Slave should have 10 rows in db1.t1]
82 include/assert.inc [Slave should have 10 rows in db3.t1]
83 #
84 # Stop slave (on server_2) for test case 2.
85 #
86 include/rpl_stop_slaves.inc
87 #
88 # Test case 2: 2.a) Test that different tables in the same database
89 # are replicated to the slave
90 [connection server_1]
91 CREATE DATABASE dbcommon;
92 [connection server_2]
93 include/start_slave.inc [FOR CHANNEL 'channel_1']
94 [connection server_1]
95 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
96 include/stop_slave.inc [FOR CHANNEL 'channel_1']
97 [connection server_1]
98 CREATE TABLE dbcommon.t11(a int);
99 INSERT INTO dbcommon.t11 values (11*10);
100 INSERT INTO dbcommon.t11 values (11*9);
101 INSERT INTO dbcommon.t11 values (11*8);
102 INSERT INTO dbcommon.t11 values (11*7);
103 INSERT INTO dbcommon.t11 values (11*6);
104 INSERT INTO dbcommon.t11 values (11*5);
105 INSERT INTO dbcommon.t11 values (11*4);
106 INSERT INTO dbcommon.t11 values (11*3);
107 INSERT INTO dbcommon.t11 values (11*2);
108 INSERT INTO dbcommon.t11 values (11*1);
109 [connection server_3]
110 CREATE DATABASE IF NOT EXISTS dbcommon;
111 CREATE TABLE dbcommon.t33(a int);
112 INSERT INTO dbcommon.t33 values(33*10);
113 INSERT INTO dbcommon.t33 values(33*9);
114 INSERT INTO dbcommon.t33 values(33*8);
115 INSERT INTO dbcommon.t33 values(33*7);
116 INSERT INTO dbcommon.t33 values(33*6);
117 INSERT INTO dbcommon.t33 values(33*5);
118 INSERT INTO dbcommon.t33 values(33*4);
119 INSERT INTO dbcommon.t33 values(33*3);
120 INSERT INTO dbcommon.t33 values(33*2);
121 INSERT INTO dbcommon.t33 values(33*1);
122 #
123 # 2.b Start replication from the masters
124 #
125 [connection server_2]
126 include/rpl_start_slaves.inc
127 #
128 # 2.c sync the slave with both masters
129 #
130 [connection server_1]
131 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
132 [connection server_3]
133 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_3']
134 #
135 # 2.d Now check the data
136 #
137 SHOW DATABASES;
138 Database
139 db1
140 db3
141 dbcommon
142 information_schema
143 mtr
144 mysql
145 performance_schema
146 sys
147 test
148 USE dbcommon;
149 SHOW TABLES;
150 Tables_in_dbcommon
151 t11
152 t33
153 include/assert.inc [Slave should have 10 rows in dbcommon.t11]
154 include/assert.inc [Slave should have 10 rows in dbcommon.t33]
155 #
156 # Test case 3: 3.a) Update nonconflicting data on the same table
157 # and see if they are replicated to the slave.
158 [connection server_1]
159 CREATE TABLE dbcommon.t101(a int, PRIMARY KEY (a));
160 [connection server_1]
161 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
162 #
163 # Stop slave (on server_2) for test case 3.
164 #
165 include/rpl_stop_slaves.inc
166 [connection server_1]
167 INSERT INTO dbcommon.t101 values (101*10);
168 INSERT INTO dbcommon.t101 values (101*9);
169 INSERT INTO dbcommon.t101 values (101*8);
170 INSERT INTO dbcommon.t101 values (101*7);
171 INSERT INTO dbcommon.t101 values (101*6);
172 INSERT INTO dbcommon.t101 values (101*5);
173 INSERT INTO dbcommon.t101 values (101*4);
174 INSERT INTO dbcommon.t101 values (101*3);
175 INSERT INTO dbcommon.t101 values (101*2);
176 INSERT INTO dbcommon.t101 values (101*1);
177 [connection server_3]
178 CREATE TABLE IF NOT EXISTS dbcommon.t101(a int, PRIMARY KEY (a));
179 INSERT INTO dbcommon.t101 values(33*10);
180 INSERT INTO dbcommon.t101 values(33*9);
181 INSERT INTO dbcommon.t101 values(33*8);
182 INSERT INTO dbcommon.t101 values(33*7);
183 INSERT INTO dbcommon.t101 values(33*6);
184 INSERT INTO dbcommon.t101 values(33*5);
185 INSERT INTO dbcommon.t101 values(33*4);
186 INSERT INTO dbcommon.t101 values(33*3);
187 INSERT INTO dbcommon.t101 values(33*2);
188 INSERT INTO dbcommon.t101 values(33*1);
189 #
190 # 3.b Start replication from the masters
191 #
192 [connection server_2]
193 include/rpl_start_slaves.inc
194 #
195 # 3.c sync slave server with all the masters.
196 #
197 [connection server_1]
198 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
199 [connection server_3]
200 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_3']
201 #
202 # check that data has been replicated to the slave.
203 #
204 USE dbcommon;
205 SHOW TABLES;
206 Tables_in_dbcommon
207 t101
208 t11
209 t33
210 include/assert.inc [Slave should have 20 rows in dbcommon.t101]
211 #
212 # Test case 4: Update tables on both masters with conflicting data
213 # In this case, slave shall stop on a SQL thread.
214 # 4.a) Do an update on server_1 and sync with the slave.
215 [connection server_1]
216 INSERT INTO dbcommon.t101 values(1729);
217 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
218 # 4.b) Do a conflicting update on server_3 and check for conflicting error
219 [connection server_3]
220 INSERT INTO dbcommon.t101 values (1729);
221 [connection server_2]
222 call mtr.add_suppression("Replica SQL.*:.* 'Duplicate entry '1729' for key 't101.PRIMARY'' on query.*");
223 call mtr.add_suppression("Replica SQL.*: Could not execute Write_rows event on table dbcommon.t101; Duplicate entry '1729' for key 't101.PRIMARY'*");
224 call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state");
225 call mtr.add_suppression("Replica SQL.*:.* Duplicate entry '1729' for key 't101.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY.*");
226 include/wait_for_slave_sql_error.inc [errno=1062 FOR CHANNEL 'channel_3']
227 include/assert.inc [Value returned by SSS and PS table for Last_Error_Number should be same.]
228 DELETE FROM dbcommon.t101 where a=1729;
229 include/start_slave.inc [FOR CHANNEL 'channel_3']
230 # channel 1
231 [connection server_1]
232 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
233 # channel 3
234 [connection server_3]
235 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_3']
236 connection_server_1
237 DROP DATABASE db1;
238 DROP DATABASE dbcommon;
239 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
240 connection_server_3
241 DROP DATABASE db3;
242 DROP DATABASE IF EXISTS dbcommon;
243 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_3']
244 include/rpl_end.inc
245 RESET REPLICA ALL FOR CHANNEL 'channel_1';
246 RESET REPLICA ALL FOR CHANNEL 'channel_3';