Debian Codegraph @

mysql-test/suite/rpl/t/rpl_partition_id_check.test

raw
1 # ==== Purpose ====
2 #
3 # WL#12168 adds partition information to binary log when INSERT/UPDATE/DELETE
4 # operations are performed on a partitioned table.
5 # The test performs INSERT/UPDATE/DELETE through transactions, functions,
6 # procedures, triggers and verifies that partition information is correctly
7 # written into binary log every time.
8 #
9 # ==== Implementation ====
10 #
11 # 1) Perform INSERT, UPDATE, DELETE operations on partitioned table through
12 # FUNCTIONS. Verify partition information in binary log
13 # 2) Perform INSERT, UPDATE, DELETE operations on partitioned table through
14 # PROCEDURES. Verify partition information in binary log
15 # 3) Perform INSERT, UPDATE, DELETE operations on partitioned table through
16 # TRIGGERS. Verify partition information in binary log
17 # 4) Perform INSERT, UPDATE, DELETE operations on partitioned table through
18 # transactions. Verify partition information in binary log
19 # Also, verify Table_map and event_type for transactions involving
20 # single, multiple and no partitions
21 # 5) Sync with slave and check if all the tables are replicated correctly
22 # 6) Clean up
23 #
24 # ==== References ====
25 #
26 # WL#12168:Add Partition Information into the Binary Log
27 #
28
29 --source include/have_binlog_format_row.inc
30 --source include/set_privilege_checks_user_as_system_user.inc
31 --source include/master-slave.inc
32 --let $gtid_event= !Gtid_or_anon
33
34 # Test partition information with Functions
35 CREATE TABLE func(id INT, age INT) PARTITION BY RANGE(age) (PARTITION P0 VALUES LESS THAN (25), PARTITION P1 VALUES LESS THAN (50));
36 DELIMITER //;
37 CREATE FUNCTION insert_data() RETURNS INT DETERMINISTIC
38 BEGIN
39 INSERT INTO func VALUES (100, 36);
40 RETURN (SELECT COUNT(*) FROM func);
41 END//
42 CREATE FUNCTION update_data() RETURNS INT DETERMINISTIC
43 BEGIN
44 UPDATE func SET age=age-12 WHERE id=100;
45 RETURN (SELECT COUNT(*) FROM func);
46 END//
47 CREATE FUNCTION delete_data() RETURNS INT DETERMINISTIC
48 BEGIN
49 DELETE FROM func WHERE id=100;
50 RETURN (SELECT COUNT(*) FROM func);
51 END//
52 DELIMITER ;//
53 --source include/save_binlog_position.inc
54 BEGIN; SELECT insert_data(); COMMIT;
55 --let $expected_partition_id = 1
56 --source suite/rpl/include/rpl_partition_info.inc
57 BEGIN; SELECT update_data(); COMMIT;
58 --let $partition_id= 1 0
59 --source suite/rpl/include/rpl_partition_info_update.inc
60 BEGIN; SELECT delete_data(); COMMIT;
61 --let $expected_partition_id = 0
62 --source suite/rpl/include/rpl_partition_info.inc
63
64 # Test partition information with Procedures
65 CREATE TABLE proc(id INT, age INT) PARTITION BY RANGE(age) (PARTITION P0 VALUES LESS THAN (35), PARTITION P1 VALUES LESS THAN (60));
66 DELIMITER //;
67 CREATE PROCEDURE insert_data()
68 BEGIN
69 INSERT INTO proc VALUES (100, 30);
70 END//
71 CREATE PROCEDURE update_data()
72 BEGIN
73 UPDATE proc SET age=age+10 WHERE id=100;
74 END//
75 CREATE PROCEDURE delete_data()
76 BEGIN
77 DELETE FROM proc WHERE id=100;
78 END//
79 DELIMITER ;//
80 --source include/save_binlog_position.inc
81 BEGIN; CALL insert_data(); COMMIT;
82 --let $expected_partition_id = 0
83 --source suite/rpl/include/rpl_partition_info.inc
84 --source include/save_binlog_position.inc
85 BEGIN; CALL update_data(); COMMIT;
86 --let $partition_id= 0 1
87 --source suite/rpl/include/rpl_partition_info_update.inc
88 --source include/save_binlog_position.inc
89 BEGIN; CALL delete_data(); COMMIT;
90 --let $expected_partition_id = 1
91 --source suite/rpl/include/rpl_partition_info.inc
92
93 # Test partition information with Triggers
94 CREATE TABLE t1(a INT);
95 CREATE TABLE trigg(id INT, age INT) PARTITION BY RANGE(age) (PARTITION P0 VALUES LESS THAN (25), PARTITION P1 VALUES LESS THAN (60));
96 DELIMITER //;
97 CREATE TRIGGER insert_data AFTER INSERT ON t1 FOR EACH ROW
98 BEGIN
99 INSERT INTO trigg VALUES (100, 36);
100 END//
101 CREATE TRIGGER update_data AFTER UPDATE ON t1 FOR EACH ROW
102 BEGIN
103 UPDATE trigg SET age=age-13 WHERE id=100;
104 END//
105 CREATE TRIGGER delete_data AFTER DELETE ON t1 FOR EACH ROW
106 BEGIN
107 DELETE FROM trigg WHERE id=100;
108 END//
109 DELIMITER ;//
110 --source include/save_binlog_position.inc
111 BEGIN; INSERT INTO t1 VALUES(1); COMMIT;
112 --let $expected_partition_id = 1
113 --source suite/rpl/include/rpl_partition_info.inc
114 --source include/save_binlog_position.inc
115 BEGIN; UPDATE t1 SET a=a+10; COMMIT;
116 --let $partition_id= 1 0
117 --source suite/rpl/include/rpl_partition_info_update.inc
118 --source include/save_binlog_position.inc
119 BEGIN; DELETE FROM t1; COMMIT;
120 --let $expected_partition_id = 0
121 --source suite/rpl/include/rpl_partition_info.inc
122
123 # Test partition information with Transactions
124 CREATE TABLE trans(id INT, age INT) PARTITION BY RANGE(age) SUBPARTITION BY KEY(id)
125 (
126 PARTITION P0 VALUES LESS THAN (30)
127 (SUBPARTITION P0A, SUBPARTITION P0B)
128 ,
129 PARTITION P1 VALUES LESS THAN (60)
130 (SUBPARTITION P1A, SUBPARTITION P1B)
131 );
132 --source include/save_binlog_position.inc
133 BEGIN; INSERT INTO trans VALUES(100,46); COMMIT;
134 --let $expected_partition_id = 3
135 --source suite/rpl/include/rpl_partition_info.inc
136 --source include/save_binlog_position.inc
137 BEGIN; UPDATE trans SET age=age-20 WHERE id=100; COMMIT;
138 --let $partition_id = 3 1
139 --source suite/rpl/include/rpl_partition_info_update.inc
140 --source include/save_binlog_position.inc
141 BEGIN; DELETE FROM trans WHERE id=100; COMMIT;
142 --let $expected_partition_id = 1
143 --source suite/rpl/include/rpl_partition_info.inc
144 # Verify Table_map and event_type for Insert Update Delete within a single transaction on single partition
145 --source include/save_binlog_position.inc
146 BEGIN;
147 INSERT INTO trans VALUES(1,55),(1,55);
148 UPDATE trans SET id=id+10;
149 DELETE FROM trans WHERE id=11;
150 COMMIT;
151 --let $insert_pattern= # Table_map # Write_rows
152 --let $update_pattern= # Table_map # Update_rows
153 --let $delete_pattern= # Table_map # Delete_rows
154 --let $dont_print_pattern= 1
155 --let $event_sequence = $gtid_event # !Begin $insert_pattern $update_pattern $delete_pattern # !Commit
156 --source include/assert_binlog_events.inc
157 # Verify Table_map and event_type for Insert Update Delete within a single transaction on multiple partitions
158 --source include/save_binlog_position.inc
159 BEGIN;
160 INSERT INTO trans VALUES(101,46),(102,23);
161 UPDATE trans SET id=id+100;
162 DELETE FROM trans WHERE id=201;
163 COMMIT;
164 --let $event_sequence = $gtid_event # !Begin $insert_pattern # Write_rows $update_pattern # Update_rows $delete_pattern # !Commit
165 --source include/assert_binlog_events.inc
166 # Verify Table_map and event_type for Insert Update Delete within a single transaction on partitioned and non-partitioned table
167 CREATE TABLE t2(a INT);
168 --source include/save_binlog_position.inc
169 BEGIN;
170 INSERT INTO trans VALUES(301,55),(301,55);
171 INSERT INTO t2 VALUES(10);
172 UPDATE trans SET id=id+10;
173 UPDATE t2 SET a=a+10;
174 DELETE FROM trans WHERE id=11;
175 DELETE FROM t2 WHERE a=20;
176 COMMIT;
177 --let $event_sequence = $gtid_event # !Begin $insert_pattern $insert_pattern $update_pattern # Update_rows $update_pattern $delete_pattern # !Commit
178 --source include/assert_binlog_events.inc
179
180 # Check if data is replicated properly across master and slave
181 --source include/sync_slave_sql_with_master.inc
182 let $diff_tables= master:trans, slave:trans;
183 --source include/diff_tables.inc
184 let $diff_tables= master:func, slave:func;
185 --source include/diff_tables.inc
186 let $diff_tables= master:proc, slave:proc;
187 --source include/diff_tables.inc
188 let $diff_tables= master:trigg, slave:trigg;
189 --source include/diff_tables.inc
190
191 # Cleanup
192 connection master;
193 DROP FUNCTION insert_data;
194 DROP FUNCTION update_data;
195 DROP FUNCTION delete_data;
196 DROP PROCEDURE insert_data;
197 DROP PROCEDURE update_data;
198 DROP PROCEDURE delete_data;
199 DROP TRIGGER insert_data;
200 DROP TRIGGER update_data;
201 DROP TRIGGER delete_data;
202 DROP TABLE trans;
203 DROP TABLE func;
204 DROP TABLE proc;
205 DROP TABLE trigg;
206 DROP TABLE t1;
207 DROP TABLE t2;
208
209 --source include/sync_slave_sql_with_master.inc
210 --source include/rpl_end.inc