diff options
Diffstat (limited to 'mysql-test/suite/rpl/r/rpl_auto_increment.result')
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_auto_increment.result | 399 |
1 files changed, 399 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/r/rpl_auto_increment.result b/mysql-test/suite/rpl/r/rpl_auto_increment.result new file mode 100644 index 00000000..fcbce494 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_auto_increment.result @@ -0,0 +1,399 @@ +include/master-slave.inc +[connection master] +create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3; +insert into t1 values (NULL,1),(NULL,2),(NULL,3); +select * from t1; +a b +12 1 +22 2 +32 3 +connection slave; +select * from t1; +a b +12 1 +22 2 +32 3 +connection master; +drop table t1; +create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam; +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; +a b +1 1 +2 2 +3 3 +22 5 +32 6 +connection slave; +select * from t1; +a b +1 1 +2 2 +3 3 +22 5 +32 6 +connection master; +drop table t1; +set @@session.auto_increment_increment=100, @@session.auto_increment_offset=10; +show variables like "auto_inc%"; +Variable_name Value +auto_increment_increment 100 +auto_increment_offset 10 +create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; +insert into t1 values (NULL),(5),(NULL); +insert into t1 values (250),(NULL); +select * from t1; +a +5 +10 +110 +250 +310 +insert into t1 values (1000); +set @@insert_id=400; +insert into t1 values(NULL),(NULL); +select * from t1; +a +5 +10 +110 +250 +310 +400 +410 +1000 +connection slave; +select * from t1; +a +5 +10 +110 +250 +310 +400 +410 +1000 +connection master; +drop table t1; +create table t1 (a int not null auto_increment, primary key (a)) engine=innodb; +insert into t1 values (NULL),(5),(NULL); +insert into t1 values (250),(NULL); +select * from t1; +a +5 +10 +110 +250 +310 +insert into t1 values (1000); +set @@insert_id=400; +insert into t1 values(NULL),(NULL); +select * from t1; +a +5 +10 +110 +250 +310 +400 +410 +1000 +connection slave; +select * from t1; +a +5 +10 +110 +250 +310 +400 +410 +1000 +connection master; +drop table t1; +set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1; +create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; +insert into t1 values (NULL),(5),(NULL),(NULL); +insert into t1 values (500),(NULL),(502),(NULL),(NULL); +select * from t1; +a +1 +5 +6 +7 +500 +501 +502 +503 +504 +set @@insert_id=600; +insert into t1 values(600),(NULL),(NULL); +ERROR 23000: Duplicate entry '600' for key 'PRIMARY' +set @@insert_id=600; +insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL); +Warnings: +Warning 1062 Duplicate entry '600' for key 'PRIMARY' +Warning 1062 Duplicate entry '600' for key 'PRIMARY' +Warning 1062 Duplicate entry '600' for key 'PRIMARY' +select * from t1; +a +1 +5 +6 +7 +500 +501 +502 +503 +504 +600 +610 +611 +connection slave; +select * from t1; +a +1 +5 +6 +7 +500 +501 +502 +503 +504 +600 +610 +611 +connection master; +drop table t1; +set @@session.auto_increment_increment=10, @@session.auto_increment_offset=1; +create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; +connection slave; +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; +a +1 +3 +11 +21 +31 +connection slave; +select * from t1; +a +1 +2 +3 +11 +12 +21 +22 +31 +32 +42 +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); +insert into t1 values(null); +ERROR 22003: Out of range value for column 'a' at row 1 +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; +a mod(a-@@auto_increment_offset,@@auto_increment_increment) +103 0 +114 0 +125 0 +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); +insert into t2 values(null); +ERROR 22003: Out of range value for column 'a' at row 1 +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a; +a mod(a-@@auto_increment_offset,@@auto_increment_increment) +10 9 +create table t3 like t1; +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t3 values(null); +ERROR 22003: Out of range value for column 'a' at row 1 +select * from t3 order by a; +a +connection slave; +select * from t1 order by a; +a +103 +114 +125 +select * from t2 order by a; +a +10 +select * from t3 order by a; +a +connection master; +drop table t1,t2,t3; +connection slave; +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` mediumint(9) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection slave; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` mediumint(9) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master; +drop table t1; +include/rpl_reset.inc +connection master; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb; +CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam; +SET SQL_MODE=''; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t2 VALUES(NULL); +SELECT * FROM t1; +id +1 +SELECT * FROM t2; +id +1 +INSERT INTO t1 VALUES(); +INSERT INTO t2 VALUES(); +SELECT * FROM t1; +id +1 +2 +SELECT * FROM t2; +id +1 +2 +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(0); +SELECT * FROM t1; +id +1 +2 +3 +SELECT * FROM t2; +id +1 +2 +3 +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(0); +SELECT * FROM t1; +id +0 +1 +2 +3 +SELECT * FROM t2; +id +0 +1 +2 +3 +INSERT INTO t1 VALUES(4); +INSERT INTO t2 VALUES(4); +FLUSH LOGS; +connection slave; +include/diff_tables.inc [master:t1, slave:t1] +include/diff_tables.inc [master:t2, slave:t2] +connection master; +DROP TABLE t1; +DROP TABLE t2; +connection slave; +connection master; +connection slave; +include/diff_tables.inc [master:t1, slave:t1] +include/diff_tables.inc [master:t2, slave:t2] +connection master; +DROP TABLE t1; +DROP TABLE t2; +SET SQL_MODE=''; +connection slave; +connection master; +CREATE TABLE t1(s VARCHAR(10)) ENGINE=myisam; +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"); +connection slave; +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'; +connection slave; +connection slave; +SELECT * FROM t1; +s +s1 +connection master; +CREATE TABLE t_ignored2(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam; +connection slave; +connection slave; +STOP SLAVE; +SET GLOBAL sql_slave_skip_counter = 2; +START SLAVE; +connection master; +INSERT INTO t_ignored2 VALUES(NULL); +UPDATE t1 SET s='s2'; +connection slave; +connection slave; +SELECT * FROM t1; +s +s2 +SHOW TABLES LIKE 't\_ignored_'; +Tables_in_test (t\_ignored_) +t_ignored2 +SELECT * FROM t_ignored2; +id +DROP TABLE slave_only; +connection master; +DROP TABLE t1; +DROP TABLE t_ignored1; +DROP TABLE t_ignored2; +connection master; +CREATE TABLE t1 (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, data INT) ENGINE=innodb; +BEGIN; +# Set sql_mode with NO_AUTO_VALUE_ON_ZERO for allowing +# zero to fill the auto_increment field. +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; +INSERT INTO t1(id,data) VALUES(0,2); +# Resetting sql_mode without NO_AUTO_VALUE_ON_ZERO to +# affect the execution of the transaction on slave. +SET SQL_MODE=0; +COMMIT; +SELECT * FROM t1; +id data +0 2 +connection slave; +SELECT * FROM t1; +id data +0 2 +connection master; +DROP TABLE t1; +connection slave; +include/rpl_end.inc |