Debian Codegraph @

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

raw
1 #
2 # Bug45677
3 # This test verifies the following two properties:
4 # P1) insert/update in an autoinc column causes statement to
5 # be logged in row format if binlog_format=mixed.
6 # P2) if binlog_format=mixed, and a trigger or function contains
7 # two or more inserts/updates in a table that has an autoinc
8 # column, then the slave should not go out of sync, even if
9 # there are concurrent transactions.
10 #
11 # Property (P1) is tested by executing an insert and an update on
12 # a table that has an autoinc column, and verifying that these
13 # statements result in row events in the binlog.
14 # Property (P2) is tested by setting up the test scenario and
15 # verifying that the tables are identical on master and slave.
16 #
17
18 --source include/not_group_replication_plugin.inc
19 source include/have_binlog_format_mixed.inc;
20 --source include/set_privilege_checks_user_as_system_user.inc
21 source include/master-slave.inc;
22
23 --echo # Test case1: INVOKES A TRIGGER with after insert action
24 let $trigger_action = after insert;
25 source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
26
27 --echo # Test case2: INVOKES A TRIGGER with before insert action
28 let $trigger_action = before insert;
29 source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
30
31 --echo # Test case3: INVOKES A TRIGGER with after update action
32 let $trigger_action = after update;
33 source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
34
35 --echo # Test case4: INVOKES A TRIGGER with before update action
36 let $trigger_action = before update;
37 source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
38
39 --echo # Test case5: INVOKES A TRIGGER with after delete action
40 let $trigger_action = after delete;
41 source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
42
43 --echo # Test case6: INVOKES A TRIGGER with before delete action
44 let $trigger_action = before delete;
45 source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test;
46
47 --echo # Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action
48 let $insert_action = after insert;
49 source extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test;
50
51 --echo # Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action
52 let $insert_action = before insert;
53 source extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test;
54
55 --echo # Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action
56 let $insert_action = after insert;
57 source extra/rpl_tests/rpl_auto_increment_insert_view.test;
58
59 --echo # Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action
60 let $insert_action = before insert;
61 source extra/rpl_tests/rpl_auto_increment_insert_view.test;
62
63 --echo # Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN
64 connection master;
65 create table t1(a int) engine=innodb;
66 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
67 delimiter //;
68 CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
69 BEGIN
70 INSERT INTO t2(a) values(2);
71 INSERT INTO t2(a) values(2);
72 RETURN 1;
73 END//
74 delimiter ;//
75 begin;
76 insert into t1(a) values(f1_two_inserts());
77
78 connection master1;
79 #The default autocommit is set to 1, so the statement is auto committed
80 insert into t2(a) values(4),(5);
81
82 connection master;
83 commit;
84 insert into t1(a) values(f1_two_inserts());
85 commit;
86
87 connection master;
88 --echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master
89 select * from t2 ORDER BY i1;
90
91 --source include/sync_slave_sql_with_master.inc
92 connection slave;
93 --echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave
94 select * from t2 ORDER BY i1;
95
96 connection master;
97 drop table t1;
98 drop table t2;
99 drop function f1_two_inserts;
100 --source include/sync_slave_sql_with_master.inc
101
102 --echo # Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
103 connection master;
104 create table t1(a int) engine=innodb;
105 create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
106 delimiter //;
107 CREATE FUNCTION f1_two_updates() RETURNS INTEGER
108 BEGIN
109 update t2 set a = a + 5 where b = 1;
110 update t2 set a = a + 5 where b = 2;
111 update t2 set a = a + 5 where b = 3;
112 update t2 set a = a + 5 where b = 4;
113 RETURN 1;
114 END//
115 delimiter ;//
116
117 connection master1;
118 #The default autocommit is set to 1, so the statement is auto committed
119 insert into t2(a,b) values(1,1);
120 insert into t2(a,b) values(2,2);
121 insert into t2(a,b) values(3,3);
122 insert into t2(a,b) values(4,4);
123 insert into t1(a) values(f1_two_updates());
124
125 connection master;
126 begin;
127 insert into t1(a) values(f1_two_updates());
128 commit;
129
130 connection master;
131 --echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master
132 select * from t2 ORDER BY i1;
133
134 --source include/sync_slave_sql_with_master.inc
135 connection slave;
136 --echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave
137 select * from t2 ORDER BY i1;
138
139 connection master;
140 drop table t1;
141 drop table t2;
142 drop function f1_two_updates;
143 --source include/sync_slave_sql_with_master.inc
144
145 --echo # Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT
146 connection master;
147 create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
148 create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
149 begin;
150 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
151 insert into t1(a,b) values(1,1),(2,2);
152 insert into t2(a,b) values(1,1),(2,2);
153 update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b;
154 insert into t1(a,b) values(3,3);
155 insert into t2(a,b) values(3,3);
156 commit;
157 --echo # To verify if it works fine when these statements are not be marked as unsafe
158 source include/show_binlog_events.inc;
159
160 --source include/sync_slave_sql_with_master.inc
161 --echo #Test if the results are consistent on master and slave
162 --echo #for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT'
163 let $diff_tables= master:t1, slave:t1;
164 source include/diff_tables.inc;
165 let $diff_tables= master:t2, slave:t2;
166 source include/diff_tables.inc;
167
168 connection master;
169 drop table t1;
170 drop table t2;
171 --source include/sync_slave_sql_with_master.inc
172
173 --echo # Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES
174 connection master;
175 CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
176 CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
177 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
178 begin;
179 INSERT INTO t1(c1) VALUES (11), (12);
180 INSERT INTO t2(c2) VALUES (13), (14);
181
182 CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2;
183
184 INSERT INTO v15(c1) VALUES (15),(16);
185 INSERT INTO v15(c2) VALUES (17),(18);
186
187 connection master1;
188 INSERT INTO v15(c1) VALUES (19),(20);
189 INSERT INTO v15(c2) VALUES (21),(22);
190
191 connection master;
192 INSERT INTO v15(c1) VALUES (23), (24);
193 INSERT INTO v15(c2) VALUES (25), (26);
194 commit;
195 --echo # To verify if it works fine when these statements are not be marked as unsafe
196 source include/show_binlog_events.inc;
197
198 --source include/sync_slave_sql_with_master.inc
199 --echo #Test if the results are consistent on master and slave
200 --echo #for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES'
201 let $diff_tables= master:t1, slave:t1;
202 source include/diff_tables.inc;
203 let $diff_tables= master:t2, slave:t2;
204 source include/diff_tables.inc;
205
206 connection master;
207 drop table t1;
208 drop table t2;
209 drop view v15;
210 --source include/sync_slave_sql_with_master.inc
211
212 --source include/rpl_end.inc