Debian Codegraph @

mysql-test/suite/parts/inc/part_exch_valid_key.inc

raw
1 use test;
2
3 call mtr.add_suppression("row in wrong partition.*from REBUILD/REORGANIZED");
4 #--disable_result_log
5 #--disable_query_log
6 --source suite/parts/inc/part_exch_ext_tabs.inc
7 #--enable_result_log
8 #--enable_query_log
9
10 --echo ######################################################################
11 --echo ### Key partitioned tables with/out validation
12
13 --error ER_ROW_DOES_NOT_MATCH_PARTITION
14 ALTER TABLE tp_k EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
15 --sorted_result
16 SELECT * FROM tp_k PARTITION (p0);
17 --sorted_result
18 SELECT * FROM tp_k PARTITION (p1);
19 --sorted_result
20 SELECT * FROM tp_k PARTITION (p2);
21
22 --error ER_ROW_DOES_NOT_MATCH_PARTITION
23 ALTER TABLE tp_k EXCHANGE PARTITION p0 WITH TABLE t_100 WITH VALIDATION;
24 ALTER TABLE tp_k EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION;
25 --sorted_result
26 SELECT * FROM tp_k PARTITION (p0);
27 --sorted_result
28 SELECT * FROM tp_k PARTITION (p1);
29 --sorted_result
30 SELECT * FROM tp_k PARTITION (p2);
31
32 # Bug #19075764 ALTER... REBUILD WITH MISPLACED ROWS IS CRASHING IN MYISAM.
33 #--error ER_ROW_IN_WRONG_PARTITION
34 #ALTER TABLE tp_k REBUILD PARTITION p0;
35 ALTER TABLE tp_k COALESCE PARTITION 1;
36 --sorted_result
37 SELECT * FROM tp_k PARTITION (p0);
38 --sorted_result
39 SELECT * FROM tp_k PARTITION (p1);
40 ALTER TABLE tp_k ADD PARTITION PARTITIONS 4;
41 ALTER TABLE tp_k EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
42 --sorted_result
43 SELECT * FROM tp_k PARTITION (p2);
44 ALTER TABLE tp_k ANALYZE PARTITION p0;
45 ALTER TABLE tp_k OPTIMIZE PARTITION p0;
46
47 --error ER_ROW_DOES_NOT_MATCH_PARTITION
48 ALTER TABLE tp_k EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
49 --sorted_result
50 SELECT * FROM tp_k PARTITION (p0);
51 --sorted_result
52 SELECT * FROM tp_k PARTITION (p1);
53 --sorted_result
54 SELECT * FROM tp_k PARTITION (p2);
55 CREATE TABLE t_11 LIKE tp_k;
56 ALTER TABLE t_11 REMOVE PARTITIONING;
57 INSERT INTO t_11 SELECT * FROM t_10;
58 INSERT INTO t_11 SELECT * FROM t_100;
59 ALTER TABLE tp_k EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
60 --sorted_result
61 SELECT * FROM tp_k PARTITION (p0);
62 DELETE FROM tp_k PARTITION (p1);
63 --sorted_result
64 SELECT * FROM tp_k PARTITION (p1);
65 ALTER TABLE tp_k CHECK PARTITION p0 ;
66 ALTER TABLE tp_k REPAIR PARTITION p0 ;
67 --sorted_result
68 SELECT * FROM tp_k PARTITION (p0);
69 --sorted_result
70 SELECT * FROM tp_k PARTITION (p1);
71 --sorted_result
72 SELECT * FROM tp_k PARTITION (p2);
73 --sorted_result
74 SELECT * FROM tp_k PARTITION (p3);
75 --sorted_result
76 SELECT * FROM tp_k PARTITION (p4);
77 --sorted_result
78 SELECT * FROM tp_k PARTITION (p5);
79 ALTER TABLE tp_k COALESCE PARTITION 2;
80 --sorted_result
81 SELECT * FROM tp_k PARTITION (p0);
82 --sorted_result
83 SELECT * FROM tp_k PARTITION (p1);
84 --sorted_result
85 SELECT * FROM tp_k PARTITION (p2);
86 --sorted_result
87 SELECT * FROM tp_k PARTITION (p3);
88 DROP TABLE IF EXISTS t_11;
89
90 # Values t_10 (not partitioned)
91 DELETE FROM t_10;
92 INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01');
93
94 # Values t_100 (not partitioned)
95 DELETE FROM t_100;
96 INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01');
97 INSERT INTO t_100 VALUES (91, "Ninety-one", '2091-01-01'), (93, "Ninety-three", '2093-01-01'), (95, "Ninety-five", '2095-01-01'), (99, "Ninety-nine", '2099-01-01');
98
99 # Bug #19075764 ALTER... REBUILD WITH MISPLACED ROWS IS CRASHING IN MYISAM.
100 #--error ER_ROW_IN_WRONG_PARTITION
101 #ALTER TABLE tp_kvar REBUILD PARTITION p0;
102 ALTER TABLE tp_kvar COALESCE PARTITION 1;
103 --sorted_result
104 SELECT * FROM tp_kvar PARTITION (p0);
105 --sorted_result
106 SELECT * FROM tp_kvar PARTITION (p1);
107 ALTER TABLE tp_kvar ADD PARTITION PARTITIONS 4;
108 --sorted_result
109 SELECT * FROM tp_kvar PARTITION (p2);
110 --sorted_result
111 SELECT * FROM tp_kvar PARTITION (p5);
112
113 CREATE TABLE t_11 LIKE tp_kvar;
114 ALTER TABLE t_11 REMOVE PARTITIONING;
115 INSERT INTO t_11 SELECT * FROM t_10;
116 INSERT INTO t_11 SELECT * FROM t_100;
117 ALTER TABLE tp_kvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
118 --sorted_result
119 SELECT * FROM tp_kvar PARTITION (p0);
120 DELETE FROM tp_kvar PARTITION (p1);
121 --sorted_result
122 SELECT * FROM tp_kvar PARTITION (p1);
123 ALTER TABLE tp_kvar ANALYZE PARTITION p0;
124 ALTER TABLE tp_kvar OPTIMIZE PARTITION p0;
125 ALTER TABLE tp_kvar CHECK PARTITION p0 ;
126 ALTER TABLE tp_kvar REPAIR PARTITION p0 ;
127 --sorted_result
128 SELECT * FROM tp_kvar PARTITION (p0);
129 --sorted_result
130 SELECT * FROM tp_kvar PARTITION (p1);
131 --sorted_result
132 SELECT * FROM tp_kvar PARTITION (p2);
133 ALTER TABLE tp_kvar COALESCE PARTITION 2;
134 --sorted_result
135 SELECT * FROM tp_kvar PARTITION (p0);
136 --sorted_result
137 SELECT * FROM tp_kvar PARTITION (p1);
138 --sorted_result
139 SELECT * FROM tp_kvar PARTITION (p2);
140 --sorted_result
141 SELECT * FROM tp_kvar PARTITION (p3);
142 DROP TABLE IF EXISTS t_11;
143
144 # Values t_10 (not partitioned)
145 DELETE FROM t_10;
146 INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01');
147
148 # Values t_100 (not partitioned)
149 DELETE FROM t_100;
150 INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01');
151 INSERT INTO t_100 VALUES (91, "Ninety-one", '2091-01-01'), (93, "Ninety-three", '2093-01-01'), (95, "Ninety-five", '2095-01-01'), (99, "Ninety-nine", '2099-01-01');
152
153 # Bug #19075764 ALTER... REBUILD WITH MISPLACED ROWS IS CRASHING IN MYISAM.
154 #--error ER_ROW_IN_WRONG_PARTITION
155 #ALTER TABLE tp_kdate REBUILD PARTITION p0;
156 ALTER TABLE tp_kdate COALESCE PARTITION 1;
157 --sorted_result
158 SELECT * FROM tp_kdate PARTITION (p0);
159 --sorted_result
160 SELECT * FROM tp_kdate PARTITION (p1);
161 ALTER TABLE tp_kdate ADD PARTITION PARTITIONS 4;
162 CREATE TABLE t_11 LIKE tp_kdate;
163 ALTER TABLE t_11 REMOVE PARTITIONING;
164 INSERT INTO t_11 SELECT * FROM t_10;
165 INSERT INTO t_11 SELECT * FROM t_100;
166 ALTER TABLE tp_kdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
167 --sorted_result
168 SELECT * FROM tp_kdate PARTITION (p0);
169 DELETE FROM tp_kdate PARTITION (p1);
170 --sorted_result
171 SELECT * FROM tp_kdate PARTITION (p1);
172 ALTER TABLE tp_kdate ANALYZE PARTITION p0;
173 ALTER TABLE tp_kdate OPTIMIZE PARTITION p0;
174 ALTER TABLE tp_kdate CHECK PARTITION p0 ;
175 ALTER TABLE tp_kdate REPAIR PARTITION p0 ;
176 --sorted_result
177 SELECT * FROM tp_kdate PARTITION (p0);
178 --sorted_result
179 SELECT * FROM tp_kdate PARTITION (p1);
180 --sorted_result
181 SELECT * FROM tp_kdate PARTITION (p2);
182 --sorted_result
183 SELECT * FROM tp_kdate PARTITION (p3);
184 --sorted_result
185 SELECT * FROM tp_kdate PARTITION (p4);
186 --sorted_result
187 SELECT * FROM tp_kdate PARTITION (p5);
188 ALTER TABLE tp_kdate COALESCE PARTITION 2;
189 --sorted_result
190 SELECT * FROM tp_kdate PARTITION (p0);
191 --sorted_result
192 SELECT * FROM tp_kdate PARTITION (p1);
193 --sorted_result
194 SELECT * FROM tp_kdate PARTITION (p2);
195 --sorted_result
196 SELECT * FROM tp_kdate PARTITION (p3);
197 DROP TABLE IF EXISTS t_11;
198
199 --source suite/parts/inc/part_exch_drop_ext_tabs.inc
200