1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
#
# Test of auto_increment with offset
#
-- source include/not_ndb_default.inc
-- source include/master-slave.inc
#
# 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;
--source include/sync_slave_sql_with_master.inc
select * from t1;
connection master;
drop table t1;
#
# 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;
--source include/sync_slave_sql_with_master.inc
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;
--enable_warnings
eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type;
SET SQL_MODE='';
# Value of the id will be 1;
INSERT INTO t1 VALUES(NULL);
SELECT * FROM t1;
# Value of the id will be 2;
INSERT INTO t1 VALUES();
SELECT * FROM t1;
# 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);
SELECT * FROM t1;
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);
SELECT * FROM t1;
INSERT INTO t1 VALUES(4);
FLUSH LOGS;
--source include/sync_slave_sql_with_master.inc
let $diff_tables= master:t1, slave:t1;
source include/diff_tables.inc;
connection master;
DROP TABLE t1;
--source include/sync_slave_sql_with_master.inc
connection master;
let $MYSQLD_DATADIR= `SELECT @@DATADIR`;
# Keep original binlog file
--copy_file $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin-rpl-auto-increment.saved
# Stop slave
connection slave;
source include/stop_slave.inc;
RESET SLAVE;
RESET MASTER;
connection master;
# Force master to forget used GTIDs
RESET MASTER;
FLUSH LOGS;
connection slave;
source include/start_slave.inc;
connection master;
--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin-rpl-auto-increment.saved | $MYSQL test
--remove_file $MYSQLD_DATADIR/master-bin-rpl-auto-increment.saved
--source include/sync_slave_sql_with_master.inc
let $diff_tables= master:t1, slave:t1;
source include/diff_tables.inc;
# End cleanup
--connection master
DROP TABLE t1;
SET SQL_MODE='';
--source include/sync_slave_sql_with_master.inc
#
# 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;
--source include/sync_slave_sql_with_master.inc
SELECT * FROM t1;
connection master;
DROP TABLE t1;
--source include/sync_slave_sql_with_master.inc
#
# WL#5872 "avoid using global heap memory to remember autoincrement
# values for statement-based binlog".
#
connection master;
eval create table t1(a int auto_increment primary key) engine=$engine_type;
insert into t1 values (null),(null),(1025),(null);
--source include/sync_slave_sql_with_master.inc
select * from t1;
let $diff_tables= master:t1, slave:t1;
--source include/diff_tables.inc
connection master;
drop table t1;
--source include/sync_slave_sql_with_master.inc
--source include/rpl_end.inc
|
