Debian Codegraph @

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

raw
1 # === Purpose ===
2 #
3 # This test validates that when a single statament generates multiple write
4 # row events for diferent tables, and one of thoese table contains an extra
5 # auto incremented key in the replica, the generation of the auto
6 # incremented key in the replica works correctly.
7 #
8 # ==== Requirements ====
9 #
10 # R1. Replication to a table with an extra auto-increment column should work
11 # when a single statement updates multiple tables.
12 #
13 # === Implementation ====
14 #
15 # 1. Create a table t1 with a primary key on both source and replica
16 # Create a table t2 that only has an auto incremented PK in the replica
17 # Create a table t3 that only has an auto incremented KEY in the replica
18 # 2. Create a trigger that for inserts in t1, inserts something on t2
19 # Create a function that inserts 2 rows in t3 but one of the rows is
20 # too large to be grouped with other in a single log event
21 # 3. Insert something on t1 on the source
22 # Validate that the table t2 on the replica has the expected values
23 # Execute the multi_event_function() on the source
24 # Validate that the table t3 on the replica has the expected values
25 # 4. Cleanup
26 #
27 # === References ===
28 #
29 # Bug#34095290: ha_external_lock(THD*, int): Assertion `next_insert_id == 0' failure
30 #
31
32 --source include/have_binlog_format_row.inc
33 --source include/set_privilege_checks_user_as_system_user.inc
34 --source include/master-slave.inc
35
36 --echo
37 --echo ##############################################################
38 --echo # 1. Create a table t1 with a primary key on both source and replica
39 --echo # Create a table t2 that only has an auto incremented PK in the replica
40 --echo # Create a table t3 that only has an auto incremented KEY in the replica
41
42 CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
43
44 CREATE TABLE t2 (f1 INT) ENGINE=InnoDB;
45
46 CREATE TABLE t3 (f1 TEXT);
47
48 --source include/sync_slave_sql_with_master.inc
49
50 ALTER TABLE t2 ADD f2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY AFTER f1;
51
52 ALTER TABLE t3 ADD f2 INT NOT NULL AUTO_INCREMENT KEY AFTER f1;
53
54 --echo
55 --echo ##############################################################
56 --echo # 2. Create a trigger that for inserts in t1, inserts something on t2
57 --echo # Create a function that inserts 2 rows in t3 but one of the rows is
58 --echo # too large to be grouped with other in a single log event
59
60
61 --source include/rpl_connection_master.inc
62
63 --delimiter |
64 CREATE TRIGGER insert_trigger BEFORE INSERT ON t1
65 FOR EACH ROW
66 BEGIN
67 INSERT INTO t2(f1) VALUES (1);
68 INSERT INTO t2(f1) VALUES (2);
69 END |
70 --delimiter ;
71
72 --delimiter |
73 CREATE FUNCTION multi_event_function() RETURNS INT BEGIN
74 INSERT INTO t3 VALUES (REPEAT('a', 300));
75 INSERT INTO t3 VALUES ('a');
76 RETURN 0;
77 END|
78 --delimiter ;
79
80 --source include/sync_slave_sql_with_master.inc
81
82 --echo
83 --echo ##############################################################
84 --echo # 3. Insert something on t1 on the source
85 --echo # Validate that the table t2 on the replica has the expected values
86 --echo # Execute the multi_event_function() on the source
87 --echo # Validate that the table t3 on the replica has the expected values
88
89 --source include/rpl_connection_master.inc
90
91 INSERT INTO t1 VALUES (99);
92
93 SELECT multi_event_function();
94
95 --source include/sync_slave_sql_with_master.inc
96
97 --let $column_count= `SELECT COUNT(*) FROM t2 WHERE f2 IN (1,2);`
98 --let $assert_text= The trigger associated table was updated
99 --let $assert_cond= $column_count = 2
100 --source include/assert.inc
101
102 --let $column_count= `SELECT COUNT(*) FROM t3 WHERE f2 IN (1,2);`
103 --let $assert_text= The function associated table was updated
104 --let $assert_cond= $column_count = 2
105 --source include/assert.inc
106
107 --echo
108 --echo ##############################################################
109 --echo # 4. Cleanup
110
111 --source include/rpl_connection_master.inc
112
113 DROP FUNCTION multi_event_function;
114 DROP TRIGGER insert_trigger;
115 DROP TABLE t1;
116 DROP TABLE t2;
117 DROP TABLE t3;
118
119 --source include/sync_slave_sql_with_master.inc
120 --source include/rpl_end.inc