1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
#
# MDEV#4953 Galera: DELETE from a partitioned table is not replicated
#
USE test;
CREATE TABLE t1 (pk INT PRIMARY KEY, i INT) ENGINE=INNODB PARTITION BY HASH(pk) PARTITIONS 2;
INSERT INTO t1 VALUES (1,100), (2,200);
SELECT * FROM t1;
pk i
2 200
1 100
DELETE FROM t1;
SELECT * FROM t1;
pk i
# On node_1
SELECT * FROM t1;
pk i
# On node_2
SELECT * FROM t1;
pk i
DROP TABLE t1;
#
# MDEV#7501 : alter table exchange partition is not replicated in
# galera cluster
#
# On node_1
CREATE TABLE test.t1 (
i INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (i)
) ENGINE=INNODB
PARTITION BY RANGE (i)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = INNODB,
PARTITION p2 VALUES LESS THAN (20) ENGINE = INNODB,
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = INNODB);
INSERT INTO test.t1 (i) VALUE (9),(19);
CREATE TABLE test.p1 LIKE test.t1;
ALTER TABLE test.p1 REMOVE PARTITIONING;
ALTER TABLE test.t1 EXCHANGE PARTITION p1 WITH TABLE test.p1;
SELECT * FROM test.t1;
i
19
SELECT * FROM test.p1;
i
9
# On node_2
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (i)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
SHOW CREATE TABLE p1;
Table Create Table
p1 CREATE TABLE `p1` (
`i` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
SELECT * FROM test.t1;
i
19
SELECT * FROM test.p1;
i
9
# On node_1
ALTER TABLE t1 TRUNCATE PARTITION p2;
SELECT * FROM test.t1;
i
# On node_2
SELECT * FROM test.t1;
i
# On node_1
ALTER TABLE t1 DROP PARTITION p2;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (i)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
# On node_2
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (i)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
DROP TABLE t1, p1;
#
# MDEV-5146: Bulk loads into partitioned table not working
#
# Case 1: wsrep_load_data_splitting = ON & LOAD DATA with 20002
# entries.
SET GLOBAL wsrep_load_data_splitting = ON;
CREATE TABLE t1 (pk INT PRIMARY KEY)
ENGINE=INNODB PARTITION BY HASH(pk) PARTITIONS 2;
SELECT COUNT(*) = 20002 FROM t1;
COUNT(*) = 20002
1
wsrep_last_committed_diff
1
DROP TABLE t1;
# Case 2: wsrep_load_data_splitting = ON & LOAD DATA with 101 entries.
SET GLOBAL wsrep_load_data_splitting = ON;
CREATE TABLE t1 (pk INT PRIMARY KEY)
ENGINE=INNODB PARTITION BY HASH(pk) PARTITIONS 2;
SELECT COUNT(*) = 101 FROM t1;
COUNT(*) = 101
1
wsrep_last_committed_diff
1
DROP TABLE t1;
# Case 3: wsrep_load_data_splitting = OFF & LOAD DATA with 20002
# entries.
SET GLOBAL wsrep_load_data_splitting = OFF;
CREATE TABLE t1 (pk INT PRIMARY KEY)
ENGINE=INNODB PARTITION BY HASH(pk) PARTITIONS 2;
SELECT COUNT(*) = 20002 FROM t1;
COUNT(*) = 20002
1
wsrep_last_committed_diff
1
DROP TABLE t1;
SET GLOBAL wsrep_load_data_splitting = 1;;
# End of test
|
