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
141
142
|
connection node_2;
connection node_1;
#
# 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
connection node_1;
SELECT * FROM t1;
pk i
# On node_2
connection 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
connection 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
connection 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT * FROM test.t1;
i
19
SELECT * FROM test.p1;
i
9
# On node_1
connection node_1;
ALTER TABLE t1 TRUNCATE PARTITION p2;
SELECT * FROM test.t1;
i
# On node_2
connection node_2;
SELECT * FROM test.t1;
i
# On node_1
connection 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE (`i`)
(PARTITION `p1` VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION `pMax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
# On node_2
connection 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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
#
connection node_1;
# Case 1: LOAD DATA with 20002 entries.
CREATE TABLE t1 (pk INT PRIMARY KEY)
ENGINE=INNODB PARTITION BY HASH(pk) PARTITIONS 2;
connection node_2;
SELECT COUNT(*) FROM t1;
COUNT(*)
20002
wsrep_last_committed_diff
AS_EXPECTED_1_or_2
DROP TABLE t1;
# Case 2: LOAD DATA with 101 entries.
connection node_1;
CREATE TABLE t1 (pk INT PRIMARY KEY)
ENGINE=INNODB PARTITION BY HASH(pk) PARTITIONS 2;
connection node_2;
SELECT COUNT(*) FROM t1;
COUNT(*)
101
wsrep_last_committed_diff
1
DROP TABLE t1;
connection node_1;
disconnect node_2;
disconnect node_1;
# End of test
|
