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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
|
# ==== Purpose ====
#
# WL#12168 adds partition information to binary log when INSERT/UPDATE/DELETE
# operations are performed on a partitioned table.
# The test performs INSERT/UPDATE/DELETE through transactions, functions,
# procedures, triggers and verifies that partition information is correctly
# written into binary log every time.
#
# ==== Implementation ====
#
# 1) Perform INSERT, UPDATE, DELETE operations on partitioned table through
# FUNCTIONS. Verify partition information in binary log
# 2) Perform INSERT, UPDATE, DELETE operations on partitioned table through
# PROCEDURES. Verify partition information in binary log
# 3) Perform INSERT, UPDATE, DELETE operations on partitioned table through
# TRIGGERS. Verify partition information in binary log
# 4) Perform INSERT, UPDATE, DELETE operations on partitioned table through
# transactions. Verify partition information in binary log
# Also, verify Table_map and event_type for transactions involving
# single, multiple and no partitions
# 5) Sync with slave and check if all the tables are replicated correctly
# 6) Clean up
#
# ==== References ====
#
# WL#12168:Add Partition Information into the Binary Log
#
--source include/have_binlog_format_row.inc
--source include/set_privilege_checks_user_as_system_user.inc
--source include/master-slave.inc
--let $gtid_event= !Gtid_or_anon
# Test partition information with Functions
CREATE TABLE func(id INT, age INT) PARTITION BY RANGE(age) (PARTITION P0 VALUES LESS THAN (25), PARTITION P1 VALUES LESS THAN (50));
DELIMITER //;
CREATE FUNCTION insert_data() RETURNS INT DETERMINISTIC
BEGIN
INSERT INTO func VALUES (100, 36);
RETURN (SELECT COUNT(*) FROM func);
END//
CREATE FUNCTION update_data() RETURNS INT DETERMINISTIC
BEGIN
UPDATE func SET age=age-12 WHERE id=100;
RETURN (SELECT COUNT(*) FROM func);
END//
CREATE FUNCTION delete_data() RETURNS INT DETERMINISTIC
BEGIN
DELETE FROM func WHERE id=100;
RETURN (SELECT COUNT(*) FROM func);
END//
DELIMITER ;//
--source include/save_binlog_position.inc
BEGIN; SELECT insert_data(); COMMIT;
--let $expected_partition_id = 1
--source suite/rpl/include/rpl_partition_info.inc
BEGIN; SELECT update_data(); COMMIT;
--let $partition_id= 1 0
--source suite/rpl/include/rpl_partition_info_update.inc
BEGIN; SELECT delete_data(); COMMIT;
--let $expected_partition_id = 0
--source suite/rpl/include/rpl_partition_info.inc
# Test partition information with Procedures
CREATE TABLE proc(id INT, age INT) PARTITION BY RANGE(age) (PARTITION P0 VALUES LESS THAN (35), PARTITION P1 VALUES LESS THAN (60));
DELIMITER //;
CREATE PROCEDURE insert_data()
BEGIN
INSERT INTO proc VALUES (100, 30);
END//
CREATE PROCEDURE update_data()
BEGIN
UPDATE proc SET age=age+10 WHERE id=100;
END//
CREATE PROCEDURE delete_data()
BEGIN
DELETE FROM proc WHERE id=100;
END//
DELIMITER ;//
--source include/save_binlog_position.inc
BEGIN; CALL insert_data(); COMMIT;
--let $expected_partition_id = 0
--source suite/rpl/include/rpl_partition_info.inc
--source include/save_binlog_position.inc
BEGIN; CALL update_data(); COMMIT;
--let $partition_id= 0 1
--source suite/rpl/include/rpl_partition_info_update.inc
--source include/save_binlog_position.inc
BEGIN; CALL delete_data(); COMMIT;
--let $expected_partition_id = 1
--source suite/rpl/include/rpl_partition_info.inc
# Test partition information with Triggers
CREATE TABLE t1(a INT);
CREATE TABLE trigg(id INT, age INT) PARTITION BY RANGE(age) (PARTITION P0 VALUES LESS THAN (25), PARTITION P1 VALUES LESS THAN (60));
DELIMITER //;
CREATE TRIGGER insert_data AFTER INSERT ON t1 FOR EACH ROW
BEGIN
INSERT INTO trigg VALUES (100, 36);
END//
CREATE TRIGGER update_data AFTER UPDATE ON t1 FOR EACH ROW
BEGIN
UPDATE trigg SET age=age-13 WHERE id=100;
END//
CREATE TRIGGER delete_data AFTER DELETE ON t1 FOR EACH ROW
BEGIN
DELETE FROM trigg WHERE id=100;
END//
DELIMITER ;//
--source include/save_binlog_position.inc
BEGIN; INSERT INTO t1 VALUES(1); COMMIT;
--let $expected_partition_id = 1
--source suite/rpl/include/rpl_partition_info.inc
--source include/save_binlog_position.inc
BEGIN; UPDATE t1 SET a=a+10; COMMIT;
--let $partition_id= 1 0
--source suite/rpl/include/rpl_partition_info_update.inc
--source include/save_binlog_position.inc
BEGIN; DELETE FROM t1; COMMIT;
--let $expected_partition_id = 0
--source suite/rpl/include/rpl_partition_info.inc
# Test partition information with Transactions
CREATE TABLE trans(id INT, age INT) PARTITION BY RANGE(age) SUBPARTITION BY KEY(id)
(
PARTITION P0 VALUES LESS THAN (30)
(SUBPARTITION P0A, SUBPARTITION P0B)
,
PARTITION P1 VALUES LESS THAN (60)
(SUBPARTITION P1A, SUBPARTITION P1B)
);
--source include/save_binlog_position.inc
BEGIN; INSERT INTO trans VALUES(100,46); COMMIT;
--let $expected_partition_id = 3
--source suite/rpl/include/rpl_partition_info.inc
--source include/save_binlog_position.inc
BEGIN; UPDATE trans SET age=age-20 WHERE id=100; COMMIT;
--let $partition_id = 3 1
--source suite/rpl/include/rpl_partition_info_update.inc
--source include/save_binlog_position.inc
BEGIN; DELETE FROM trans WHERE id=100; COMMIT;
--let $expected_partition_id = 1
--source suite/rpl/include/rpl_partition_info.inc
# Verify Table_map and event_type for Insert Update Delete within a single transaction on single partition
--source include/save_binlog_position.inc
BEGIN;
INSERT INTO trans VALUES(1,55),(1,55);
UPDATE trans SET id=id+10;
DELETE FROM trans WHERE id=11;
COMMIT;
--let $insert_pattern= # Table_map # Write_rows
--let $update_pattern= # Table_map # Update_rows
--let $delete_pattern= # Table_map # Delete_rows
--let $dont_print_pattern= 1
--let $event_sequence = $gtid_event # !Begin $insert_pattern $update_pattern $delete_pattern # !Commit
--source include/assert_binlog_events.inc
# Verify Table_map and event_type for Insert Update Delete within a single transaction on multiple partitions
--source include/save_binlog_position.inc
BEGIN;
INSERT INTO trans VALUES(101,46),(102,23);
UPDATE trans SET id=id+100;
DELETE FROM trans WHERE id=201;
COMMIT;
--let $event_sequence = $gtid_event # !Begin $insert_pattern # Write_rows $update_pattern # Update_rows $delete_pattern # !Commit
--source include/assert_binlog_events.inc
# Verify Table_map and event_type for Insert Update Delete within a single transaction on partitioned and non-partitioned table
CREATE TABLE t2(a INT);
--source include/save_binlog_position.inc
BEGIN;
INSERT INTO trans VALUES(301,55),(301,55);
INSERT INTO t2 VALUES(10);
UPDATE trans SET id=id+10;
UPDATE t2 SET a=a+10;
DELETE FROM trans WHERE id=11;
DELETE FROM t2 WHERE a=20;
COMMIT;
--let $event_sequence = $gtid_event # !Begin $insert_pattern $insert_pattern $update_pattern # Update_rows $update_pattern $delete_pattern # !Commit
--source include/assert_binlog_events.inc
# Check if data is replicated properly across master and slave
--source include/sync_slave_sql_with_master.inc
let $diff_tables= master:trans, slave:trans;
--source include/diff_tables.inc
let $diff_tables= master:func, slave:func;
--source include/diff_tables.inc
let $diff_tables= master:proc, slave:proc;
--source include/diff_tables.inc
let $diff_tables= master:trigg, slave:trigg;
--source include/diff_tables.inc
# Cleanup
connection master;
DROP FUNCTION insert_data;
DROP FUNCTION update_data;
DROP FUNCTION delete_data;
DROP PROCEDURE insert_data;
DROP PROCEDURE update_data;
DROP PROCEDURE delete_data;
DROP TRIGGER insert_data;
DROP TRIGGER update_data;
DROP TRIGGER delete_data;
DROP TABLE trans;
DROP TABLE func;
DROP TABLE proc;
DROP TABLE trigg;
DROP TABLE t1;
DROP TABLE t2;
--source include/sync_slave_sql_with_master.inc
--source include/rpl_end.inc
|
