mirror of
https://github.com/MariaDB/server.git
synced 2025-09-29 20:39:17 +02:00
f9bdff6162
Problem was that for partitioned tables base table storage engine is DB_TYPE_PARTITION_DB and naturally different than DB_TYPE_INNODB so operation was not allowed in Galera. Fixed by requesting implementing storage engine for partitioned tables i.e. table->file->partition_ht() or if that does not exist we can use base table storage engine. Resulting storage engine type is then used on condition is operation allowed when wsrep_mode=DISALLOW_LOCAL_GTID or not. Operations to InnoDB storage engine i.e DB_TYPE_INNODB should be allowed.
229 lines
7.8 KiB
Text
229 lines
7.8 KiB
Text
connection node_2;
|
|
connection node_1;
|
|
call mtr.add_suppression("WSREP: wsrep_mode = STRICT_REPLICATION enabled\\. Storage engine partition for table");
|
|
# wsrep-mode= DEFAULT
|
|
SET GLOBAL wsrep_mode = "";
|
|
SELECT @@wsrep_mode;
|
|
@@wsrep_mode
|
|
|
|
CREATE OR REPLACE TABLE t1 (v1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB
|
|
PARTITION BY KEY (v1)
|
|
PARTITIONS 2;
|
|
CREATE OR REPLACE TABLE t2 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
|
|
PARTITION BY KEY (v1)
|
|
PARTITIONS 2;
|
|
ALTER TABLE t1 ADD COLUMN v2 int;
|
|
ALTER TABLE t2 ADD COLUMN v2 int;
|
|
INSERT INTO t1 VALUES (1,1),(2,2);
|
|
INSERT INTO t2 VALUES (1,1),(2,2);
|
|
ALTER TABLE t1 ADD COLUMN v3 int, ENGINE=MyISAM;
|
|
ALTER TABLE t2 ADD COLUMN v3 int, ENGINE=Aria;
|
|
UPDATE t1 SET v3 = 3;
|
|
UPDATE t2 SET v3 = 3;
|
|
CREATE INDEX xx1 ON t1(v2);
|
|
CREATE INDEX xx2 ON t2(v2);
|
|
DROP INDEX xx1 ON t1;
|
|
DROP INDEX xx2 ON t2;
|
|
TRUNCATE TABLE t1;
|
|
TRUNCATE TABLE t2;
|
|
RENAME TABLE t1 TO t1_v2;
|
|
RENAME TABLE t2 TO t2_v2;
|
|
CREATE VIEW x1 AS SELECT * FROM t1_v2;
|
|
CREATE VIEW x2 AS SELECT * FROM t2_v2;
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t1
|
|
AFTER INSERT ON t1_v2 FOR EACH ROW
|
|
UPDATE t1_v2 SET t1_v2.v3 = t1_v2.v3+1;
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t2
|
|
AFTER INSERT ON t2_v2 FOR EACH ROW
|
|
UPDATE t2_v2 SET t2_v2.v3 = t2_v2.v3+1;
|
|
connection node_2;
|
|
SHOW CREATE TABLE t1_v2;
|
|
Table Create Table
|
|
t1_v2 CREATE TABLE `t1_v2` (
|
|
`v1` int(11) NOT NULL,
|
|
`v2` int(11) DEFAULT NULL,
|
|
`v3` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`v1`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY KEY (`v1`)
|
|
PARTITIONS 2
|
|
SHOW CREATE TABLE t2_v2;
|
|
Table Create Table
|
|
t2_v2 CREATE TABLE `t2_v2` (
|
|
`v1` int(11) NOT NULL,
|
|
`v2` int(11) DEFAULT NULL,
|
|
`v3` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`v1`)
|
|
) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY KEY (`v1`)
|
|
PARTITIONS 2
|
|
SHOW CREATE VIEW x1;
|
|
View Create View character_set_client collation_connection
|
|
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
|
|
SHOW CREATE VIEW x2;
|
|
View Create View character_set_client collation_connection
|
|
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
|
|
SELECT * FROM t1_v2;
|
|
v1 v2 v3
|
|
SELECT * FROM t2_v2;
|
|
v1 v2 v3
|
|
connection node_1;
|
|
DROP VIEW x1;
|
|
DROP VIEW x2;
|
|
DROP TRIGGER increment_before_t1;
|
|
DROP TRIGGER increment_before_t2;
|
|
DROP TABLE t1_v2;
|
|
DROP TABLE t2_v2;
|
|
SET GLOBAL wsrep_mode = "";
|
|
CREATE OR REPLACE TABLE t2 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
|
|
PARTITION BY KEY (v1)
|
|
PARTITIONS 2;
|
|
# wsrep-mode= STRICT_REPLICATION
|
|
SET GLOBAL wsrep_mode = "STRICT_REPLICATION";
|
|
SELECT @@wsrep_mode;
|
|
@@wsrep_mode
|
|
STRICT_REPLICATION
|
|
CREATE OR REPLACE TABLE t1 (v1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB
|
|
PARTITION BY KEY (v1)
|
|
PARTITIONS 2;
|
|
CREATE OR REPLACE TABLE t3 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
|
|
PARTITION BY KEY (v1)
|
|
PARTITIONS 2;
|
|
ERROR HY000: Galera replication not supported
|
|
ALTER TABLE t1 ADD COLUMN v2 int;
|
|
ALTER TABLE t2 ADD COLUMN v2 int;
|
|
ERROR HY000: Galera replication not supported
|
|
INSERT INTO t1 VALUES (1,1),(2,2);
|
|
INSERT INTO t2 VALUES (1),(2);
|
|
Warnings:
|
|
Warning 1290 WSREP: wsrep_mode = STRICT_REPLICATION enabled. Storage engine partition for table 'test'.'t2' is not supported in Galera
|
|
ALTER TABLE t1 ADD COLUMN v3 int, ENGINE=MyISAM;
|
|
ERROR HY000: Galera replication not supported
|
|
ALTER TABLE t2 ADD COLUMN v3 int, ENGINE=Aria;
|
|
ERROR HY000: Galera replication not supported
|
|
UPDATE t1 SET v2 = v2 + 3;
|
|
UPDATE t2 SET v1 = v1 + 3;
|
|
Warnings:
|
|
Warning 1290 WSREP: wsrep_mode = STRICT_REPLICATION enabled. Storage engine partition for table 'test'.'t2' is not supported in Galera
|
|
CREATE INDEX xx1 ON t1(v2);
|
|
CREATE INDEX xx2 ON t2(v2);
|
|
ERROR HY000: Galera replication not supported
|
|
DROP INDEX xx1 ON t1;
|
|
DROP INDEX xx2 on t2;
|
|
ERROR HY000: Galera replication not supported
|
|
TRUNCATE TABLE t1;
|
|
TRUNCATE TABLE t2;
|
|
ERROR HY000: Galera replication not supported
|
|
RENAME TABLE t1 TO t1_v2;
|
|
RENAME TABLE t2 TO t2_v2;
|
|
RENAME TABLE t2_v2 TO t2;
|
|
CREATE VIEW x1 AS SELECT * FROM t1_v2;
|
|
CREATE VIEW x2 AS SELECT * FROM t2;
|
|
ERROR HY000: Galera replication not supported
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t1
|
|
AFTER INSERT ON t1_v2 FOR EACH ROW
|
|
UPDATE t1_v2 SET t1_v2.v2 = t1_v2.v2+1;
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER increment_before_t2
|
|
AFTER INSERT ON t2 FOR EACH ROW
|
|
UPDATE t2 SET t2.v1 = t2.v1+1;
|
|
ERROR HY000: Galera replication not supported
|
|
connection node_2;
|
|
SHOW CREATE TABLE t1_v2;
|
|
Table Create Table
|
|
t1_v2 CREATE TABLE `t1_v2` (
|
|
`v1` int(11) NOT NULL,
|
|
`v2` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`v1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY KEY (`v1`)
|
|
PARTITIONS 2
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`v1` int(11) NOT NULL,
|
|
`v2` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`v1`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY KEY (`v1`)
|
|
PARTITIONS 2
|
|
SHOW CREATE VIEW x1;
|
|
View Create View character_set_client collation_connection
|
|
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
|
|
SELECT * FROM t1_v2;
|
|
v1 v2
|
|
SELECT * FROM t2;
|
|
v1 v2
|
|
connection node_1;
|
|
DROP VIEW x1;
|
|
DROP TRIGGER increment_before_t1;
|
|
DROP TABLE t1_v2;
|
|
DROP TABLE t2;
|
|
SET GLOBAL wsrep_mode = "";
|
|
CREATE OR REPLACE TABLE t2 (v1 INT NOT NULL PRIMARY KEY) ENGINE=MyISAM
|
|
PARTITION BY KEY (v1)
|
|
PARTITIONS 2;
|
|
# wsrep-mode= STRICT_REPLICATION
|
|
SET GLOBAL wsrep_mode = "STRICT_REPLICATION";
|
|
SELECT @@wsrep_mode;
|
|
@@wsrep_mode
|
|
STRICT_REPLICATION
|
|
ALTER TABLE t2 ENGINE=InnoDB;
|
|
DROP TABLE t2;
|
|
connection node_1;
|
|
#
|
|
# MDEV-37373 InnoDB partition table disallow local GTIDs in galera
|
|
# wsrep-mode= DISALLOW_LOCAL_GTID
|
|
#
|
|
SET GLOBAL wsrep_mode = "DISALLOW_LOCAL_GTID";
|
|
SELECT @@wsrep_mode;
|
|
@@wsrep_mode
|
|
DISALLOW_LOCAL_GTID
|
|
CREATE TABLE `sales` (
|
|
`customer_id` int(11) NOT NULL,
|
|
`customer_name` varchar(40) DEFAULT NULL,
|
|
`store_id` varchar(20) NOT NULL,
|
|
`bill_number` int(11) NOT NULL,
|
|
`bill_date` date NOT NULL,
|
|
`amount` decimal(8,2) NOT NULL,
|
|
PRIMARY KEY (`bill_date`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY RANGE (year(`bill_date`))
|
|
(PARTITION `p0` VALUES LESS THAN (2016) ENGINE = InnoDB,
|
|
PARTITION `p1` VALUES LESS THAN (2017) ENGINE = InnoDB,
|
|
PARTITION `p2` VALUES LESS THAN (2018) ENGINE = InnoDB,
|
|
PARTITION `p3` VALUES LESS THAN (2020) ENGINE = InnoDB);
|
|
INSERT INTO sales
|
|
VALUES (1, 'Michael', 'S001', 101, '2015-01-02', 125.56),
|
|
(2, 'Jim', 'S003', 103, '2015-01-25', 476.50),
|
|
(3, 'Dwight', 'S012', 122, '2016-02-15', 335.00),
|
|
(4, 'Andy', 'S345', 121, '2016-03-26', 787.00),
|
|
(5, 'Pam', 'S234', 132, '2017-04-19', 678.00),
|
|
(6, 'Karen', 'S743', 111, '2017-05-31', 864.00),
|
|
(7, 'Toby', 'S234', 115, '2018-06-11', 762.00),
|
|
(8, 'Oscar', 'S012', 125, '2019-07-24', 300.00),
|
|
(9, 'Darryl', 'S456', 119, '2019-08-02', 492.20);
|
|
SELECT * FROM sales;
|
|
customer_id customer_name store_id bill_number bill_date amount
|
|
1 Michael S001 101 2015-01-02 125.56
|
|
2 Jim S003 103 2015-01-25 476.50
|
|
3 Dwight S012 122 2016-02-15 335.00
|
|
4 Andy S345 121 2016-03-26 787.00
|
|
5 Pam S234 132 2017-04-19 678.00
|
|
6 Karen S743 111 2017-05-31 864.00
|
|
7 Toby S234 115 2018-06-11 762.00
|
|
8 Oscar S012 125 2019-07-24 300.00
|
|
9 Darryl S456 119 2019-08-02 492.20
|
|
SET GLOBAL wsrep_mode=DEFAULT;
|
|
connection node_2;
|
|
SELECT * FROM sales;
|
|
customer_id customer_name store_id bill_number bill_date amount
|
|
1 Michael S001 101 2015-01-02 125.56
|
|
2 Jim S003 103 2015-01-25 476.50
|
|
3 Dwight S012 122 2016-02-15 335.00
|
|
4 Andy S345 121 2016-03-26 787.00
|
|
5 Pam S234 132 2017-04-19 678.00
|
|
6 Karen S743 111 2017-05-31 864.00
|
|
7 Toby S234 115 2018-06-11 762.00
|
|
8 Oscar S012 125 2019-07-24 300.00
|
|
9 Darryl S456 119 2019-08-02 492.20
|
|
DROP TABLE sales;
|