Debian Codegraph @

mysql-test/suite/galera/r/galera_partitioned_tables.result

raw
1 connection node_2;
2 connection node_1;
3 call mtr.add_suppression("WSREP: wsrep_mode = STRICT_REPLICATION enabled\\. Storage engine partition for table");
4 # wsrep-mode= DEFAULT
5 SET GLOBAL wsrep_mode = "";
6 SELECT @@wsrep_mode;
7 @@wsrep_mode
8
9 CREATE OR REPLACE TABLE t1 (v1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB
10 PARTITION BY KEY (v1)
11 PARTITIONS 2;
12 CREATE OR REPLACE TABLE t2 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
13 PARTITION BY KEY (v1)
14 PARTITIONS 2;
15 ALTER TABLE t1 ADD COLUMN v2 int;
16 ALTER TABLE t2 ADD COLUMN v2 int;
17 INSERT INTO t1 VALUES (1,1),(2,2);
18 INSERT INTO t2 VALUES (1,1),(2,2);
19 ALTER TABLE t1 ADD COLUMN v3 int, ENGINE=MyISAM;
20 ALTER TABLE t2 ADD COLUMN v3 int, ENGINE=Aria;
21 UPDATE t1 SET v3 = 3;
22 UPDATE t2 SET v3 = 3;
23 CREATE INDEX xx1 ON t1(v2);
24 CREATE INDEX xx2 ON t2(v2);
25 DROP INDEX xx1 ON t1;
26 DROP INDEX xx2 ON t2;
27 TRUNCATE TABLE t1;
28 TRUNCATE TABLE t2;
29 RENAME TABLE t1 TO t1_v2;
30 RENAME TABLE t2 TO t2_v2;
31 CREATE VIEW x1 AS SELECT * FROM t1_v2;
32 CREATE VIEW x2 AS SELECT * FROM t2_v2;
33 CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t1
34 AFTER INSERT ON t1_v2 FOR EACH ROW
35 UPDATE t1_v2 SET t1_v2.v3 = t1_v2.v3+1;
36 CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t2
37 AFTER INSERT ON t2_v2 FOR EACH ROW
38 UPDATE t2_v2 SET t2_v2.v3 = t2_v2.v3+1;
39 connection node_2;
40 SHOW CREATE TABLE t1_v2;
41 Table Create Table
42 t1_v2 CREATE TABLE `t1_v2` (
43 `v1` int(11) NOT NULL,
44 `v2` int(11) DEFAULT NULL,
45 `v3` int(11) DEFAULT NULL,
46 PRIMARY KEY (`v1`)
47 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
48 PARTITION BY KEY (`v1`)
49 PARTITIONS 2
50 SHOW CREATE TABLE t2_v2;
51 Table Create Table
52 t2_v2 CREATE TABLE `t2_v2` (
53 `v1` int(11) NOT NULL,
54 `v2` int(11) DEFAULT NULL,
55 `v3` int(11) DEFAULT NULL,
56 PRIMARY KEY (`v1`)
57 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
58 PARTITION BY KEY (`v1`)
59 PARTITIONS 2
60 SHOW CREATE VIEW x1;
61 View Create View character_set_client collation_connection
62 x1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `x1` AS select `t1_v2`.`v1` AS `v1`,`t1_v2`.`v2` AS `v2`,`t1_v2`.`v3` AS `v3` from `t1_v2` latin1 latin1_swedish_ci
63 SHOW CREATE VIEW x2;
64 View Create View character_set_client collation_connection
65 x2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `x2` AS select `t2_v2`.`v1` AS `v1`,`t2_v2`.`v2` AS `v2`,`t2_v2`.`v3` AS `v3` from `t2_v2` latin1 latin1_swedish_ci
66 SELECT * FROM t1_v2;
67 v1 v2 v3
68 SELECT * FROM t2_v2;
69 v1 v2 v3
70 connection node_1;
71 DROP VIEW x1;
72 DROP VIEW x2;
73 DROP TRIGGER increment_before_t1;
74 DROP TRIGGER increment_before_t2;
75 DROP TABLE t1_v2;
76 DROP TABLE t2_v2;
77 SET GLOBAL wsrep_mode = "";
78 CREATE OR REPLACE TABLE t2 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
79 PARTITION BY KEY (v1)
80 PARTITIONS 2;
81 # wsrep-mode= STRICT_REPLICATION
82 SET GLOBAL wsrep_mode = "STRICT_REPLICATION";
83 SELECT @@wsrep_mode;
84 @@wsrep_mode
85 STRICT_REPLICATION
86 CREATE OR REPLACE TABLE t1 (v1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB
87 PARTITION BY KEY (v1)
88 PARTITIONS 2;
89 CREATE OR REPLACE TABLE t3 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
90 PARTITION BY KEY (v1)
91 PARTITIONS 2;
92 ERROR HY000: Galera replication not supported
93 ALTER TABLE t1 ADD COLUMN v2 int;
94 ALTER TABLE t2 ADD COLUMN v2 int;
95 ERROR HY000: Galera replication not supported
96 INSERT INTO t1 VALUES (1,1),(2,2);
97 INSERT INTO t2 VALUES (1),(2);
98 Warnings:
99 Warning 1290 WSREP: wsrep_mode = STRICT_REPLICATION enabled. Storage engine partition for table 'test'.'t2' is not supported in Galera
100 ALTER TABLE t1 ADD COLUMN v3 int, ENGINE=MyISAM;
101 ERROR HY000: Galera replication not supported
102 ALTER TABLE t2 ADD COLUMN v3 int, ENGINE=Aria;
103 ERROR HY000: Galera replication not supported
104 UPDATE t1 SET v2 = v2 + 3;
105 UPDATE t2 SET v1 = v1 + 3;
106 Warnings:
107 Warning 1290 WSREP: wsrep_mode = STRICT_REPLICATION enabled. Storage engine partition for table 'test'.'t2' is not supported in Galera
108 CREATE INDEX xx1 ON t1(v2);
109 CREATE INDEX xx2 ON t2(v2);
110 ERROR HY000: Galera replication not supported
111 DROP INDEX xx1 ON t1;
112 DROP INDEX xx2 on t2;
113 ERROR HY000: Galera replication not supported
114 TRUNCATE TABLE t1;
115 TRUNCATE TABLE t2;
116 ERROR HY000: Galera replication not supported
117 RENAME TABLE t1 TO t1_v2;
118 RENAME TABLE t2 TO t2_v2;
119 RENAME TABLE t2_v2 TO t2;
120 CREATE VIEW x1 AS SELECT * FROM t1_v2;
121 CREATE VIEW x2 AS SELECT * FROM t2;
122 ERROR HY000: Galera replication not supported
123 CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t1
124 AFTER INSERT ON t1_v2 FOR EACH ROW
125 UPDATE t1_v2 SET t1_v2.v2 = t1_v2.v2+1;
126 CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t2
127 AFTER INSERT ON t2 FOR EACH ROW
128 UPDATE t2 SET t2.v1 = t2.v1+1;
129 ERROR HY000: Galera replication not supported
130 connection node_2;
131 SHOW CREATE TABLE t1_v2;
132 Table Create Table
133 t1_v2 CREATE TABLE `t1_v2` (
134 `v1` int(11) NOT NULL,
135 `v2` int(11) DEFAULT NULL,
136 PRIMARY KEY (`v1`)
137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
138 PARTITION BY KEY (`v1`)
139 PARTITIONS 2
140 SHOW CREATE TABLE t2;
141 Table Create Table
142 t2 CREATE TABLE `t2` (
143 `v1` int(11) NOT NULL,
144 `v2` int(11) DEFAULT NULL,
145 PRIMARY KEY (`v1`)
146 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
147 PARTITION BY KEY (`v1`)
148 PARTITIONS 2
149 SHOW CREATE VIEW x1;
150 View Create View character_set_client collation_connection
151 x1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `x1` AS select `t1_v2`.`v1` AS `v1`,`t1_v2`.`v2` AS `v2` from `t1_v2` latin1 latin1_swedish_ci
152 SELECT * FROM t1_v2;
153 v1 v2
154 SELECT * FROM t2;
155 v1 v2
156 connection node_1;
157 DROP VIEW x1;
158 DROP TRIGGER increment_before_t1;
159 DROP TABLE t1_v2;
160 DROP TABLE t2;
161 SET GLOBAL wsrep_mode = "";
162 CREATE OR REPLACE TABLE t2 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
163 PARTITION BY KEY (v1)
164 PARTITIONS 2;
165 # wsrep-mode= STRICT_REPLICATION
166 SET GLOBAL wsrep_mode = "STRICT_REPLICATION";
167 SELECT @@wsrep_mode;
168 @@wsrep_mode
169 STRICT_REPLICATION
170 ALTER TABLE t2 ENGINE=InnoDB;
171 DROP TABLE t2;
172 connection node_1;
173 #
174 # MDEV-37373 InnoDB partition table disallow local GTIDs in galera
175 # wsrep-mode= DISALLOW_LOCAL_GTID
176 #
177 SET GLOBAL wsrep_mode = "DISALLOW_LOCAL_GTID";
178 SELECT @@wsrep_mode;
179 @@wsrep_mode
180 DISALLOW_LOCAL_GTID
181 CREATE TABLE `sales` (
182 `customer_id` int(11) NOT NULL,
183 `customer_name` varchar(40) DEFAULT NULL,
184 `store_id` varchar(20) NOT NULL,
185 `bill_number` int(11) NOT NULL,
186 `bill_date` date NOT NULL,
187 `amount` decimal(8,2) NOT NULL,
188 PRIMARY KEY (`bill_date`)
189 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
190 PARTITION BY RANGE (year(`bill_date`))
191 (PARTITION `p0` VALUES LESS THAN (2016) ENGINE = InnoDB,
192 PARTITION `p1` VALUES LESS THAN (2017) ENGINE = InnoDB,
193 PARTITION `p2` VALUES LESS THAN (2018) ENGINE = InnoDB,
194 PARTITION `p3` VALUES LESS THAN (2020) ENGINE = InnoDB);
195 INSERT INTO sales
196 VALUES (1, 'Michael', 'S001', 101, '2015-01-02', 125.56),
197 (2, 'Jim', 'S003', 103, '2015-01-25', 476.50),
198 (3, 'Dwight', 'S012', 122, '2016-02-15', 335.00),
199 (4, 'Andy', 'S345', 121, '2016-03-26', 787.00),
200 (5, 'Pam', 'S234', 132, '2017-04-19', 678.00),
201 (6, 'Karen', 'S743', 111, '2017-05-31', 864.00),
202 (7, 'Toby', 'S234', 115, '2018-06-11', 762.00),
203 (8, 'Oscar', 'S012', 125, '2019-07-24', 300.00),
204 (9, 'Darryl', 'S456', 119, '2019-08-02', 492.20);
205 SELECT * FROM sales;
206 customer_id customer_name store_id bill_number bill_date amount
207 1 Michael S001 101 2015-01-02 125.56
208 2 Jim S003 103 2015-01-25 476.50
209 3 Dwight S012 122 2016-02-15 335.00
210 4 Andy S345 121 2016-03-26 787.00
211 5 Pam S234 132 2017-04-19 678.00
212 6 Karen S743 111 2017-05-31 864.00
213 7 Toby S234 115 2018-06-11 762.00
214 8 Oscar S012 125 2019-07-24 300.00
215 9 Darryl S456 119 2019-08-02 492.20
216 SET GLOBAL wsrep_mode=DEFAULT;
217 connection node_2;
218 SELECT * FROM sales;
219 customer_id customer_name store_id bill_number bill_date amount
220 1 Michael S001 101 2015-01-02 125.56
221 2 Jim S003 103 2015-01-25 476.50
222 3 Dwight S012 122 2016-02-15 335.00
223 4 Andy S345 121 2016-03-26 787.00
224 5 Pam S234 132 2017-04-19 678.00
225 6 Karen S743 111 2017-05-31 864.00
226 7 Toby S234 115 2018-06-11 762.00
227 8 Oscar S012 125 2019-07-24 300.00
228 9 Darryl S456 119 2019-08-02 492.20
229 DROP TABLE sales;