200 lines
7.7 KiB
Plaintext
200 lines
7.7 KiB
Plaintext
#This script will test the following partitioning commands
|
|
|
|
# PARTITION BY
|
|
# ADD PARTITION
|
|
# DROP PARTITION
|
|
# TRUNCATE PARTITION
|
|
# REORGANIZE PARTITION
|
|
# EXCHANGE PARTITION
|
|
# ANALYZE PARTITION
|
|
# CHECK PARTITION
|
|
# OPTIMIZE PARTITION
|
|
# REBUILD PARTITION
|
|
# REPAIR PARTITION
|
|
# REMOVE PARTITIONING
|
|
# COALESCE PARTITION
|
|
|
|
# References
|
|
# WL#12168: Add Partition Information into the Binary Log
|
|
#
|
|
|
|
|
|
--source include/have_binlog_format_row.inc
|
|
|
|
--let $rpl_skip_start_slave=1
|
|
--let $rpl_skip_change_master=1
|
|
|
|
--source include/master-slave.inc
|
|
RESET MASTER;
|
|
#--let $rpl_connection_name= server_1
|
|
#--source include/rpl_connection.inc
|
|
|
|
# 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
|
|
# with PARTITION BY HASH
|
|
|
|
CREATE TABLE employees (id INT NOT NULL, store_id INT NOT NULL);
|
|
|
|
--echo Test PARTITION BY
|
|
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));
|
|
|
|
INSERT INTO employees VALUES (1, 3);
|
|
INSERT INTO employees VALUES (2, 7);
|
|
INSERT INTO employees VALUES (3, 12);
|
|
|
|
--echo Test ADD PARTITION
|
|
# The number of partitions before adding the new partition
|
|
let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS`;
|
|
ALTER TABLE employees ADD PARTITION (PARTITION p3 VALUES LESS THAN (20));
|
|
INSERT INTO employees VALUES (4, 19);
|
|
let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS`;
|
|
--let $assert_text= Check that the partition count is increased after ADD PARTITION
|
|
--let $assert_cond= $before_count < $after_count
|
|
--source include/assert.inc
|
|
|
|
--echo Test DROP PARTITION
|
|
let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
ALTER TABLE employees DROP PARTITION p3;
|
|
let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
--let $assert_text= Check that the partition count is decreased after ADD PARTITION
|
|
--let $assert_cond= $before_count > $after_count
|
|
--source include/assert.inc
|
|
|
|
--echo Test TRUNCATE PARTITION
|
|
# Increase the row count of 1st partition 'p0'
|
|
INSERT INTO employees VALUES (2, 4);
|
|
ALTER TABLE employees TRUNCATE PARTITION P0;
|
|
--let $row_count_after = `SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees' AND PARTITION_NAME= 'p0'`
|
|
--let $assert_text= Check that the row count after is zero
|
|
--let $assert_cond= $row_count_after = 0
|
|
--source include/assert.inc
|
|
|
|
--echo Test REORGANIZE PARTITION
|
|
let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
ALTER TABLE employees REORGANIZE PARTITION p1,p2 INTO (PARTITION p3 VALUES LESS THAN (20));
|
|
let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
|
|
--let $assert_text= Check that the partition count is decreased after REORGANIZE PARTITION
|
|
--let $assert_cond= $before_count > $after_count
|
|
--source include/assert.inc
|
|
|
|
--echo Test EXCHANGE PARTITION
|
|
CREATE TABLE employees1 LIKE employees;
|
|
ALTER TABLE employees1 REMOVE PARTITIONING;
|
|
let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
ALTER TABLE employees EXCHANGE PARTITION p3 WITH TABLE employees1;
|
|
let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
|
|
--let $assert_text= Check that the partition count is same after EXCHANGE PARTITION
|
|
--let $assert_cond= $before_count = $after_count
|
|
--source include/assert.inc
|
|
|
|
DROP TABLE employees1;
|
|
|
|
--echo Test ANALYZE PARTITION
|
|
ALTER TABLE employees ANALYZE PARTITION p0;
|
|
ALTER TABLE employees ANALYZE PARTITION ALL;
|
|
|
|
--echo Test CHECK PARTITION
|
|
|
|
ALTER TABLE employees CHECK PARTITION p0;
|
|
ALTER TABLE employees CHECK PARTITION ALL;
|
|
|
|
--echo Test OPTIMIZE PARTITION
|
|
|
|
ALTER TABLE employees OPTIMIZE PARTITION p3;
|
|
ALTER TABLE employees OPTIMIZE PARTITION ALL;
|
|
|
|
--echo Test REBUILD PARTITION
|
|
|
|
ALTER TABLE employees REBUILD PARTITION p3;
|
|
ALTER TABLE employees REBUILD PARTITION ALL;
|
|
|
|
--echo Test REPAIR PARTITION
|
|
|
|
# Just checking here at once for the count of partition_ordinal_position should
|
|
# be okay and we don't need to check it individually for all commands like
|
|
# CHECK, ANALYZE, OPTIMIZE, REBUILD.
|
|
let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
ALTER TABLE employees REPAIR PARTITION p4;
|
|
ALTER TABLE employees REPAIR PARTITION ALL;
|
|
let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
|
|
--let $assert_text= Check that the partition count is same after REPAIR PARTITION
|
|
--let $assert_cond= $before_count = $after_count
|
|
--source include/assert.inc
|
|
|
|
--echo Test REMOVE PARTITION
|
|
ALTER TABLE employees REMOVE PARTITIONING;
|
|
let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
|
|
--let $assert_text= Check that the partition count is 0 after REMOVE PARTITIONING
|
|
--let $assert_cond= $after_count = 0
|
|
--source include/assert.inc
|
|
|
|
DROP TABLE employees;
|
|
|
|
--echo Test DISCARD/IMPORT PARTITION
|
|
|
|
# Create table and partitions on server_1 as we have removed it in the step above
|
|
# while/after testing REMOVE PARTITION
|
|
|
|
CREATE TABLE employees (id INT NOT NULL, store_id INT NOT NULL);
|
|
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));
|
|
--let $SERVER1_MYSQLD_DATADIR= `SELECT @@DATADIR`
|
|
--let $rpl_connection_name= server_2
|
|
--source include/rpl_connection.inc
|
|
|
|
CREATE TABLE employees (id INT NOT NULL, store_id INT NOT NULL);
|
|
--let $SERVER2_MYSQLD_DATADIR= `SELECT @@DATADIR`
|
|
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));
|
|
ALTER TABLE employees DISCARD PARTITION p0 TABLESPACE;
|
|
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
FLUSH TABLES employees FOR EXPORT;
|
|
--let $rpl_connection_name= server_2
|
|
--source include/rpl_connection.inc
|
|
--copy_file $SERVER1_MYSQLD_DATADIR/test/employees#P#p0.cfg $SERVER2_MYSQLD_DATADIR/test/employees#P#p0.cfg
|
|
--copy_file $SERVER1_MYSQLD_DATADIR/test/employees#P#p0.ibd $SERVER2_MYSQLD_DATADIR/test/employees#P#p0.ibd
|
|
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
UNLOCK TABLES;
|
|
|
|
--let $rpl_connection_name= server_2
|
|
--source include/rpl_connection.inc
|
|
ALTER TABLE employees IMPORT PARTITION p0 TABLESPACE;
|
|
|
|
DROP TABLE employees;
|
|
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
DROP TABLE employees;
|
|
|
|
# The below table and partition type will be used for testing the following
|
|
# PARTITION BY
|
|
# COALESCE PARTITION
|
|
|
|
CREATE TABLE employees (id INT NOT NULL, store_id INT NOT NULL);
|
|
|
|
--echo Test PARTITION BY
|
|
ALTER TABLE employees PARTITION BY HASH(store_id) PARTITIONS 6;
|
|
|
|
--echo Test COALESCE PARTITION
|
|
|
|
let $before_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
ALTER TABLE employees COALESCE PARTITION 2;
|
|
let $after_count = `SELECT COUNT(PARTITION_ORDINAL_POSITION) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees'`;
|
|
|
|
--let $assert_text= Check that the before count is greater than after count after COALESCE PARTITION
|
|
--let $assert_cond= $after_count < $before_count
|
|
--source include/assert.inc
|
|
|
|
|
|
DROP TABLE employees;
|
|
--let $rpl_only_running_threads= 1
|
|
--let $rpl_skip_sync= 1
|
|
--source include/rpl_end.inc
|