Debian Codegraph @

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

raw
1 --echo # Replicating simple inserts
2 --echo [Connection Master]
3 --connection master
4
5 # Check if lock mode is changed on master
6 SELECT @@global.innodb_autoinc_lock_mode;
7
8 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
9 INSERT INTO t1 VALUES (NULL,10),
10 (NULL,20),
11 (NULL,30);
12
13 --source include/sync_slave_sql_with_master.inc
14 --echo [Connection Slave]
15
16 # Check if lock mode is changed on slave
17 SELECT @@global.innodb_autoinc_lock_mode;
18
19 SELECT * FROM t1;
20
21 --let $diff_tables = master:t1, slave:t1
22 --source include/diff_tables.inc
23
24 --echo [Connection Master]
25 --connection master
26
27 # Check with insert on duplicate key update
28
29 INSERT INTO t1 VALUES (3,35)
30 ON DUPLICATE KEY UPDATE aa = aa+1;
31
32 --source include/sync_slave_sql_with_master.inc
33 --echo [Connection Slave]
34
35 SELECT * FROM t1;
36
37 --let $diff_tables = master:t1, slave:t1
38 --source include/diff_tables.inc
39
40
41 --echo [Connection Master]
42 --connection master
43
44 # Change the auto_increment_offset and auto_increment_increment values
45 SET @default_auto_increment_offset = @@auto_increment_offset;
46 SET @default_auto_increment_increment = @@auto_increment_increment;
47 SET auto_increment_offset = 5;
48 SET auto_increment_increment = 15;
49
50 INSERT INTO t1 VALUES (NULL,40),
51 (NULL,50),
52 (NULL,60),
53 (NULL,70),
54 (NULL,80);
55
56 --source include/sync_slave_sql_with_master.inc
57 --echo [Connection Slave]
58
59 SELECT * FROM t1;
60
61 --let $diff_tables = master:t1, slave:t1
62 --source include/diff_tables.inc
63
64 --echo [Conenction Master]
65 --connection master
66
67 # Delete records
68
69 DELETE FROM t1 WHERE aa IN (40,60);
70
71 --source include/sync_slave_sql_with_master.inc
72 --echo [Connection Slave]
73
74 SELECT * FROM t1;
75
76 --let $diff_tables = master:t1, slave:t1
77 --source include/diff_tables.inc
78
79 --echo [Connection Master]
80 --connection master
81
82 SET @@auto_increment_offset = @default_auto_increment_offset;
83 SET @@auto_increment_increment= @default_auto_increment_increment;
84
85 DROP TABLE t1;
86
87 --source include/sync_slave_sql_with_master.inc
88 --echo [Connection Slave]
89
90 --error ER_NO_SUCH_TABLE
91 SELECT * FROM t1;
92
93 --echo # Replicating bulk inserts
94 --echo [Connection Master]
95 --connection master
96
97 CREATE TABLE t1(aa INT);
98 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
99 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
100 --disable_warnings
101 INSERT INTO t2(aa) SELECT * FROM t1;
102 INSERT INTO t2(aa) SELECT * FROM t1;
103 --enable_warnings
104
105 --source include/sync_slave_sql_with_master.inc
106 --echo [Connection Slave]
107
108 SELECT COUNT(*) FROM t2;
109
110 --let $diff_tables = master:t2, slave:t2
111 --source include/diff_tables.inc
112
113 --echo [Connection Master]
114 --connection master
115
116 # Bulk inserts with auto increment offset
117
118 SET auto_increment_offset = 4;
119 SET auto_increment_increment = 16;
120
121 --disable_warnings
122 INSERT INTO t2(aa) SELECT * FROM t1;
123 INSERT INTO t2(aa) SELECT * FROM t1;
124 --enable_warnings
125
126 --source include/sync_slave_sql_with_master.inc
127 --echo [Connection Slave]
128
129 SELECT COUNT(*) FROM t2;
130
131 --let $diff_tables = master:t2, slave:t2
132 --source include/diff_tables.inc
133
134 --echo [Connection Master]
135 --connection master
136
137 SET @@auto_increment_offset = @default_auto_increment_offset;
138 SET @@auto_increment_increment = @default_auto_increment_increment;
139
140 DROP TABLE t1;
141 DROP TABLE t2;
142
143 --source include/sync_slave_sql_with_master.inc
144 --echo [Connection Slave]
145
146 --error ER_NO_SUCH_TABLE
147 SELECT * FROM t2;
148
149
150 --echo # Replicating mixed mode inserts
151 --echo [Connection Master]
152 --connection master
153
154 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
155 INSERT INTO t1 VALUES(NULL,20),
156 (20,40),
157 (NULL,60),
158 (NULL,80),
159 (30,100),
160 (NULL,120);
161
162 --source include/sync_slave_sql_with_master.inc
163 --echo [Connection Slave]
164
165 SELECT COUNT(*) FROM t1;
166
167 --let $diff_tables = master:t1, slave:t1
168 --source include/diff_tables.inc
169
170 --echo [Connection Master]
171 --connection master
172
173 DROP TABLE t1;
174
175 --source include/sync_slave_sql_with_master.inc
176 --echo [Connection Slave]
177
178 --error ER_NO_SUCH_TABLE
179 SELECT * FROM t1;