Debian Codegraph @

mysql-test/suite/group_replication/t/gr_columns_partition_tables.test

raw
1 ################################################################################
2 # The aim of this test is to verify that Group Replication does not affect
3 # partitioning of tables.
4 #
5 # 0. Start 3 servers with Group Replication
6 # 1. Create two tables with "range column" partition, "list column" partition.
7 # 2. Perform few DMLs on tables and check it is replicated properly on other servers.
8 # 3. Clean-up
9 ################################################################################
10
11 --source include/big_test.inc
12 --source include/have_group_replication_plugin.inc
13
14 --let $rpl_server_count= 3
15 --source include/group_replication.inc
16
17 --let $rpl_connection_name= server1
18 --source include/rpl_connection.inc
19 --echo ## Create table with Range Column Partition ##
20 CREATE TABLE t1 ( c1 int, c2 int, PRIMARY KEY (c1, c2))
21 CHARSET=utf8mb4
22 PARTITION BY RANGE COLUMNS(c1, c2)
23 (
24 PARTITION t1p1 VALUES LESS THAN (5, 10),
25 PARTITION t1p2 VALUES LESS THAN (MAXVALUE, MAXVALUE)
26 );
27
28 --echo ## Create table with list Column Partition ##
29 CREATE TABLE t2 ( c1 int, c2 char(5), PRIMARY KEY (c1, c2))
30 CHARSET=utf8mb4
31 PARTITION BY LIST COLUMNS(c1, c2)
32 (
33 PARTITION t2p1 VALUES in ( (1, 'aa'), (2, 'ab') ),
34 PARTITION t2p2 VALUES in ( (1, 'cc'), (2, 'cd') )
35 );
36
37 --source include/rpl_sync.inc
38
39 --let $rpl_connection_name= server2
40 --source include/rpl_connection.inc
41 SHOW CREATE TABLE t1;
42 # Insert some values into table t1
43 # This record should belong to partition t1p1
44 INSERT INTO t1 VALUES (05, 2);
45
46 # This record should belong to partition t1p2
47 INSERT INTO t1 VALUES (10, 5);
48
49 # This record should belong to partition t1p1
50 INSERT INTO t1 VALUES (03, 1);
51
52 # This record should belong to partition t1p2
53 INSERT INTO t1 VALUES (100, 50);
54
55 SHOW CREATE TABLE t2;
56 # Insert some values into table t2
57 # This should throw an error as the value is not defined in any partition of t2
58 --error ER_NO_PARTITION_FOR_GIVEN_VALUE
59 INSERT INTO t2 VALUES (1, 'ab');
60
61 # This record should belong to partition t2p1
62 INSERT INTO t2 VALUES (2, 'ab');
63
64 # This record should belong to partition t2p2
65 INSERT INTO t2 VALUES (1, 'cc');
66
67 --source include/rpl_sync.inc
68
69 --let $rpl_connection_name= server1
70 --source include/rpl_connection.inc
71
72 # Perform some DMLs
73 # This record should belong to partition t1p2
74 INSERT INTO t1 VALUES (11, 5);
75
76 DELETE FROM t1 WHERE c1= 5;
77
78 DELETE FROM t2 WHERE c2= 'ab';
79
80 # This record should belong to partition t2p2
81 INSERT INTO t2 VALUES (2, 'cd');
82
83 --source include/rpl_sync.inc
84
85 --let $rpl_connection_name= server3
86 --source include/rpl_connection.inc
87
88 # Validate Record counts
89 # Assert that partition t1p1 has 1 row.
90 --let $assert_text= 'Check that partition t1p1 on table t1 has 1 row'
91 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 PARTITION (t1p1), count, 1]= 1
92 --source include/assert.inc
93
94 # Assert that partition t1p2 has 3 rows.
95 --let $assert_text= 'Check that partition t1p2 on table t1 has 3 rows'
96 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 PARTITION (t1p2), count, 1]= 3
97 --source include/assert.inc
98
99 # Assert that partition t2p1 has 0 rows.
100 --let $assert_text= 'Check that partition t2p1 on table t2 has 0 rows'
101 --let $assert_cond= [SELECT COUNT(*) AS count FROM t2 PARTITION (t2p1), count, 1]= 0
102 --source include/assert.inc
103
104 # Assert that partition t2p2 has 2 rows.
105 --let $assert_text= 'Check that partition t2p2 on table t2 has 2 rows'
106 --let $assert_cond= [SELECT COUNT(*) AS count FROM t2 PARTITION (t2p2), count, 1]= 2
107 --source include/assert.inc
108
109 # Clean-up
110 DROP TABLE t1;
111 DROP TABLE t2;
112 --source include/rpl_sync.inc
113
114 --source include/group_replication_end.inc