Debian Codegraph @

mysql-test/suite/galera/t/galera_partitioned_tables.test

raw
1 --source include/galera_cluster.inc
2 --source include/have_partition.inc
3 --source include/have_innodb.inc
4 --source include/have_aria.inc
5
6 call mtr.add_suppression("WSREP: wsrep_mode = STRICT_REPLICATION enabled\\. Storage engine partition for table");
7
8 --echo # wsrep-mode= DEFAULT
9 SET GLOBAL wsrep_mode = "";
10 SELECT @@wsrep_mode;
11 CREATE OR REPLACE TABLE t1 (v1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB
12 PARTITION BY KEY (v1)
13 PARTITIONS 2;
14 CREATE OR REPLACE TABLE t2 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
15 PARTITION BY KEY (v1)
16 PARTITIONS 2;
17 ALTER TABLE t1 ADD COLUMN v2 int;
18 ALTER TABLE t2 ADD COLUMN v2 int;
19 INSERT INTO t1 VALUES (1,1),(2,2);
20 INSERT INTO t2 VALUES (1,1),(2,2);
21 ALTER TABLE t1 ADD COLUMN v3 int, ENGINE=MyISAM;
22 ALTER TABLE t2 ADD COLUMN v3 int, ENGINE=Aria;
23 UPDATE t1 SET v3 = 3;
24 UPDATE t2 SET v3 = 3;
25 CREATE INDEX xx1 ON t1(v2);
26 CREATE INDEX xx2 ON t2(v2);
27 DROP INDEX xx1 ON t1;
28 DROP INDEX xx2 ON t2;
29 TRUNCATE TABLE t1;
30 TRUNCATE TABLE t2;
31 RENAME TABLE t1 TO t1_v2;
32 RENAME TABLE t2 TO t2_v2;
33 CREATE VIEW x1 AS SELECT * FROM t1_v2;
34 CREATE VIEW x2 AS SELECT * FROM t2_v2;
35 CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t1
36 AFTER INSERT ON t1_v2 FOR EACH ROW
37 UPDATE t1_v2 SET t1_v2.v3 = t1_v2.v3+1;
38 CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t2
39 AFTER INSERT ON t2_v2 FOR EACH ROW
40 UPDATE t2_v2 SET t2_v2.v3 = t2_v2.v3+1;
41
42 --connection node_2
43 SHOW CREATE TABLE t1_v2;
44 SHOW CREATE TABLE t2_v2;
45 SHOW CREATE VIEW x1;
46 SHOW CREATE VIEW x2;
47
48 SELECT * FROM t1_v2;
49 SELECT * FROM t2_v2;
50
51 --connection node_1
52 DROP VIEW x1;
53 DROP VIEW x2;
54 DROP TRIGGER increment_before_t1;
55 DROP TRIGGER increment_before_t2;
56 DROP TABLE t1_v2;
57 DROP TABLE t2_v2;
58
59 SET GLOBAL wsrep_mode = "";
60 CREATE OR REPLACE TABLE t2 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
61 PARTITION BY KEY (v1)
62 PARTITIONS 2;
63 --echo # wsrep-mode= STRICT_REPLICATION
64 SET GLOBAL wsrep_mode = "STRICT_REPLICATION";
65 SELECT @@wsrep_mode;
66 CREATE OR REPLACE TABLE t1 (v1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB
67 PARTITION BY KEY (v1)
68 PARTITIONS 2;
69 --error ER_GALERA_REPLICATION_NOT_SUPPORTED
70 CREATE OR REPLACE TABLE t3 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
71 PARTITION BY KEY (v1)
72 PARTITIONS 2;
73 ALTER TABLE t1 ADD COLUMN v2 int;
74 --error ER_GALERA_REPLICATION_NOT_SUPPORTED
75 ALTER TABLE t2 ADD COLUMN v2 int;
76 INSERT INTO t1 VALUES (1,1),(2,2);
77 INSERT INTO t2 VALUES (1),(2);
78 --error ER_GALERA_REPLICATION_NOT_SUPPORTED
79 ALTER TABLE t1 ADD COLUMN v3 int, ENGINE=MyISAM;
80 --error ER_GALERA_REPLICATION_NOT_SUPPORTED
81 ALTER TABLE t2 ADD COLUMN v3 int, ENGINE=Aria;
82 UPDATE t1 SET v2 = v2 + 3;
83 UPDATE t2 SET v1 = v1 + 3;
84 CREATE INDEX xx1 ON t1(v2);
85 --error ER_GALERA_REPLICATION_NOT_SUPPORTED
86 CREATE INDEX xx2 ON t2(v2);
87 DROP INDEX xx1 ON t1;
88 --error ER_GALERA_REPLICATION_NOT_SUPPORTED
89 DROP INDEX xx2 on t2;
90 TRUNCATE TABLE t1;
91 --error ER_GALERA_REPLICATION_NOT_SUPPORTED
92 TRUNCATE TABLE t2;
93 # At the moment can't restrict rename
94 RENAME TABLE t1 TO t1_v2;
95 RENAME TABLE t2 TO t2_v2;
96 RENAME TABLE t2_v2 TO t2;
97 CREATE VIEW x1 AS SELECT * FROM t1_v2;
98 --error ER_GALERA_REPLICATION_NOT_SUPPORTED
99 CREATE VIEW x2 AS SELECT * FROM t2;
100 CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t1
101 AFTER INSERT ON t1_v2 FOR EACH ROW
102 UPDATE t1_v2 SET t1_v2.v2 = t1_v2.v2+1;
103 --error ER_GALERA_REPLICATION_NOT_SUPPORTED
104 CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t2
105 AFTER INSERT ON t2 FOR EACH ROW
106 UPDATE t2 SET t2.v1 = t2.v1+1;
107
108 --connection node_2
109 SHOW CREATE TABLE t1_v2;
110 SHOW CREATE TABLE t2;
111 SHOW CREATE VIEW x1;
112
113 SELECT * FROM t1_v2;
114 SELECT * FROM t2;
115
116 --connection node_1
117 DROP VIEW x1;
118 DROP TRIGGER increment_before_t1;
119 DROP TABLE t1_v2;
120 # We allow dropping table
121 DROP TABLE t2;
122 SET GLOBAL wsrep_mode = "";
123
124 CREATE OR REPLACE TABLE t2 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
125 PARTITION BY KEY (v1)
126 PARTITIONS 2;
127 --echo # wsrep-mode= STRICT_REPLICATION
128 SET GLOBAL wsrep_mode = "STRICT_REPLICATION";
129 SELECT @@wsrep_mode;
130 ALTER TABLE t2 ENGINE=InnoDB;
131 DROP TABLE t2;
132
133 --connection node_1
134 --echo #
135 --echo # MDEV-37373 InnoDB partition table disallow local GTIDs in galera
136 --echo # wsrep-mode= DISALLOW_LOCAL_GTID
137 --echo #
138 SET GLOBAL wsrep_mode = "DISALLOW_LOCAL_GTID";
139 SELECT @@wsrep_mode;
140 CREATE TABLE `sales` (
141 `customer_id` int(11) NOT NULL,
142 `customer_name` varchar(40) DEFAULT NULL,
143 `store_id` varchar(20) NOT NULL,
144 `bill_number` int(11) NOT NULL,
145 `bill_date` date NOT NULL,
146 `amount` decimal(8,2) NOT NULL,
147 PRIMARY KEY (`bill_date`)
148 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
149 PARTITION BY RANGE (year(`bill_date`))
150 (PARTITION `p0` VALUES LESS THAN (2016) ENGINE = InnoDB,
151 PARTITION `p1` VALUES LESS THAN (2017) ENGINE = InnoDB,
152 PARTITION `p2` VALUES LESS THAN (2018) ENGINE = InnoDB,
153 PARTITION `p3` VALUES LESS THAN (2020) ENGINE = InnoDB);
154
155 INSERT INTO sales
156 VALUES (1, 'Michael', 'S001', 101, '2015-01-02', 125.56),
157 (2, 'Jim', 'S003', 103, '2015-01-25', 476.50),
158 (3, 'Dwight', 'S012', 122, '2016-02-15', 335.00),
159 (4, 'Andy', 'S345', 121, '2016-03-26', 787.00),
160 (5, 'Pam', 'S234', 132, '2017-04-19', 678.00),
161 (6, 'Karen', 'S743', 111, '2017-05-31', 864.00),
162 (7, 'Toby', 'S234', 115, '2018-06-11', 762.00),
163 (8, 'Oscar', 'S012', 125, '2019-07-24', 300.00),
164 (9, 'Darryl', 'S456', 119, '2019-08-02', 492.20);
165
166 SELECT * FROM sales;
167 SET GLOBAL wsrep_mode=DEFAULT;
168 --connection node_2
169 SELECT * FROM sales;
170 DROP TABLE sales;