Debian Codegraph @

mysql-test/suite/rpl/include/rpl_auto_increment.test

raw
1 #
2 # Test of auto_increment with offset
3 #
4 -- source include/master-slave.inc
5
6 eval create table t1 (a int not null auto_increment,b int, primary key (a)) engine=$engine_type2 auto_increment=3;
7 insert into t1 values (NULL,1),(NULL,2),(NULL,3);
8 select * from t1;
9
10 sync_slave_with_master;
11 select * from t1;
12 connection master;
13 drop table t1;
14
15 eval create table t1 (a int not null auto_increment,b int, primary key (a)) engine=$engine_type2;
16 insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4);
17 delete from t1 where b=4;
18 insert into t1 values (NULL,5),(NULL,6);
19 select * from t1;
20
21 sync_slave_with_master;
22 select * from t1;
23 connection master;
24
25 drop table t1;
26
27 set @@session.auto_increment_increment=100, @@session.auto_increment_offset=10;
28 show variables like "auto_inc%";
29
30 eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2;
31 # Insert with 2 insert statements to get better testing of logging
32 insert into t1 values (NULL),(5),(NULL);
33 insert into t1 values (250),(NULL);
34 select * from t1;
35 insert into t1 values (1000);
36 set @@insert_id=400;
37 insert into t1 values(NULL),(NULL);
38 select * from t1;
39
40 sync_slave_with_master;
41 select * from t1;
42 connection master;
43 drop table t1;
44
45 #
46 # Same test with innodb (as the innodb code is a bit different)
47 #
48 eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type;
49 # Insert with 2 insert statements to get better testing of logging
50 insert into t1 values (NULL),(5),(NULL);
51 insert into t1 values (250),(NULL);
52 select * from t1;
53 insert into t1 values (1000);
54 set @@insert_id=400;
55 insert into t1 values(NULL),(NULL);
56 select * from t1;
57
58 sync_slave_with_master;
59 select * from t1;
60 connection master;
61 drop table t1;
62
63 set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
64 eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2;
65 # Insert with 2 insert statements to get better testing of logging
66 insert into t1 values (NULL),(5),(NULL),(NULL);
67 insert into t1 values (500),(NULL),(502),(NULL),(NULL);
68 select * from t1;
69 set @@insert_id=600;
70 --error ER_DUP_ENTRY
71 insert into t1 values(600),(NULL),(NULL);
72 set @@insert_id=600;
73 insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL);
74 select * from t1;
75
76 sync_slave_with_master;
77 select * from t1;
78 connection master;
79 drop table t1;
80
81 #
82 # Test that auto-increment works when slave has rows in the table
83 #
84 set @@session.auto_increment_increment=10, @@session.auto_increment_offset=1;
85
86 eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2;
87
88 sync_slave_with_master;
89 insert into t1 values(2),(12),(22),(32),(42);
90 connection master;
91
92 insert into t1 values (NULL),(NULL);
93 insert into t1 values (3),(NULL),(NULL);
94 select * from t1;
95
96 sync_slave_with_master;
97 select * from t1;
98
99 # Test for BUG#20524 "auto_increment_* not observed when inserting
100 # a too large value". When an autogenerated value was bigger than the
101 # maximum possible value of the field, it was truncated to that max
102 # possible value, without being "rounded down" to still honour
103 # auto_increment_* variables.
104
105 connection master;
106 drop table t1;
107 create table t1 (a tinyint not null auto_increment primary key) engine=myisam;
108 insert into t1 values(103);
109 set auto_increment_increment=11;
110 set auto_increment_offset=4;
111 insert into t1 values(null);
112 insert into t1 values(null);
113 --error 167
114 insert into t1 values(null);
115 select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a;
116
117 # same but with a larger value
118 create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam;
119 set auto_increment_increment=10;
120 set auto_increment_offset=1;
121 set insert_id=1000;
122 insert into t2 values(10);
123 --error 167
124 insert into t2 values(null);
125 select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a;
126
127 # An offset so big that even first value does not fit
128 create table t3 like t1;
129 set auto_increment_increment=1000;
130 set auto_increment_offset=700;
131 --error 167
132 insert into t3 values(null);
133 select * from t3 order by a;
134 sync_slave_with_master;
135 select * from t1 order by a;
136 select * from t2 order by a;
137 select * from t3 order by a;
138
139 connection master;
140
141 drop table t1,t2,t3;
142 sync_slave_with_master;
143
144 #
145 # BUG#41986 Replication slave does not pick up proper AUTO_INCREMENT value for Innodb tables
146 #
147 connection master;
148 set auto_increment_increment=1;
149 set auto_increment_offset=1;
150 CREATE TABLE t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb;
151 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
152 show create table t1;
153
154 sync_slave_with_master;
155 show create table t1;
156
157 connection master;
158 drop table t1;
159
160 #
161 # BUG#45999 Row based replication fails when auto_increment field = 0.
162 # Store engine of Slaves auto-generates new sequence numbers for
163 # auto_increment fields if the values of them are 0. There is an inconsistency
164 # between slave and master. When MODE_NO_AUTO_VALUE_ON_ZERO are masters treat
165 #
166 source include/rpl_reset.inc;
167
168 connection master;
169 --disable_warnings
170 DROP TABLE IF EXISTS t1;
171 DROP TABLE IF EXISTS t2;
172 --enable_warnings
173
174 eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type;
175 eval CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type2;
176 SET SQL_MODE='';
177 # Value of the id will be 1;
178 INSERT INTO t1 VALUES(NULL);
179 INSERT INTO t2 VALUES(NULL);
180 SELECT * FROM t1;
181 SELECT * FROM t2;
182 # Value of the id will be 2;
183 INSERT INTO t1 VALUES();
184 INSERT INTO t2 VALUES();
185 SELECT * FROM t1;
186 SELECT * FROM t2;
187 # Value of the id will be 3. The master treats 0 as NULL or empty because
188 # NO_AUTO_VALUE_ON_ZERO is not assign to SQL_MODE.
189 INSERT INTO t1 VALUES(0);
190 INSERT INTO t2 VALUES(0);
191 SELECT * FROM t1;
192 SELECT * FROM t2;
193
194 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
195 # Value of the id will be 0. The master does not treat 0 as NULL or empty
196 # because NO_AUTO_VALUE_ON_ZERO has assigned to SQL_MODE.
197 INSERT INTO t1 VALUES(0);
198 INSERT INTO t2 VALUES(0);
199 SELECT * FROM t1;
200 SELECT * FROM t2;
201
202 INSERT INTO t1 VALUES(4);
203 INSERT INTO t2 VALUES(4);
204 FLUSH LOGS;
205 sync_slave_with_master;
206
207 let $diff_tables= master:t1, slave:t1;
208 source include/diff_tables.inc;
209
210 let $diff_tables= master:t2, slave:t2;
211 source include/diff_tables.inc;
212
213 connection master;
214 DROP TABLE t1;
215 DROP TABLE t2;
216 sync_slave_with_master;
217
218 connection master;
219 let $MYSQLD_DATADIR= `SELECT @@DATADIR`;
220 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL test
221 sync_slave_with_master;
222
223 let $diff_tables= master:t1, slave:t1;
224 source include/diff_tables.inc;
225
226 let $diff_tables= master:t2, slave:t2;
227 source include/diff_tables.inc;
228
229 # End cleanup
230 --connection master
231 DROP TABLE t1;
232 DROP TABLE t2;
233 SET SQL_MODE='';
234 sync_slave_with_master;
235
236 #
237 # Bug#54201: "SET INSERT_ID" event must be ignored if corresponding event is
238 # ignored.
239 #
240 connection master;
241
242 CREATE TABLE t1(s VARCHAR(10)) ENGINE=myisam;
243 # -slave.opt has --replicate-ignore-table=test.t_ignored1
244 CREATE TABLE t_ignored1(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
245 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column");
246 sync_slave_with_master;
247
248 connection slave;
249
250 CREATE TABLE test.slave_only(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
251 INSERT INTO slave_only VALUES(NULL);
252 CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO slave_only VALUES(NULL);
253
254 connection master;
255
256 INSERT INTO t_ignored1 VALUES(NULL);
257 INSERT INTO t1 VALUES('s');
258 UPDATE t1 SET s='s1';
259
260 # With Bug#54201, slave stops with duplicate key error here due to trigger
261 # using the insert_id from insert on master into t1_ignored1
262 sync_slave_with_master;
263 connection slave;
264 SELECT * FROM t1;
265
266 connection master;
267 CREATE TABLE t_ignored2(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
268 sync_slave_with_master;
269
270 connection slave;
271 STOP SLAVE;
272 # Ignore the next INSERT into t_ignored2 and the INSERT_ID event just before it.
273 SET GLOBAL sql_slave_skip_counter = 2;
274 START SLAVE;
275
276 connection master;
277 INSERT INTO t_ignored2 VALUES(NULL);
278 UPDATE t1 SET s='s2';
279 sync_slave_with_master;
280
281 connection slave;
282 SELECT * FROM t1;
283 SHOW TABLES LIKE 't\_ignored_';
284 SELECT * FROM t_ignored2;
285 DROP TABLE slave_only;
286
287 connection master;
288 DROP TABLE t1;
289 DROP TABLE t_ignored1;
290 DROP TABLE t_ignored2;
291
292 #
293 # BUG#56662
294 # The test verifies if the assertion of "next_insert_id == 0"
295 # will fail in ha_external_lock() function.
296 #
297 connection master;
298 CREATE TABLE t1 (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, data INT) ENGINE=innodb;
299
300 BEGIN;
301 --echo # Set sql_mode with NO_AUTO_VALUE_ON_ZERO for allowing
302 --echo # zero to fill the auto_increment field.
303 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
304 INSERT INTO t1(id,data) VALUES(0,2);
305 --echo # Resetting sql_mode without NO_AUTO_VALUE_ON_ZERO to
306 --echo # affect the execution of the transaction on slave.
307 SET SQL_MODE=0;
308 COMMIT;
309 SELECT * FROM t1;
310 sync_slave_with_master;
311 SELECT * FROM t1;
312
313 connection master;
314 DROP TABLE t1;
315 sync_slave_with_master;
316
317 --source include/rpl_end.inc