Debian Codegraph @

mysql-test/suite/rpl/r/rpl_auto_increment_myisam.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
7 insert into t1 values (NULL,1),(NULL,2),(NULL,3);
8 select * from t1;
9 a b
10 3 1
11 4 2
12 5 3
13 include/sync_slave_sql_with_master.inc
14 select * from t1;
15 a b
16 3 1
17 4 2
18 5 3
19 drop table t1;
20 create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam;
21 insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4);
22 delete from t1 where b=4;
23 insert into t1 values (NULL,5),(NULL,6);
24 select * from t1;
25 a b
26 1 1
27 2 2
28 3 3
29 5 5
30 6 6
31 include/sync_slave_sql_with_master.inc
32 select * from t1;
33 a b
34 1 1
35 2 2
36 3 3
37 5 5
38 6 6
39 drop table t1;
40 set @@session.auto_increment_increment=100, @@session.auto_increment_offset=10;
41 show variables like "%auto_inc%";
42 Variable_name Value
43 auto_increment_increment 100
44 auto_increment_offset 10
45 create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
46 insert into t1 values (NULL),(5),(NULL);
47 insert into t1 values (250),(NULL);
48 select * from t1;
49 a
50 5
51 10
52 110
53 250
54 310
55 insert into t1 values (1000);
56 set @@insert_id=400;
57 insert into t1 values(NULL),(NULL);
58 select * from t1;
59 a
60 5
61 10
62 110
63 250
64 310
65 400
66 410
67 1000
68 include/sync_slave_sql_with_master.inc
69 select * from t1;
70 a
71 5
72 10
73 110
74 250
75 310
76 400
77 410
78 1000
79 drop table t1;
80 set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
81 create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
82 insert into t1 values (NULL),(5),(NULL),(NULL);
83 insert into t1 values (500),(NULL),(502),(NULL),(NULL);
84 select * from t1;
85 a
86 1
87 5
88 6
89 7
90 500
91 501
92 502
93 503
94 504
95 set @@insert_id=600;
96 insert into t1 values(600),(NULL),(NULL);
97 ERROR 23000: Duplicate entry '600' for key 't1.PRIMARY'
98 set @@insert_id=600;
99 insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL);
100 Warnings:
101 Warning 1062 Duplicate entry '600' for key 't1.PRIMARY'
102 Warning 1062 Duplicate entry '600' for key 't1.PRIMARY'
103 Warning 1062 Duplicate entry '600' for key 't1.PRIMARY'
104 select * from t1;
105 a
106 1
107 5
108 6
109 7
110 500
111 501
112 502
113 503
114 504
115 600
116 610
117 611
118 include/sync_slave_sql_with_master.inc
119 select * from t1;
120 a
121 1
122 5
123 6
124 7
125 500
126 501
127 502
128 503
129 504
130 600
131 610
132 611
133 drop table t1;
134 set @@session.auto_increment_increment=10, @@session.auto_increment_offset=1;
135 create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
136 include/sync_slave_sql_with_master.inc
137 insert into t1 values(2),(12),(22),(32),(42);
138 insert into t1 values (NULL),(NULL);
139 insert into t1 values (3),(NULL),(NULL);
140 select * from t1;
141 a
142 1
143 3
144 11
145 21
146 31
147 include/sync_slave_sql_with_master.inc
148 select * from t1;
149 a
150 1
151 2
152 3
153 11
154 12
155 21
156 22
157 31
158 32
159 42
160 drop table t1;
161 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
162 create table t1 (a tinyint not null auto_increment primary key) engine=myisam;
163 insert into t1 values(103);
164 set auto_increment_increment=11;
165 set auto_increment_offset=4;
166 insert into t1 values(null);
167 insert into t1 values(null);
168 insert into t1 values(null);
169 ERROR 23000: Duplicate entry '125' for key 't1.PRIMARY'
170 select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a;
171 a mod(a-@@auto_increment_offset,@@auto_increment_increment)
172 103 0
173 114 0
174 125 0
175 create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam;
176 set auto_increment_increment=10;
177 set auto_increment_offset=1;
178 set insert_id=1000;
179 insert into t2 values(null);
180 Warnings:
181 Warning 1264 Out of range value for column 'a' at row 1
182 select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a;
183 a mod(a-@@auto_increment_offset,@@auto_increment_increment)
184 251 0
185 create table t3 like t1;
186 set auto_increment_increment=1000;
187 set auto_increment_offset=700;
188 insert into t3 values(null);
189 Warnings:
190 Warning 1264 Out of range value for column 'a' at row 1
191 select * from t3 order by a;
192 a
193 127
194 include/sync_slave_sql_with_master.inc
195 select * from t1 order by a;
196 a
197 103
198 114
199 125
200 select * from t2 order by a;
201 a
202 251
203 select * from t3 order by a;
204 a
205 127
206 drop table t1,t2,t3;
207 SET sql_mode = default;
208 include/sync_slave_sql_with_master.inc
209 include/rpl_reset.inc
210 DROP TABLE IF EXISTS t2;
211 CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
212 SET SQL_MODE='';
213 INSERT INTO t2 VALUES(NULL);
214 SELECT * FROM t2;
215 id
216 700
217 INSERT INTO t2 VALUES();
218 SELECT * FROM t2;
219 id
220 700
221 1700
222 INSERT INTO t2 VALUES(0);
223 SELECT * FROM t2;
224 id
225 700
226 1700
227 2700
228 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
229 INSERT INTO t2 VALUES(0);
230 SELECT * FROM t2;
231 id
232 0
233 700
234 1700
235 2700
236 INSERT INTO t2 VALUES(4);
237 FLUSH LOGS;
238 include/sync_slave_sql_with_master.inc
239 include/diff_tables.inc [master:t2, slave:t2]
240 DROP TABLE t2;
241 include/sync_slave_sql_with_master.inc
242 include/stop_slave.inc
243 RESET SLAVE;
244 Warnings:
245 Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
246 RESET MASTER;
247 RESET MASTER;
248 FLUSH LOGS;
249 include/start_slave.inc
250 include/sync_slave_sql_with_master.inc
251 include/diff_tables.inc [master:t2, slave:t2]
252 DROP TABLE t2;
253 SET SQL_MODE='';
254 include/sync_slave_sql_with_master.inc
255 include/rpl_end.inc