Debian Codegraph @

mysql-test/suite/s3/replication_partition.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection slave;
4 use database;
5 connection master;
6 #
7 # Check replication of parititioned S3 tables
8 #
9 CREATE TABLE t1 (
10 c1 INT DEFAULT NULL
11 ) ENGINE=Aria
12 PARTITION BY HASH (c1)
13 PARTITIONS 3;
14 INSERT INTO t1 VALUE (1), (2), (101), (102), (201), (202);
15 ALTER TABLE t1 ENGINE=S3;
16 connection slave;
17 connection master;
18 ALTER TABLE t1 ADD PARTITION PARTITIONS 6;
19 select sum(c1) from t1;
20 sum(c1)
21 609
22 connection slave;
23 connection master;
24 ALTER TABLE t1 ADD COLUMN c INT;
25 select sum(c1) from t1;
26 sum(c1)
27 609
28 connection slave;
29 show create table t1;
30 Table Create Table
31 t1 CREATE TABLE `t1` (
32 `c1` int(11) DEFAULT NULL,
33 `c` int(11) DEFAULT NULL
34 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
35 PARTITION BY HASH (`c1`)
36 PARTITIONS 9
37 select sum(c1) from t1;
38 sum(c1)
39 609
40 connection master;
41 drop table t1;
42 #
43 # Checking that the slave is keeping in sync with changed partitions
44 #
45 CREATE TABLE t1 (
46 c1 int primary key,
47 c2 int DEFAULT NULL
48 ) ENGINE=InnoDB
49 PARTITION BY RANGE (c1)
50 (PARTITION p1 VALUES LESS THAN (200),
51 PARTITION p2 VALUES LESS THAN (300),
52 PARTITION p3 VALUES LESS THAN (400));
53 insert into t1 select seq*100,seq*100 from seq_1_to_3;
54 alter table t1 engine=S3;
55 show create table t1;
56 Table Create Table
57 t1 CREATE TABLE `t1` (
58 `c1` int(11) NOT NULL,
59 `c2` int(11) DEFAULT NULL,
60 PRIMARY KEY (`c1`)
61 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
62 PARTITION BY RANGE (`c1`)
63 (PARTITION `p1` VALUES LESS THAN (200) ENGINE = S3,
64 PARTITION `p2` VALUES LESS THAN (300) ENGINE = S3,
65 PARTITION `p3` VALUES LESS THAN (400) ENGINE = S3)
66 connection slave;
67 select sum(c1) from t1;
68 sum(c1)
69 600
70 stop slave;
71 connection master;
72 ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (500));
73 connection slave;
74 show create table t1;
75 Table Create Table
76 t1 CREATE TABLE `t1` (
77 `c1` int(11) NOT NULL,
78 `c2` int(11) DEFAULT NULL,
79 PRIMARY KEY (`c1`)
80 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
81 PARTITION BY RANGE (`c1`)
82 (PARTITION `p1` VALUES LESS THAN (200) ENGINE = S3,
83 PARTITION `p2` VALUES LESS THAN (300) ENGINE = S3,
84 PARTITION `p3` VALUES LESS THAN (400) ENGINE = S3,
85 PARTITION `p4` VALUES LESS THAN (500) ENGINE = S3)
86 select sum(c1) from t1;
87 sum(c1)
88 600
89 start slave;
90 connection master;
91 connection slave;
92 select sum(c1)+0 from t1;
93 sum(c1)+0
94 600
95 stop slave;
96 show create table t1;
97 Table Create Table
98 t1 CREATE TABLE `t1` (
99 `c1` int(11) NOT NULL,
100 `c2` int(11) DEFAULT NULL,
101 PRIMARY KEY (`c1`)
102 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
103 PARTITION BY RANGE (`c1`)
104 (PARTITION `p1` VALUES LESS THAN (200) ENGINE = S3,
105 PARTITION `p2` VALUES LESS THAN (300) ENGINE = S3,
106 PARTITION `p3` VALUES LESS THAN (400) ENGINE = S3,
107 PARTITION `p4` VALUES LESS THAN (500) ENGINE = S3)
108 connection master;
109 drop table t1;
110 connection slave;
111 select sum(c1) from t1;
112 ERROR 42S02: Table 'database.t1' doesn't exist
113 start slave;
114 connection master;
115 connection slave;
116 connection master;
117 #
118 # Check altering partitioned table to S3 and back
119 # Checks also rename partitoned table and drop partition
120 #
121 CREATE TABLE t2 (
122 c1 int primary key,
123 c2 int DEFAULT NULL
124 ) ENGINE=InnoDB
125 PARTITION BY RANGE (c1)
126 (PARTITION p1 VALUES LESS THAN (200),
127 PARTITION p2 VALUES LESS THAN (300),
128 PARTITION p3 VALUES LESS THAN (400));
129 insert into t2 select seq*100,seq*100 from seq_1_to_3;
130 alter table t2 engine=S3;
131 rename table t2 to t1;
132 alter table t1 drop partition p1;
133 connection slave;
134 select sum(c1) from t1;
135 sum(c1)
136 500
137 connection master;
138 alter table t1 engine=innodb;
139 connection slave;
140 select sum(c1) from t1;
141 sum(c1)
142 500
143 connection master;
144 drop table t1;
145 #
146 # Check that slaves ignores changes to S3 tables.
147 #
148 connection master;
149 CREATE TABLE t1 (
150 c1 int primary key,
151 c2 int DEFAULT NULL
152 ) ENGINE=InnoDB
153 PARTITION BY RANGE (c1)
154 (PARTITION p1 VALUES LESS THAN (200),
155 PARTITION p2 VALUES LESS THAN (300),
156 PARTITION p3 VALUES LESS THAN (400));
157 insert into t1 select seq*100,seq*100 from seq_1_to_3;
158 create table t2 like t1;
159 alter table t2 remove partitioning;
160 insert into t2 values (450,450);
161 connection slave;
162 stop slave;
163 connection master;
164 alter table t1 engine=s3;
165 alter table t2 engine=s3;
166 ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (500));
167 alter table t1 exchange partition p4 with table t2;
168 select count(*) from t1;
169 count(*)
170 4
171 drop table t1,t2;
172 connection slave;
173 start slave;
174 connection master;
175 connection slave;
176 select sum(c1) from t1;
177 ERROR 42S02: Table 'database.t1' doesn't exist
178 connection master;
179 #
180 # Check slave binary log
181 #
182 connection slave;
183 include/show_binlog_events.inc
184 Log_name Pos Event_type Server_id End_log_pos Info
185 slave-bin.000001 # Gtid # # GTID #-#-#
186 slave-bin.000001 # Query # # create database database
187 slave-bin.000001 # Gtid # # GTID #-#-#
188 slave-bin.000001 # Query # # use `database`; CREATE TABLE t1 (
189 c1 INT DEFAULT NULL
190 ) ENGINE=Aria
191 PARTITION BY HASH (c1)
192 PARTITIONS 3
193 slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
194 slave-bin.000001 # Query # # use `database`; INSERT INTO t1 VALUE (1), (2), (101), (102), (201), (202)
195 slave-bin.000001 # Query # # COMMIT
196 slave-bin.000001 # Gtid # # GTID #-#-#
197 slave-bin.000001 # Query # # use `database`; ALTER TABLE t1 ENGINE=S3
198 slave-bin.000001 # Gtid # # GTID #-#-#
199 slave-bin.000001 # Query # # use `database`; set @@sql_if_exists=1; ALTER TABLE t1 ADD PARTITION PARTITIONS 6
200 slave-bin.000001 # Gtid # # GTID #-#-#
201 slave-bin.000001 # Query # # use `database`; set @@sql_if_exists=1; ALTER TABLE t1 ADD COLUMN c INT
202 slave-bin.000001 # Gtid # # GTID #-#-#
203 slave-bin.000001 # Query # # use `database`; DROP TABLE IF EXISTS `t1` /* generated by server */
204 slave-bin.000001 # Gtid # # GTID #-#-#
205 slave-bin.000001 # Query # # use `database`; CREATE TABLE t1 (
206 c1 int primary key,
207 c2 int DEFAULT NULL
208 ) ENGINE=InnoDB
209 PARTITION BY RANGE (c1)
210 (PARTITION p1 VALUES LESS THAN (200),
211 PARTITION p2 VALUES LESS THAN (300),
212 PARTITION p3 VALUES LESS THAN (400))
213 slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
214 slave-bin.000001 # Query # # use `database`; insert into t1 select seq*100,seq*100 from seq_1_to_3
215 slave-bin.000001 # Xid # # COMMIT /* XID */
216 slave-bin.000001 # Gtid # # GTID #-#-#
217 slave-bin.000001 # Query # # use `database`; alter table t1 engine=S3
218 slave-bin.000001 # Gtid # # GTID #-#-#
219 slave-bin.000001 # Query # # use `database`; set @@sql_if_exists=1; ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (500))
220 slave-bin.000001 # Gtid # # GTID #-#-#
221 slave-bin.000001 # Query # # use `database`; DROP TABLE IF EXISTS `t1` /* generated by server */
222 slave-bin.000001 # Gtid # # GTID #-#-#
223 slave-bin.000001 # Query # # use `database`; CREATE TABLE t2 (
224 c1 int primary key,
225 c2 int DEFAULT NULL
226 ) ENGINE=InnoDB
227 PARTITION BY RANGE (c1)
228 (PARTITION p1 VALUES LESS THAN (200),
229 PARTITION p2 VALUES LESS THAN (300),
230 PARTITION p3 VALUES LESS THAN (400))
231 slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
232 slave-bin.000001 # Query # # use `database`; insert into t2 select seq*100,seq*100 from seq_1_to_3
233 slave-bin.000001 # Xid # # COMMIT /* XID */
234 slave-bin.000001 # Gtid # # GTID #-#-#
235 slave-bin.000001 # Query # # use `database`; alter table t2 engine=S3
236 slave-bin.000001 # Gtid # # GTID #-#-#
237 slave-bin.000001 # Query # # use `database`; set @@sql_if_exists=1; rename table t2 to t1
238 slave-bin.000001 # Gtid # # GTID #-#-#
239 slave-bin.000001 # Query # # use `database`; set @@sql_if_exists=1; alter table t1 drop partition p1
240 slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
241 slave-bin.000001 # Query # # use `database`; DROP TABLE IF EXISTS `t1` /* generated by server */
242 slave-bin.000001 # Query # # use `database`; CREATE OR REPLACE TABLE `t1` (
243 `c1` int(11) NOT NULL,
244 `c2` int(11) DEFAULT NULL,
245 PRIMARY KEY (`c1`)
246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
247 PARTITION BY RANGE (`c1`)
248 (PARTITION `p2` VALUES LESS THAN (300) ENGINE = InnoDB,
249 PARTITION `p3` VALUES LESS THAN (400) ENGINE = InnoDB)
250 slave-bin.000001 # Annotate_rows # # alter table t1 engine=innodb
251 slave-bin.000001 # Table_map # # table_id: # (database.t1)
252 slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
253 slave-bin.000001 # Xid # # COMMIT /* XID */
254 slave-bin.000001 # Gtid # # GTID #-#-#
255 slave-bin.000001 # Query # # use `database`; DROP TABLE IF EXISTS `t1` /* generated by server */
256 slave-bin.000001 # Gtid # # GTID #-#-#
257 slave-bin.000001 # Query # # use `database`; CREATE TABLE t1 (
258 c1 int primary key,
259 c2 int DEFAULT NULL
260 ) ENGINE=InnoDB
261 PARTITION BY RANGE (c1)
262 (PARTITION p1 VALUES LESS THAN (200),
263 PARTITION p2 VALUES LESS THAN (300),
264 PARTITION p3 VALUES LESS THAN (400))
265 slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
266 slave-bin.000001 # Query # # use `database`; insert into t1 select seq*100,seq*100 from seq_1_to_3
267 slave-bin.000001 # Xid # # COMMIT /* XID */
268 slave-bin.000001 # Gtid # # GTID #-#-#
269 slave-bin.000001 # Query # # use `database`; create table t2 like t1
270 slave-bin.000001 # Gtid # # GTID #-#-#
271 slave-bin.000001 # Query # # use `database`; alter table t2 remove partitioning
272 slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
273 slave-bin.000001 # Query # # use `database`; insert into t2 values (450,450)
274 slave-bin.000001 # Xid # # COMMIT /* XID */
275 slave-bin.000001 # Gtid # # GTID #-#-#
276 slave-bin.000001 # Query # # use `database`; alter table t1 engine=s3
277 slave-bin.000001 # Gtid # # GTID #-#-#
278 slave-bin.000001 # Query # # use `database`; alter table t2 engine=s3
279 slave-bin.000001 # Gtid # # GTID #-#-#
280 slave-bin.000001 # Query # # use `database`; set @@sql_if_exists=1; ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (500))
281 slave-bin.000001 # Gtid # # GTID #-#-#
282 slave-bin.000001 # Query # # use `database`; set @@sql_if_exists=1; alter table t1 exchange partition p4 with table t2
283 slave-bin.000001 # Gtid # # GTID #-#-#
284 slave-bin.000001 # Query # # use `database`; DROP TABLE IF EXISTS `t1`,`t2` /* generated by server */
285 connection master;
286 #
287 # clean up
288 #
289 connection slave;
290 include/rpl_end.inc