115 lines
3.7 KiB
Plaintext
115 lines
3.7 KiB
Plaintext
################################################################################
|
|
# The aim of this test is to verify that Group Replication does not affect
|
|
# partitioning of tables.
|
|
#
|
|
# 0. Start 3 servers with Group Replication
|
|
# 1. Create two tables with "range column" partition, "list column" partition.
|
|
# 2. Perform few DMLs on tables and check it is replicated properly on other servers.
|
|
# 3. Clean-up
|
|
################################################################################
|
|
|
|
--source include/big_test.inc
|
|
--source include/have_group_replication_plugin.inc
|
|
|
|
--let $rpl_server_count= 3
|
|
--source include/group_replication.inc
|
|
|
|
--let $rpl_connection_name= server1
|
|
--source include/rpl_connection.inc
|
|
--echo ## Create table with Range Column Partition ##
|
|
CREATE TABLE t1 ( c1 int, c2 int, PRIMARY KEY (c1, c2))
|
|
CHARSET=utf8mb4
|
|
PARTITION BY RANGE COLUMNS(c1, c2)
|
|
(
|
|
PARTITION t1p1 VALUES LESS THAN (5, 10),
|
|
PARTITION t1p2 VALUES LESS THAN (MAXVALUE, MAXVALUE)
|
|
);
|
|
|
|
--echo ## Create table with list Column Partition ##
|
|
CREATE TABLE t2 ( c1 int, c2 char(5), PRIMARY KEY (c1, c2))
|
|
CHARSET=utf8mb4
|
|
PARTITION BY LIST COLUMNS(c1, c2)
|
|
(
|
|
PARTITION t2p1 VALUES in ( (1, 'aa'), (2, 'ab') ),
|
|
PARTITION t2p2 VALUES in ( (1, 'cc'), (2, 'cd') )
|
|
);
|
|
|
|
--source include/rpl_sync.inc
|
|
|
|
--let $rpl_connection_name= server2
|
|
--source include/rpl_connection.inc
|
|
SHOW CREATE TABLE t1;
|
|
# Insert some values into table t1
|
|
# This record should belong to partition t1p1
|
|
INSERT INTO t1 VALUES (05, 2);
|
|
|
|
# This record should belong to partition t1p2
|
|
INSERT INTO t1 VALUES (10, 5);
|
|
|
|
# This record should belong to partition t1p1
|
|
INSERT INTO t1 VALUES (03, 1);
|
|
|
|
# This record should belong to partition t1p2
|
|
INSERT INTO t1 VALUES (100, 50);
|
|
|
|
SHOW CREATE TABLE t2;
|
|
# Insert some values into table t2
|
|
# This should throw an error as the value is not defined in any partition of t2
|
|
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
|
|
INSERT INTO t2 VALUES (1, 'ab');
|
|
|
|
# This record should belong to partition t2p1
|
|
INSERT INTO t2 VALUES (2, 'ab');
|
|
|
|
# This record should belong to partition t2p2
|
|
INSERT INTO t2 VALUES (1, 'cc');
|
|
|
|
--source include/rpl_sync.inc
|
|
|
|
--let $rpl_connection_name= server1
|
|
--source include/rpl_connection.inc
|
|
|
|
# Perform some DMLs
|
|
# This record should belong to partition t1p2
|
|
INSERT INTO t1 VALUES (11, 5);
|
|
|
|
DELETE FROM t1 WHERE c1= 5;
|
|
|
|
DELETE FROM t2 WHERE c2= 'ab';
|
|
|
|
# This record should belong to partition t2p2
|
|
INSERT INTO t2 VALUES (2, 'cd');
|
|
|
|
--source include/rpl_sync.inc
|
|
|
|
--let $rpl_connection_name= server3
|
|
--source include/rpl_connection.inc
|
|
|
|
# Validate Record counts
|
|
# Assert that partition t1p1 has 1 row.
|
|
--let $assert_text= 'Check that partition t1p1 on table t1 has 1 row'
|
|
--let $assert_cond= [SELECT COUNT(*) AS count FROM t1 PARTITION (t1p1), count, 1]= 1
|
|
--source include/assert.inc
|
|
|
|
# Assert that partition t1p2 has 3 rows.
|
|
--let $assert_text= 'Check that partition t1p2 on table t1 has 3 rows'
|
|
--let $assert_cond= [SELECT COUNT(*) AS count FROM t1 PARTITION (t1p2), count, 1]= 3
|
|
--source include/assert.inc
|
|
|
|
# Assert that partition t2p1 has 0 rows.
|
|
--let $assert_text= 'Check that partition t2p1 on table t2 has 0 rows'
|
|
--let $assert_cond= [SELECT COUNT(*) AS count FROM t2 PARTITION (t2p1), count, 1]= 0
|
|
--source include/assert.inc
|
|
|
|
# Assert that partition t2p2 has 2 rows.
|
|
--let $assert_text= 'Check that partition t2p2 on table t2 has 2 rows'
|
|
--let $assert_cond= [SELECT COUNT(*) AS count FROM t2 PARTITION (t2p2), count, 1]= 2
|
|
--source include/assert.inc
|
|
|
|
# Clean-up
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
--source include/rpl_sync.inc
|
|
|
|
--source include/group_replication_end.inc
|