diff options
Diffstat (limited to 'mysql-test/suite/rpl/include/rpl_auto_increment.test')
| -rw-r--r-- | mysql-test/suite/rpl/include/rpl_auto_increment.test | 317 |
1 files changed, 317 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/include/rpl_auto_increment.test b/mysql-test/suite/rpl/include/rpl_auto_increment.test new file mode 100644 index 00000000..67286c37 --- /dev/null +++ b/mysql-test/suite/rpl/include/rpl_auto_increment.test @@ -0,0 +1,317 @@ +# +# Test of auto_increment with offset +# +-- source include/master-slave.inc + +eval create table t1 (a int not null auto_increment,b int, primary key (a)) engine=$engine_type2 auto_increment=3; +insert into t1 values (NULL,1),(NULL,2),(NULL,3); +select * from t1; + +sync_slave_with_master; +select * from t1; +connection master; +drop table t1; + +eval create table t1 (a int not null auto_increment,b int, primary key (a)) engine=$engine_type2; +insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4); +delete from t1 where b=4; +insert into t1 values (NULL,5),(NULL,6); +select * from t1; + +sync_slave_with_master; +select * from t1; +connection master; + +drop table t1; + +set @@session.auto_increment_increment=100, @@session.auto_increment_offset=10; +show variables like "auto_inc%"; + +eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2; +# Insert with 2 insert statements to get better testing of logging +insert into t1 values (NULL),(5),(NULL); +insert into t1 values (250),(NULL); +select * from t1; +insert into t1 values (1000); +set @@insert_id=400; +insert into t1 values(NULL),(NULL); +select * from t1; + +sync_slave_with_master; +select * from t1; +connection master; +drop table t1; + +# +# Same test with innodb (as the innodb code is a bit different) +# +eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type; +# Insert with 2 insert statements to get better testing of logging +insert into t1 values (NULL),(5),(NULL); +insert into t1 values (250),(NULL); +select * from t1; +insert into t1 values (1000); +set @@insert_id=400; +insert into t1 values(NULL),(NULL); +select * from t1; + +sync_slave_with_master; +select * from t1; +connection master; +drop table t1; + +set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1; +eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2; +# Insert with 2 insert statements to get better testing of logging +insert into t1 values (NULL),(5),(NULL),(NULL); +insert into t1 values (500),(NULL),(502),(NULL),(NULL); +select * from t1; +set @@insert_id=600; +--error ER_DUP_ENTRY +insert into t1 values(600),(NULL),(NULL); +set @@insert_id=600; +insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL); +select * from t1; + +sync_slave_with_master; +select * from t1; +connection master; +drop table t1; + +# +# Test that auto-increment works when slave has rows in the table +# +set @@session.auto_increment_increment=10, @@session.auto_increment_offset=1; + +eval create table t1 (a int not null auto_increment, primary key (a)) engine=$engine_type2; + +sync_slave_with_master; +insert into t1 values(2),(12),(22),(32),(42); +connection master; + +insert into t1 values (NULL),(NULL); +insert into t1 values (3),(NULL),(NULL); +select * from t1; + +sync_slave_with_master; +select * from t1; + +# Test for BUG#20524 "auto_increment_* not observed when inserting +# a too large value". When an autogenerated value was bigger than the +# maximum possible value of the field, it was truncated to that max +# possible value, without being "rounded down" to still honour +# auto_increment_* variables. + +connection master; +drop table t1; +create table t1 (a tinyint not null auto_increment primary key) engine=myisam; +insert into t1 values(103); +set auto_increment_increment=11; +set auto_increment_offset=4; +insert into t1 values(null); +insert into t1 values(null); +--error 167 +insert into t1 values(null); +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; + +# same but with a larger value +create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam; +set auto_increment_increment=10; +set auto_increment_offset=1; +set insert_id=1000; +insert into t2 values(10); +--error 167 +insert into t2 values(null); +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a; + +# An offset so big that even first value does not fit +create table t3 like t1; +set auto_increment_increment=1000; +set auto_increment_offset=700; +--error 167 +insert into t3 values(null); +select * from t3 order by a; +sync_slave_with_master; +select * from t1 order by a; +select * from t2 order by a; +select * from t3 order by a; + +connection master; + +drop table t1,t2,t3; +sync_slave_with_master; + +# +# BUG#41986 Replication slave does not pick up proper AUTO_INCREMENT value for Innodb tables +# +connection master; +set auto_increment_increment=1; +set auto_increment_offset=1; +CREATE TABLE t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb; +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +show create table t1; + +sync_slave_with_master; +show create table t1; + +connection master; +drop table t1; + +# +# BUG#45999 Row based replication fails when auto_increment field = 0. +# Store engine of Slaves auto-generates new sequence numbers for +# auto_increment fields if the values of them are 0. There is an inconsistency +# between slave and master. When MODE_NO_AUTO_VALUE_ON_ZERO are masters treat +# +source include/rpl_reset.inc; + +connection master; +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings + +eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type; +eval CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type2; +SET SQL_MODE=''; +# Value of the id will be 1; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t2 VALUES(NULL); +SELECT * FROM t1; +SELECT * FROM t2; +# Value of the id will be 2; +INSERT INTO t1 VALUES(); +INSERT INTO t2 VALUES(); +SELECT * FROM t1; +SELECT * FROM t2; +# Value of the id will be 3. The master treats 0 as NULL or empty because +# NO_AUTO_VALUE_ON_ZERO is not assign to SQL_MODE. +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(0); +SELECT * FROM t1; +SELECT * FROM t2; + +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; +# Value of the id will be 0. The master does not treat 0 as NULL or empty +# because NO_AUTO_VALUE_ON_ZERO has assigned to SQL_MODE. +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(0); +SELECT * FROM t1; +SELECT * FROM t2; + +INSERT INTO t1 VALUES(4); +INSERT INTO t2 VALUES(4); +FLUSH LOGS; +sync_slave_with_master; + +let $diff_tables= master:t1, slave:t1; +source include/diff_tables.inc; + +let $diff_tables= master:t2, slave:t2; +source include/diff_tables.inc; + +connection master; +DROP TABLE t1; +DROP TABLE t2; +sync_slave_with_master; + +connection master; +let $MYSQLD_DATADIR= `SELECT @@DATADIR`; +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL test +sync_slave_with_master; + +let $diff_tables= master:t1, slave:t1; +source include/diff_tables.inc; + +let $diff_tables= master:t2, slave:t2; +source include/diff_tables.inc; + +# End cleanup +--connection master +DROP TABLE t1; +DROP TABLE t2; +SET SQL_MODE=''; +sync_slave_with_master; + +# +# Bug#54201: "SET INSERT_ID" event must be ignored if corresponding event is +# ignored. +# +connection master; + +CREATE TABLE t1(s VARCHAR(10)) ENGINE=myisam; +# -slave.opt has --replicate-ignore-table=test.t_ignored1 +CREATE TABLE t_ignored1(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam; +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"); +sync_slave_with_master; + +connection slave; + +CREATE TABLE test.slave_only(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam; +INSERT INTO slave_only VALUES(NULL); +CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO slave_only VALUES(NULL); + +connection master; + +INSERT INTO t_ignored1 VALUES(NULL); +INSERT INTO t1 VALUES('s'); +UPDATE t1 SET s='s1'; + +# With Bug#54201, slave stops with duplicate key error here due to trigger +# using the insert_id from insert on master into t1_ignored1 +sync_slave_with_master; +connection slave; +SELECT * FROM t1; + +connection master; +CREATE TABLE t_ignored2(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam; +sync_slave_with_master; + +connection slave; +STOP SLAVE; +# Ignore the next INSERT into t_ignored2 and the INSERT_ID event just before it. +SET GLOBAL sql_slave_skip_counter = 2; +START SLAVE; + +connection master; +INSERT INTO t_ignored2 VALUES(NULL); +UPDATE t1 SET s='s2'; +sync_slave_with_master; + +connection slave; +SELECT * FROM t1; +SHOW TABLES LIKE 't\_ignored_'; +SELECT * FROM t_ignored2; +DROP TABLE slave_only; + +connection master; +DROP TABLE t1; +DROP TABLE t_ignored1; +DROP TABLE t_ignored2; + +# +# BUG#56662 +# The test verifies if the assertion of "next_insert_id == 0" +# will fail in ha_external_lock() function. +# +connection master; +CREATE TABLE t1 (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, data INT) ENGINE=innodb; + +BEGIN; +--echo # Set sql_mode with NO_AUTO_VALUE_ON_ZERO for allowing +--echo # zero to fill the auto_increment field. +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; +INSERT INTO t1(id,data) VALUES(0,2); +--echo # Resetting sql_mode without NO_AUTO_VALUE_ON_ZERO to +--echo # affect the execution of the transaction on slave. +SET SQL_MODE=0; +COMMIT; +SELECT * FROM t1; +sync_slave_with_master; +SELECT * FROM t1; + +connection master; +DROP TABLE t1; +sync_slave_with_master; + +--source include/rpl_end.inc |