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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
|
#
# Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT
# Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE
#
CREATE TABLE t1 (a INT, b VARCHAR(64), KEY(b,a))
PARTITION BY HASH (a) PARTITIONS 3;
INSERT INTO t1 VALUES (1, "1"), (2, "2"), (3, "3"), (4, "Four"), (5, "Five"),
(6, "Six"), (7, "Seven"), (8, "Eight"), (9, "Nine");
SET SESSION debug="+d,partition_fail_index_init";
SELECT * FROM t1 WHERE b = "Seven";
ERROR HY000: Table has no partition for value 0
SET SESSION debug="-d,partition_fail_index_init";
SELECT * FROM t1 WHERE b = "Seven";
a b
7 Seven
DROP TABLE t1;
#
# Bug 29706669 ALTER EXCHANGE CAUSES INDEX CORRUPTION
#
CREATE TABLE t1_part_table ( f1 INT PRIMARY KEY,
f2 iNT , f3 INT,
KEY `IDX_F2` (f2),
KEY `IDX_F3` (f3)
) ENGINE=INNODB
PARTITION BY RANGE (f1) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20));
CREATE TABLE t1_normal_table ( f1 INT PRIMARY KEY,
f2 iNT , f3 INT,
KEY `IDX_F3` (f3),
KEY `IDX_F2` (f2)
) ENGINE=INNODB;
INSERT INTO t1_part_table VALUES (5,10,20),(6,30,40),(15,50,60),(17,70,80);
INSERT INTO t1_normal_table VALUES (19,90,100),(18,110,120);
SET SESSION debug='+d,skip_dd_table_access_check';
SELECT t.NAME AS TABLE_NAME , i.NAME AS INDEX_NAME , i.ORDINAL_POSITION
FROM mysql.indexes i JOIN mysql.tables t ON i.TABLE_ID = t.ID
WHERE t.NAME='t1_normal_table' ORDER BY i.ORDINAL_POSITION;
TABLE_NAME INDEX_NAME ORDINAL_POSITION
t1_normal_table PRIMARY 1
t1_normal_table IDX_F3 2
t1_normal_table IDX_F2 3
SHOW CREATE TABLE t1_normal_table;
Table Create Table
t1_normal_table CREATE TABLE `t1_normal_table` (
`f1` int NOT NULL,
`f2` int DEFAULT NULL,
`f3` int DEFAULT NULL,
PRIMARY KEY (`f1`),
KEY `IDX_F3` (`f3`),
KEY `IDX_F2` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT t.NAME AS TABLE_NAME, p.NAME AS PARTITION_NAME , i.NAME AS INDEX_NAME, i.ORDINAL_POSITION
FROM mysql.tables t JOIN mysql.table_partitions p ON t.ID = p.TABLE_ID
JOIN mysql.index_partitions ip ON p.ID = ip.PARTITION_ID
JOIN mysql.indexes i ON i.ID = ip.INDEX_ID
WHERE t.NAME= 't1_part_table' ORDER BY i.ORDINAL_POSITION,p.NAME;
TABLE_NAME PARTITION_NAME INDEX_NAME ORDINAL_POSITION
t1_part_table p0 PRIMARY 1
t1_part_table p1 PRIMARY 1
t1_part_table p0 IDX_F2 2
t1_part_table p1 IDX_F2 2
t1_part_table p0 IDX_F3 3
t1_part_table p1 IDX_F3 3
SHOW CREATE TABLE t1_part_table;
Table Create Table
t1_part_table CREATE TABLE `t1_part_table` (
`f1` int NOT NULL,
`f2` int DEFAULT NULL,
`f3` int DEFAULT NULL,
PRIMARY KEY (`f1`),
KEY `IDX_F2` (`f2`),
KEY `IDX_F3` (`f3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`f1`)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB) */
ALTER TABLE t1_part_table EXCHANGE PARTITION p1 WITH TABLE t1_normal_table;
SELECT t.NAME AS TABLE_NAME , i.NAME AS INDEX_NAME , i.ORDINAL_POSITION
FROM mysql.indexes i JOIN mysql.tables t ON i.TABLE_ID = t.ID
WHERE t.NAME='t1_normal_table' ORDER BY i.ORDINAL_POSITION;
TABLE_NAME INDEX_NAME ORDINAL_POSITION
t1_normal_table PRIMARY 1
t1_normal_table IDX_F3 2
t1_normal_table IDX_F2 3
SHOW CREATE TABLE t1_normal_table;
Table Create Table
t1_normal_table CREATE TABLE `t1_normal_table` (
`f1` int NOT NULL,
`f2` int DEFAULT NULL,
`f3` int DEFAULT NULL,
PRIMARY KEY (`f1`),
KEY `IDX_F3` (`f3`),
KEY `IDX_F2` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT t.NAME AS TABLE_NAME, p.NAME AS PARTITION_NAME , i.NAME AS INDEX_NAME, i.ORDINAL_POSITION
FROM mysql.tables t JOIN mysql.table_partitions p ON t.ID = p.TABLE_ID
JOIN mysql.index_partitions ip ON p.ID = ip.PARTITION_ID
JOIN mysql.indexes i ON i.ID = ip.INDEX_ID
WHERE t.NAME= 't1_part_table' ORDER BY i.ORDINAL_POSITION,p.NAME;
TABLE_NAME PARTITION_NAME INDEX_NAME ORDINAL_POSITION
t1_part_table p0 PRIMARY 1
t1_part_table p1 PRIMARY 1
t1_part_table p0 IDX_F2 2
t1_part_table p1 IDX_F2 2
t1_part_table p0 IDX_F3 3
t1_part_table p1 IDX_F3 3
SHOW CREATE TABLE t1_part_table;
Table Create Table
t1_part_table CREATE TABLE `t1_part_table` (
`f1` int NOT NULL,
`f2` int DEFAULT NULL,
`f3` int DEFAULT NULL,
PRIMARY KEY (`f1`),
KEY `IDX_F2` (`f2`),
KEY `IDX_F3` (`f3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`f1`)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB) */
SET SESSION debug='-d,skip_dd_table_access_check';
CHECK TABLE t1_part_table;
Table Op Msg_type Msg_text
test.t1_part_table check status OK
CHECK TABLE t1_normal_table;
Table Op Msg_type Msg_text
test.t1_normal_table check status OK
SELECT * FROM t1_part_table;
f1 f2 f3
5 10 20
6 30 40
18 110 120
19 90 100
SELECT * FROM t1_normal_table;
f1 f2 f3
15 50 60
17 70 80
DROP TABLE t1_part_table;
DROP TABLE t1_normal_table;
#
# Bug #30437407 ASSERTION ".NUM_ROWS == .~.HA_ROWS"
#
CREATE TABLE t1(a int) PARTITION BY LIST (a) (PARTITION x1 VALUES IN
(2),PARTITION x2 VALUES IN (3));
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*)FROM t1;
ERROR HY000: Tablespace has been discarded for table 'test/t1#p#x1'
DROP TABLE t1;
#
# Bug #31941543 EXCHANGE PARTITION ASSERT
#
CREATE TABLE t1 (
LogTime TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00',
UserAgent VARCHAR(256) COLLATE utf8mb4_bin DEFAULT NULL,
KEY IX_LogTime (LogTime)
) ENGINE=INNODB
PARTITION BY RANGE(UNIX_TIMESTAMP(LogTime))
(
PARTITION p201407 VALUES LESS THAN (UNIX_TIMESTAMP('2014-08-01')),
PARTITION p201412 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01')),
PARTITION p201501 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01')),
PARTITION p201502 VALUES LESS THAN (UNIX_TIMESTAMP('2015-03-01')),
PARTITION p201503 VALUES LESS THAN (UNIX_TIMESTAMP('2015-04-01')),
PARTITION future VALUES LESS THAN MAXVALUE
);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`LogTime` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
`UserAgent` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
KEY `IX_LogTime` (`LogTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (unix_timestamp(`LogTime`))
(PARTITION p201407 VALUES LESS THAN (1406840400) ENGINE = InnoDB,
PARTITION p201412 VALUES LESS THAN (1420059600) ENGINE = InnoDB,
PARTITION p201501 VALUES LESS THAN (1422738000) ENGINE = InnoDB,
PARTITION p201502 VALUES LESS THAN (1425157200) ENGINE = InnoDB,
PARTITION p201503 VALUES LESS THAN (1427835600) ENGINE = InnoDB,
PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
CREATE TABLE t2 (
LogTime TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00',
UserAgent VARCHAR(256) COLLATE utf8mb4_bin DEFAULT NULL,
KEY IX_LogTime (LogTime)
) ENGINE=INNODB;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`LogTime` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
`UserAgent` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
KEY `IX_LogTime` (`LogTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t2 VALUES('2014-08-02 00:00:00','useragent');
SELECT * FROM t2;
LogTime UserAgent
2014-08-02 00:00:00 useragent
SELECT * FROM t1;
LogTime UserAgent
ALTER TABLE t1 EXCHANGE PARTITION p201412 WITH TABLE t2;
SELECT * FROM t2;
LogTime UserAgent
SELECT * FROM t1;
LogTime UserAgent
2014-08-02 00:00:00 useragent
DROP TABLE t1,t2;
|
