1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
#####1.Verifying the value of partition_id written to the binary log #####
CREATE TABLE employees (store_id INT NOT NULL) PARTITION BY LIST (store_id)
( PARTITION pEast VALUES IN (4, 8, 12), PARTITION pWest VALUES IN (3, 6, 9),
PARTITION pNorth VALUES IN (5, 10, 15));
include/save_binlog_position.inc
INSERT INTO employees VALUES(10);
include/rpl_partition_info.inc
ALTER TABLE employees ADD PARTITION (PARTITION pSouth VALUES IN (7, 14, 28));
include/save_binlog_position.inc
INSERT INTO employees VALUES(28);
include/rpl_partition_info.inc
#####2.INSERT, UPDATE, DELETE in a partitioned table affecting same and multiple partition #####
#####3.Verifying the number of events created when executing statements affecting same and multiple partitions #####
-----INSERT-----
include/save_binlog_position.inc
include/save_binlog_position.inc
INSERT INTO employees VALUES(4), (8);
include/assert_binlog_events.inc
include/rpl_partition_info.inc
include/save_binlog_position.inc
INSERT INTO employees VALUES(4), (6);
include/assert_binlog_events.inc
include/rpl_partition_info.inc
-----UPDATE-----
include/save_binlog_position.inc
UPDATE employees SET store_id = 9 WHERE store_id = 4 OR store_id = 8 ;
include/assert_binlog_events.inc
include/rpl_partition_info_update.inc
include/save_binlog_position.inc
UPDATE employees SET store_id = 8 WHERE store_id = 6 OR store_id = 10;
include/assert_binlog_events.inc
include/rpl_partition_info_update.inc
-----DELETE-----
include/save_binlog_position.inc
DELETE FROM employees where store_id = 8;
include/assert_binlog_events.inc
include/rpl_partition_info.inc
include/save_binlog_position.inc
DELETE FROM employees where store_id = 9 OR store_id = 28;
include/assert_binlog_events.inc
include/rpl_partition_info.inc
DROP TABLE employees;
CREATE TABLE employees (store_id INT NOT NULL);
ALTER TABLE employees PARTITION BY LIST (store_id) ( PARTITION pEast VALUES IN (4, 8, 12),
PARTITION pWest VALUES IN (3, 6, 9),
PARTITION pNorth VALUES IN (5, 10, 15));
include/save_binlog_position.inc
INSERT INTO employees VALUES(10);
include/rpl_partition_info.inc
ALTER TABLE employees ADD PARTITION (PARTITION pSouth VALUES IN (7, 14, 28));
include/save_binlog_position.inc
INSERT INTO employees VALUES(28);
include/rpl_partition_info.inc
#####2.INSERT, UPDATE, DELETE in a partitioned table affecting same and multiple partition #####
#####3.Verifying the number of events created when executing statements affecting same and multiple partitions #####
-----INSERT-----
include/save_binlog_position.inc
include/save_binlog_position.inc
INSERT INTO employees VALUES(4), (8);
include/assert_binlog_events.inc
include/rpl_partition_info.inc
include/save_binlog_position.inc
INSERT INTO employees VALUES(4), (6);
include/assert_binlog_events.inc
include/rpl_partition_info.inc
-----UPDATE-----
include/save_binlog_position.inc
UPDATE employees SET store_id = 9 WHERE store_id = 4 OR store_id = 8 ;
include/assert_binlog_events.inc
include/rpl_partition_info_update.inc
include/save_binlog_position.inc
UPDATE employees SET store_id = 8 WHERE store_id = 6 OR store_id = 10;
include/assert_binlog_events.inc
include/rpl_partition_info_update.inc
-----DELETE-----
include/save_binlog_position.inc
DELETE FROM employees where store_id = 8;
include/assert_binlog_events.inc
include/rpl_partition_info.inc
include/save_binlog_position.inc
DELETE FROM employees where store_id = 9 OR store_id = 28;
include/assert_binlog_events.inc
include/rpl_partition_info.inc
DROP TABLE employees;
#####4.Verifying the value of partition_id written to the binary log in case of sub-partitions######
CREATE TABLE sp (id INT, age INT) PARTITION BY LIST (age) SUBPARTITION BY HASH (id)
SUBPARTITIONS 2 (
PARTITION p0 VALUES IN (2, 3, 5, 7, 11),
PARTITION p1 VALUES IN (4, 6, 8, 10, 12));
include/save_binlog_position.inc
INSERT INTO sp VALUES(101, 3);
include/rpl_partition_info.inc
INSERT INTO sp VALUES (102, 3);
INSERT INTO sp VALUES (107, 4);
INSERT INTO sp VALUES (108, 10);
#####5.INSERTS, UPDATES, DELETES in a partitioned table affecting same and multiple sub_partition #####
#####6.Verifying the number of events created when executing statements affecting single and multiple sub_partitions #####
-----INSERT-----
include/save_binlog_position.inc
INSERT INTO sp VALUES(103, 2), (105, 5);
include/assert_binlog_events.inc
include/rpl_partition_info.inc
include/save_binlog_position.inc
INSERT INTO sp VALUES(103, 2), (104, 7);
include/assert_binlog_events.inc
include/rpl_partition_info.inc
-----UPDATE-----
include/save_binlog_position.inc
UPDATE sp SET age=5 WHERE id = 102 OR id=104;
include/rpl_partition_info_update.inc
include/assert_binlog_events.inc
include/save_binlog_position.inc
UPDATE sp SET age=11 WHERE id = 102 OR id=105;
include/assert_binlog_events.inc
include/rpl_partition_info_update.inc
-----DELETE-----
include/save_binlog_position.inc
DELETE FROM sp WHERE id = 101 OR id=105;
include/assert_binlog_events.inc
include/rpl_partition_info.inc
include/save_binlog_position.inc
DELETE FROM sp WHERE id = 108 OR id=107;
include/assert_binlog_events.inc
include/rpl_partition_info.inc
#####7.Sync with slave and check if all the tables are replicated correctly#####
include/sync_slave_sql_with_master.inc
include/diff_tables.inc [master:sp, slave:sp]
DROP TABLE sp;
RESET MASTER;
include/rpl_end.inc
|
