1# 2# Test of auto_increment with offset 3# 4-- source include/master-slave.inc 5 6eval create table t1 (a int not null auto_increment,b int, primary key (a)) engine=$engine_type2 auto_increment=3; 7insert into t1 values (NULL,1),(NULL,2),(NULL,3); 8select * from t1; 9 10sync_slave_with_master; 11select * from t1; 12connection master; 13drop table t1; 14 15eval create table t1 (a int not null auto_increment,b int, primary key (a)) engine=$engine_type2; 16insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4); 17delete from t1 where b=4; 18insert into t1 values (NULL,5),(NULL,6); 19select * from t1; 20 21sync_slave_with_master; 22select * from t1; 23connection master; 24 25drop table t1; 26 27set @@session.auto_increment_increment=100, @@session.auto_increment_offset=10; 28show variables like "auto_inc%"; 29 30eval 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 32insert into t1 values (NULL),(5),(NULL); 33insert into t1 values (250),(NULL); 34select * from t1; 35insert into t1 values (1000); 36set @@insert_id=400; 37insert into t1 values(NULL),(NULL); 38select * from t1; 39 40sync_slave_with_master; 41select * from t1; 42connection master; 43drop table t1; 44 45# 46# Same test with innodb (as the innodb code is a bit different) 47# 48eval 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 50insert into t1 values (NULL),(5),(NULL); 51insert into t1 values (250),(NULL); 52select * from t1; 53insert into t1 values (1000); 54set @@insert_id=400; 55insert into t1 values(NULL),(NULL); 56select * from t1; 57 58sync_slave_with_master; 59select * from t1; 60connection master; 61drop table t1; 62 63set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1; 64eval 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 66insert into t1 values (NULL),(5),(NULL),(NULL); 67insert into t1 values (500),(NULL),(502),(NULL),(NULL); 68select * from t1; 69set @@insert_id=600; 70--error ER_DUP_ENTRY 71insert into t1 values(600),(NULL),(NULL); 72set @@insert_id=600; 73insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL); 74select * from t1; 75 76sync_slave_with_master; 77select * from t1; 78connection master; 79drop table t1; 80 81# 82# Test that auto-increment works when slave has rows in the table 83# 84set @@session.auto_increment_increment=10, @@session.auto_increment_offset=1; 85 86eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2; 87 88sync_slave_with_master; 89insert into t1 values(2),(12),(22),(32),(42); 90connection master; 91 92insert into t1 values (NULL),(NULL); 93insert into t1 values (3),(NULL),(NULL); 94select * from t1; 95 96sync_slave_with_master; 97select * 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 105connection master; 106drop table t1; 107create table t1 (a tinyint not null auto_increment primary key) engine=myisam; 108insert into t1 values(103); 109set auto_increment_increment=11; 110set auto_increment_offset=4; 111insert into t1 values(null); 112insert into t1 values(null); 113--error 167 114insert into t1 values(null); 115select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; 116 117# same but with a larger value 118create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam; 119set auto_increment_increment=10; 120set auto_increment_offset=1; 121set insert_id=1000; 122insert into t2 values(10); 123--error 167 124insert into t2 values(null); 125select 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 128create table t3 like t1; 129set auto_increment_increment=1000; 130set auto_increment_offset=700; 131--error 167 132insert into t3 values(null); 133select * from t3 order by a; 134sync_slave_with_master; 135select * from t1 order by a; 136select * from t2 order by a; 137select * from t3 order by a; 138 139connection master; 140 141drop table t1,t2,t3; 142sync_slave_with_master; 143 144# 145# BUG#41986 Replication slave does not pick up proper AUTO_INCREMENT value for Innodb tables 146# 147connection master; 148set auto_increment_increment=1; 149set auto_increment_offset=1; 150CREATE TABLE t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb; 151INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 152show create table t1; 153 154sync_slave_with_master; 155show create table t1; 156 157connection master; 158drop 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# 166source include/rpl_reset.inc; 167 168connection master; 169--disable_warnings 170DROP TABLE IF EXISTS t1; 171DROP TABLE IF EXISTS t2; 172--enable_warnings 173 174eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type; 175eval CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type2; 176SET SQL_MODE=''; 177# Value of the id will be 1; 178INSERT INTO t1 VALUES(NULL); 179INSERT INTO t2 VALUES(NULL); 180SELECT * FROM t1; 181SELECT * FROM t2; 182# Value of the id will be 2; 183INSERT INTO t1 VALUES(); 184INSERT INTO t2 VALUES(); 185SELECT * FROM t1; 186SELECT * 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. 189INSERT INTO t1 VALUES(0); 190INSERT INTO t2 VALUES(0); 191SELECT * FROM t1; 192SELECT * FROM t2; 193 194SET 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. 197INSERT INTO t1 VALUES(0); 198INSERT INTO t2 VALUES(0); 199SELECT * FROM t1; 200SELECT * FROM t2; 201 202INSERT INTO t1 VALUES(4); 203INSERT INTO t2 VALUES(4); 204FLUSH LOGS; 205sync_slave_with_master; 206 207let $diff_tables= master:t1, slave:t1; 208source include/diff_tables.inc; 209 210let $diff_tables= master:t2, slave:t2; 211source include/diff_tables.inc; 212 213connection master; 214DROP TABLE t1; 215DROP TABLE t2; 216sync_slave_with_master; 217 218connection master; 219let $MYSQLD_DATADIR= `SELECT @@DATADIR`; 220--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL test 221sync_slave_with_master; 222 223let $diff_tables= master:t1, slave:t1; 224source include/diff_tables.inc; 225 226let $diff_tables= master:t2, slave:t2; 227source include/diff_tables.inc; 228 229# End cleanup 230--connection master 231DROP TABLE t1; 232DROP TABLE t2; 233SET SQL_MODE=''; 234sync_slave_with_master; 235 236# 237# Bug#54201: "SET INSERT_ID" event must be ignored if corresponding event is 238# ignored. 239# 240connection master; 241 242CREATE TABLE t1(s VARCHAR(10)) ENGINE=myisam; 243# -slave.opt has --replicate-ignore-table=test.t_ignored1 244CREATE TABLE t_ignored1(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam; 245call 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"); 246sync_slave_with_master; 247 248connection slave; 249 250CREATE TABLE test.slave_only(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam; 251INSERT INTO slave_only VALUES(NULL); 252CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO slave_only VALUES(NULL); 253 254connection master; 255 256INSERT INTO t_ignored1 VALUES(NULL); 257INSERT INTO t1 VALUES('s'); 258UPDATE 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 262sync_slave_with_master; 263connection slave; 264SELECT * FROM t1; 265 266connection master; 267CREATE TABLE t_ignored2(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam; 268sync_slave_with_master; 269 270connection slave; 271STOP SLAVE; 272# Ignore the next INSERT into t_ignored2 and the INSERT_ID event just before it. 273SET GLOBAL sql_slave_skip_counter = 2; 274START SLAVE; 275 276connection master; 277INSERT INTO t_ignored2 VALUES(NULL); 278UPDATE t1 SET s='s2'; 279sync_slave_with_master; 280 281connection slave; 282SELECT * FROM t1; 283SHOW TABLES LIKE 't\_ignored_'; 284SELECT * FROM t_ignored2; 285DROP TABLE slave_only; 286 287connection master; 288DROP TABLE t1; 289DROP TABLE t_ignored1; 290DROP 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# 297connection master; 298CREATE TABLE t1 (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, data INT) ENGINE=innodb; 299 300BEGIN; 301--echo # Set sql_mode with NO_AUTO_VALUE_ON_ZERO for allowing 302--echo # zero to fill the auto_increment field. 303SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; 304INSERT 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. 307SET SQL_MODE=0; 308COMMIT; 309SELECT * FROM t1; 310sync_slave_with_master; 311SELECT * FROM t1; 312 313connection master; 314DROP TABLE t1; 315sync_slave_with_master; 316 317--source include/rpl_end.inc 318