Debian Codegraph @

mysql-test/suite/rpl/t/rpl_partition_id_commands.test

raw
1 #This script will test the following partitioning commands
2
3 # PARTITION BY
4 # ADD PARTITION
5 # DROP PARTITION
6 # TRUNCATE PARTITION
7 # REORGANIZE PARTITION
8 # EXCHANGE PARTITION
9 # ANALYZE PARTITION
10 # CHECK PARTITION
11 # OPTIMIZE PARTITION
12 # REBUILD PARTITION
13 # REPAIR PARTITION
14 # REMOVE PARTITIONING
15 # COALESCE PARTITION
16
17 # References
18 # WL#12168: Add Partition Information into the Binary Log
19 #
20
21
22 --source include/have_binlog_format_row.inc
23
24 --let $rpl_skip_start_slave=1
25 --let $rpl_skip_change_master=1
26
27 --source include/master-slave.inc
28 RESET MASTER;
29 #--let $rpl_connection_name= server_1
30 #--source include/rpl_connection.inc
31
32 # This table will be used to test all the above mentioned thing except COALESCE PARTITION, that will be tested at the end of this test
33 # with PARTITION BY HASH
34
35 CREATE TABLE employees (id INT NOT NULL, store_id INT NOT NULL);
36
37 --echo Test PARTITION BY
38 ALTER TABLE employees PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16));
39
40 INSERT INTO employees VALUES (1, 3);
41 INSERT INTO employees VALUES (2, 7);
42 INSERT INTO employees VALUES (3, 12);
43
44 --echo Test ADD PARTITION
45 # The number of partitions before adding the new partition
46 let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS`;
47 ALTER TABLE employees ADD PARTITION (PARTITION p3 VALUES LESS THAN (20));
48 INSERT INTO employees VALUES (4, 19);
49 let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS`;
50 --let $assert_text= Check that the partition count is increased after ADD PARTITION
51 --let $assert_cond= $before_count < $after_count
52 --source include/assert.inc
53
54 --echo Test DROP PARTITION
55 let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
56 ALTER TABLE employees DROP PARTITION p3;
57 let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
58 --let $assert_text= Check that the partition count is decreased after ADD PARTITION
59 --let $assert_cond= $before_count > $after_count
60 --source include/assert.inc
61
62 --echo Test TRUNCATE PARTITION
63 # Increase the row count of 1st partition 'p0'
64 INSERT INTO employees VALUES (2, 4);
65 ALTER TABLE employees TRUNCATE PARTITION P0;
66 --let $row_count_after = `SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees' AND PARTITION_NAME= 'p0'`
67 --let $assert_text= Check that the row count after is zero
68 --let $assert_cond= $row_count_after = 0
69 --source include/assert.inc
70
71 --echo Test REORGANIZE PARTITION
72 let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
73 ALTER TABLE employees REORGANIZE PARTITION p1,p2 INTO (PARTITION p3 VALUES LESS THAN (20));
74 let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
75
76 --let $assert_text= Check that the partition count is decreased after REORGANIZE PARTITION
77 --let $assert_cond= $before_count > $after_count
78 --source include/assert.inc
79
80 --echo Test EXCHANGE PARTITION
81 CREATE TABLE employees1 LIKE employees;
82 ALTER TABLE employees1 REMOVE PARTITIONING;
83 let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
84 ALTER TABLE employees EXCHANGE PARTITION p3 WITH TABLE employees1;
85 let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
86
87 --let $assert_text= Check that the partition count is same after EXCHANGE PARTITION
88 --let $assert_cond= $before_count = $after_count
89 --source include/assert.inc
90
91 DROP TABLE employees1;
92
93 --echo Test ANALYZE PARTITION
94 ALTER TABLE employees ANALYZE PARTITION p0;
95 ALTER TABLE employees ANALYZE PARTITION ALL;
96
97 --echo Test CHECK PARTITION
98
99 ALTER TABLE employees CHECK PARTITION p0;
100 ALTER TABLE employees CHECK PARTITION ALL;
101
102 --echo Test OPTIMIZE PARTITION
103
104 ALTER TABLE employees OPTIMIZE PARTITION p3;
105 ALTER TABLE employees OPTIMIZE PARTITION ALL;
106
107 --echo Test REBUILD PARTITION
108
109 ALTER TABLE employees REBUILD PARTITION p3;
110 ALTER TABLE employees REBUILD PARTITION ALL;
111
112 --echo Test REPAIR PARTITION
113
114 # Just checking here at once for the count of partition_ordinal_position should
115 # be okay and we don't need to check it individually for all commands like
116 # CHECK, ANALYZE, OPTIMIZE, REBUILD.
117 let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
118 ALTER TABLE employees REPAIR PARTITION p4;
119 ALTER TABLE employees REPAIR PARTITION ALL;
120 let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
121
122 --let $assert_text= Check that the partition count is same after REPAIR PARTITION
123 --let $assert_cond= $before_count = $after_count
124 --source include/assert.inc
125
126 --echo Test REMOVE PARTITION
127 ALTER TABLE employees REMOVE PARTITIONING;
128 let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
129
130 --let $assert_text= Check that the partition count is 0 after REMOVE PARTITIONING
131 --let $assert_cond= $after_count = 0
132 --source include/assert.inc
133
134 DROP TABLE employees;
135
136 --echo Test DISCARD/IMPORT PARTITION
137
138 # Create table and partitions on server_1 as we have removed it in the step above
139 # while/after testing REMOVE PARTITION
140
141 CREATE TABLE employees (id INT NOT NULL, store_id INT NOT NULL);
142 ALTER TABLE employees PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16));
143 --let $SERVER1_MYSQLD_DATADIR= `SELECT @@DATADIR`
144 --let $rpl_connection_name= server_2
145 --source include/rpl_connection.inc
146
147 CREATE TABLE employees (id INT NOT NULL, store_id INT NOT NULL);
148 --let $SERVER2_MYSQLD_DATADIR= `SELECT @@DATADIR`
149 ALTER TABLE employees PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16));
150 ALTER TABLE employees DISCARD PARTITION p0 TABLESPACE;
151
152 --let $rpl_connection_name= server_1
153 --source include/rpl_connection.inc
154
155 FLUSH TABLES employees FOR EXPORT;
156 --let $rpl_connection_name= server_2
157 --source include/rpl_connection.inc
158 --copy_file $SERVER1_MYSQLD_DATADIR/test/employees#p#p0.cfg $SERVER2_MYSQLD_DATADIR/test/employees#p#p0.cfg
159 --copy_file $SERVER1_MYSQLD_DATADIR/test/employees#p#p0.ibd $SERVER2_MYSQLD_DATADIR/test/employees#p#p0.ibd
160
161 --let $rpl_connection_name= server_1
162 --source include/rpl_connection.inc
163
164 UNLOCK TABLES;
165
166 --let $rpl_connection_name= server_2
167 --source include/rpl_connection.inc
168 ALTER TABLE employees IMPORT PARTITION p0 TABLESPACE;
169
170 DROP TABLE employees;
171
172 --let $rpl_connection_name= server_1
173 --source include/rpl_connection.inc
174 DROP TABLE employees;
175
176 # The below table and partition type will be used for testing the following
177 # PARTITION BY
178 # COALESCE PARTITION
179
180 CREATE TABLE employees (id INT NOT NULL, store_id INT NOT NULL);
181
182 --echo Test PARTITION BY
183 ALTER TABLE employees PARTITION BY HASH(store_id) PARTITIONS 6;
184
185 --echo Test COALESCE PARTITION
186
187 let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
188 ALTER TABLE employees COALESCE PARTITION 2;
189 let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
190
191 --let $assert_text= Check that the before count is greater than after count after COALESCE PARTITION
192 --let $assert_cond= $after_count < $before_count
193 --source include/assert.inc
194
195
196 DROP TABLE employees;
197 --let $rpl_only_running_threads= 1
198 --let $rpl_skip_sync= 1
199 --source include/rpl_end.inc