Debian Codegraph @

mysql-test/suite/ndb/t/ndb_auto_increment.test

raw
1 -- source include/have_multi_ndb.inc
2
3 --disable_warnings
4 connection server1;
5 DROP TABLE IF EXISTS t1,t2;
6 connection server2;
7 DROP TABLE IF EXISTS t1;
8 set @old_auto_increment_offset = @@session.auto_increment_offset;
9 set @old_auto_increment_increment = @@session.auto_increment_increment;
10 set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz;
11 connection server1;
12 --enable_warnings
13
14 set @old_auto_increment_offset = @@session.auto_increment_offset;
15 set @old_auto_increment_increment = @@session.auto_increment_increment;
16 set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz;
17
18 flush status;
19
20 create table t1 (a int not null auto_increment primary key) engine ndb;
21
22 # Step 1: Verify simple insert
23 insert into t1 values (NULL);
24 select * from t1 order by a;
25
26 # Step 2: Verify simple update with higher than highest value causes
27 # next insert to use updated_value + 1
28 update t1 set a = 5 where a = 1;
29 insert into t1 values (NULL);
30 select * from t1 order by a;
31
32 # Step 3: Verify insert that inserts higher than highest value causes
33 # next insert to use inserted_value + 1
34 insert into t1 values (7);
35 insert into t1 values (NULL);
36 select * from t1 order by a;
37
38 # Step 4: Verify that insert into hole, lower than highest value doesn't
39 # affect next insert
40 insert into t1 values (2);
41 insert into t1 values (NULL);
42 select * from t1 order by a;
43
44 # Step 5: Verify that update into hole, lower than highest value doesn't
45 # affect next insert
46 update t1 set a = 4 where a = 2;
47 insert into t1 values (NULL);
48 select * from t1 order by a;
49
50 # Step 6: Verify that delete of highest value doesn't cause the next
51 # insert to reuse this value
52 delete from t1 where a = 10;
53 insert into t1 values (NULL);
54 select * from t1 order by a;
55
56 # Step 7: Verify that REPLACE has the same effect as INSERT
57 replace t1 values (NULL);
58 select * from t1 order by a;
59 replace t1 values (15);
60 select * from t1 order by a;
61 replace into t1 values (NULL);
62 select * from t1 order by a;
63
64 # Step 8: Verify that REPLACE has the same effect as UPDATE
65 replace t1 values (15);
66 select * from t1 order by a;
67
68 # Step 9: Verify that IGNORE doesn't affect auto_increment
69 insert ignore into t1 values (NULL);
70 select * from t1 order by a;
71 insert ignore into t1 values (15), (NULL);
72 select * from t1 order by a;
73
74 # Step 10: Verify that on duplicate key as UPDATE behaves as an
75 # UPDATE
76 insert into t1 values (15)
77 on duplicate key update a = 20;
78 insert into t1 values (NULL);
79 select * from t1 order by a;
80
81 # Step 11: Verify that on duplicate key as INSERT behaves as INSERT
82 insert into t1 values (NULL) on duplicate key update a = 30;
83 select * from t1 order by a;
84 insert into t1 values (30) on duplicate key update a = 40;
85 select * from t1 order by a;
86
87 #Step 12: Vefify INSERT IGNORE (bug#32055)
88 insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL);
89 select * from t1 order by a;
90 drop table t1;
91
92 #Step 13: Verify auto_increment of unique key
93 create table t1 (a int not null primary key,
94 b int not null unique auto_increment) engine ndb;
95 insert into t1 values (1, NULL);
96 insert into t1 values (3, NULL);
97 update t1 set b = 3 where a = 3;
98 insert into t1 values (4, NULL);
99 select * from t1 order by a;
100 drop table t1;
101
102 #Step 14: Verify that auto_increment_increment and auto_increment_offset
103 # work as expected
104
105 CREATE TABLE t1 (
106 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
107 b INT NOT NULL,
108 c INT NOT NULL UNIQUE
109 ) ENGINE=NDBCLUSTER;
110
111 CREATE TABLE t2 (
112 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
113 b INT NOT NULL,
114 c INT NOT NULL UNIQUE
115 ) ENGINE=MYISAM;
116
117 SET @@session.auto_increment_increment=10;
118 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
119 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
120 SELECT * FROM t1 ORDER BY pk;
121 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
122 TRUNCATE t1;
123 TRUNCATE t2;
124 SET @@session.auto_increment_offset=5;
125 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
126 INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6);
127 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
128 INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6);
129 SELECT * FROM t1 ORDER BY pk;
130 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
131 TRUNCATE t1;
132 TRUNCATE t2;
133 SET @@session.auto_increment_increment=2;
134 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
135 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
136 SELECT * FROM t1 ORDER BY pk;
137 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
138 DROP TABLE t1, t2;
139
140 CREATE TABLE t1 (
141 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
142 b INT NOT NULL,
143 c INT NOT NULL UNIQUE
144 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7;
145
146 CREATE TABLE t2 (
147 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
148 b INT NOT NULL,
149 c INT NOT NULL UNIQUE
150 ) ENGINE=MYISAM AUTO_INCREMENT = 7;
151
152 SET @@session.auto_increment_offset=1;
153 SET @@session.auto_increment_increment=1;
154 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
155 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
156 SELECT * FROM t1 ORDER BY pk;
157 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
158 DROP TABLE t1, t2;
159
160 CREATE TABLE t1 (
161 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
162 b INT NOT NULL,
163 c INT NOT NULL UNIQUE
164 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3;
165
166 CREATE TABLE t2 (
167 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
168 b INT NOT NULL,
169 c INT NOT NULL UNIQUE
170 ) ENGINE=MYISAM AUTO_INCREMENT = 3;
171
172 SET @@session.auto_increment_offset=5;
173 SET @@session.auto_increment_increment=10;
174 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
175 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
176 SELECT * FROM t1 ORDER BY pk;
177 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
178 DROP TABLE t1, t2;
179
180 CREATE TABLE t1 (
181 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
182 b INT NOT NULL,
183 c INT NOT NULL UNIQUE
184 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7;
185
186 CREATE TABLE t2 (
187 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
188 b INT NOT NULL,
189 c INT NOT NULL UNIQUE
190 ) ENGINE=MYISAM AUTO_INCREMENT = 7;
191
192 SET @@session.auto_increment_offset=5;
193 SET @@session.auto_increment_increment=10;
194 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
195 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
196 SELECT * FROM t1 ORDER BY pk;
197 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
198 DROP TABLE t1, t2;
199
200 CREATE TABLE t1 (
201 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
202 b INT NOT NULL,
203 c INT NOT NULL UNIQUE
204 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5;
205
206 CREATE TABLE t2 (
207 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
208 b INT NOT NULL,
209 c INT NOT NULL UNIQUE
210 ) ENGINE=MYISAM AUTO_INCREMENT = 5;
211
212 SET @@session.auto_increment_offset=5;
213 SET @@session.auto_increment_increment=10;
214 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
215 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
216 SELECT * FROM t1 ORDER BY pk;
217 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
218 DROP TABLE t1, t2;
219
220 CREATE TABLE t1 (
221 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
222 b INT NOT NULL,
223 c INT NOT NULL UNIQUE
224 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100;
225
226 CREATE TABLE t2 (
227 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
228 b INT NOT NULL,
229 c INT NOT NULL UNIQUE
230 ) ENGINE=MYISAM AUTO_INCREMENT = 100;
231
232 SET @@session.auto_increment_offset=5;
233 SET @@session.auto_increment_increment=10;
234 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
235 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
236 SELECT * FROM t1 ORDER BY pk;
237 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
238 DROP TABLE t1, t2;
239
240 #Step 15: Now verify that behaviour on multiple MySQL Servers behave
241 # properly. Start by dropping table and recreating it to start
242 # counters and id caches from zero again.
243 --disable_warnings
244 connection server2;
245 SET @@session.auto_increment_offset=1;
246 SET @@session.auto_increment_increment=1;
247 set ndb_autoincrement_prefetch_sz = 32;
248 drop table if exists t1;
249 connection server1;
250 SET @@session.auto_increment_offset=1;
251 SET @@session.auto_increment_increment=1;
252 set ndb_autoincrement_prefetch_sz = 32;
253 --enable_warnings
254
255
256 create table t1 (a int not null auto_increment primary key) engine ndb;
257 # Basic test, ensure that the second server gets a new range.
258 #Generate record with key = 1
259 insert into t1 values (NULL);
260 connection server2;
261 #Generate record with key = 33
262 insert into t1 values (NULL);
263 connection server1;
264 select * from t1 order by a;
265
266 #This insert should not affect the range of the second server
267 insert into t1 values (20);
268 connection server2;
269 insert into t1 values (NULL);
270 select * from t1 order by a;
271
272 connection server1;
273 #This insert should remove cached values but also skip values already
274 #taken by server2, given that there is no method of communicating with
275 #the other server it should also cause a conflict
276 connection server1;
277
278 insert into t1 values (35);
279 insert into t1 values (NULL);
280 connection server2;
281 --error ER_DUP_ENTRY
282 insert into t1 values (NULL);
283 select * from t1 order by a;
284
285 insert into t1 values (100);
286 insert into t1 values (NULL);
287 connection server1;
288 insert into t1 values (NULL);
289 select * from t1 order by a;
290
291 set auto_increment_offset = @old_auto_increment_offset;
292 set auto_increment_increment = @old_auto_increment_increment;
293 set ndb_autoincrement_prefetch_sz = 1;
294
295 drop table t1;
296
297 connection server2;
298 set auto_increment_offset = @old_auto_increment_offset;
299 set auto_increment_increment = @old_auto_increment_increment;
300 set ndb_autoincrement_prefetch_sz = 1;
301
302 # bug#46712 Auto_increment work incorrectly when using triggers and NDB Cluster
303 #
304 # Testing that auto_increment values are set correctly when inserting from
305 # multiple SQL-nodes
306
307 connection server1;
308
309 CREATE TABLE `t1` (
310 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
311 `k` int(10) unsigned NOT NULL DEFAULT '0',
312 `c` char(120) NOT NULL DEFAULT '',
313 `pad` char(60) NOT NULL DEFAULT '',
314 PRIMARY KEY (`id`),
315 KEY `k` (`k`)
316 ) ENGINE=ndbcluster;
317
318 CREATE TABLE `t2` (
319 `evend_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
320 `timestamp` int(11) NOT NULL,
321 `server_id` int(11) NOT NULL,
322 PRIMARY KEY (`evend_id`)
323 ) ENGINE=ndbcluster;
324
325
326 insert into t1 values (null,1,'',''),(null,2,'','');
327
328 DELIMITER |;
329
330 CREATE TRIGGER tr1
331 AFTER UPDATE ON t1
332 FOR EACH ROW
333 BEGIN
334 insert into t2(timestamp, server_id) values(UNIX_TIMESTAMP(),@@global.server_id);
335 end;
336 |
337
338 DELIMITER ;|
339
340 connection server2;
341
342 DELIMITER |;
343
344 CREATE TRIGGER tr1
345 AFTER UPDATE ON t1
346 FOR EACH ROW
347 BEGIN
348 insert into t2(timestamp, server_id) values(UNIX_TIMESTAMP(),@@global.server_id);
349 end;
350 |
351
352 DELIMITER ;|
353
354 connection server1;
355 update t1 set c='foobar' where id=1;
356 connection server2;
357 update t1 set c='foobar' where id=1;
358 connection server1;
359 update t1 set c='foobar' where id=1;
360 connection server2;
361 update t1 set c='foobar' where id=1;
362 connection server1;
363 update t1 set c='foobar' where id=1;
364 connection server2;
365 update t1 set c='foobar' where id=1;
366 connection server1;
367 update t1 set c='foobar' where id=1;
368 connection server2;
369 update t1 set c='foobar' where id=1;
370 connection server1;
371 select evend_id,server_id from t2 order by evend_id;
372
373 drop trigger tr1;
374 drop table t1, t2;
375
376 connection server2;
377 --disable_warnings
378 drop trigger if exists tr1;
379 --enable_warnings
380
381 connection server1;
382
383 #
384 # Bug #47865 SHOW CREATE TABLE does not show the current auto_increment number for ndb tables
385 #
386
387 create table t1 (a int primary key auto_increment, b int) engine=ndb;
388
389 insert into t1 values (null,1),(null,2),(null,3);
390
391 --disable_warnings
392 show create table t1;
393 --enable_warnings
394
395 drop table t1;
396
397 #
398 # Bug #50247 ALTER TABLE cannot change auto_increment
399 #
400 create table t1 (a int primary key auto_increment, b int) auto_increment=5
401 engine=ndb;
402
403 alter table t1 auto_increment=32000000;
404
405 --disable_warnings
406 show create table t1;
407 --enable_warnings
408
409 drop table t1;
410
411 #
412 # Bug #46985 Got error 4350 'Transaction already aborted' from NDBCLUSTER
413 #
414 # This bug was a configuration issue, but the testcase revealed another
415 # bug whereauto_increment was not handled correctly for INSERT IGNORE
416
417 CREATE TABLE t1 (
418 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
419 data binary(16) NOT NULL,
420 PRIMARY KEY (id),
421 UNIQUE KEY uk_t1_data (data)
422 ) ENGINE = NDB;
423
424 INSERT IGNORE INTO t1 (data) VALUES (6),(6),(6),(6),(6),(6),(6);
425
426 SELECT id FROM t1;
427
428 DELETE from t1;
429
430 INSERT IGNORE INTO t1 (data) VALUES (6),(6),(6),(6),(6),(6),(6);
431
432 SELECT id from t1;
433
434 DROP TABLE t1;
435
436 #
437 # Bug #13731134 AUTO-INC COUNTER IS NOT UPDATED WITH EXPLICIT @@INSERT_ID SET
438 #
439
440 create table t1 (a serial) engine ndb;
441
442 set @@insert_id=1;
443 insert into t1 values(null);
444
445 connection server2;
446
447 insert into t1 values(null);
448 insert into t1 values(null);
449 insert into t1 values(null);
450 set @@insert_id=17;
451 insert into t1 values(null);
452 insert into t1 values(null);
453 insert into t1 values(null);
454 select * from t1 order by a;
455
456 set @@insert_id=1;
457 --error ER_DUP_ENTRY
458 insert into t1 values(null);
459
460 set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz;
461 connection server1;
462 set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz;
463 drop table t1;
464
465 #
466 # Bug 30316314 INCREASE AUTO INCREMENT PREFETCH SIZE
467 #
468 create table t1 (a tinyint not null auto_increment primary key) engine ndb;
469 drop table t1;