Debian Codegraph @

mysql-test/suite/innodb/t/innodb-autoinc.test

raw
1 --disable_warnings
2 drop table if exists t1;
3 --enable_warnings
4
5 #
6 # Bug #34335
7 #
8 CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
9 INSERT INTO t1 VALUES (9223372036854775807, null);
10 -- error ER_DUP_ENTRY,1062
11 INSERT INTO t1 (c2) VALUES ('innodb');
12 SELECT * FROM t1;
13 DROP TABLE t1;
14 #
15 ## Test AUTOINC overflow
16 ##
17
18 # TINYINT
19 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
20 INSERT INTO t1 VALUES (127, null);
21 -- error ER_DUP_ENTRY,1062
22 INSERT INTO t1 (c2) VALUES ('innodb');
23 SELECT * FROM t1;
24 DROP TABLE t1;
25
26 CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
27 INSERT INTO t1 VALUES (255, null);
28 -- error ER_DUP_ENTRY,1062
29 INSERT INTO t1 (c2) VALUES ('innodb');
30 SELECT * FROM t1;
31 DROP TABLE t1;
32 #
33 # SMALLINT
34 #
35 CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
36 INSERT INTO t1 VALUES (32767, null);
37 -- error ER_DUP_ENTRY,1062
38 INSERT INTO t1 (c2) VALUES ('innodb');
39 SELECT * FROM t1;
40 DROP TABLE t1;
41
42 CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
43 INSERT INTO t1 VALUES (65535, null);
44 -- error ER_DUP_ENTRY,1062
45 INSERT INTO t1 (c2) VALUES ('innodb');
46 SELECT * FROM t1;
47 DROP TABLE t1;
48 #
49 # MEDIUMINT
50 #
51 CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
52 INSERT INTO t1 VALUES (8388607, null);
53 -- error ER_DUP_ENTRY,1062
54 INSERT INTO t1 (c2) VALUES ('innodb');
55 SELECT * FROM t1;
56 DROP TABLE t1;
57
58 CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
59 INSERT INTO t1 VALUES (16777215, null);
60 -- error ER_DUP_ENTRY,1062
61 INSERT INTO t1 (c2) VALUES ('innodb');
62 SELECT * FROM t1;
63 DROP TABLE t1;
64 #
65 # INT
66 #
67 CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
68 INSERT INTO t1 VALUES (2147483647, null);
69 -- error ER_DUP_ENTRY,1062
70 INSERT INTO t1 (c2) VALUES ('innodb');
71 SELECT * FROM t1;
72 DROP TABLE t1;
73
74 CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
75 INSERT INTO t1 VALUES (4294967295, null);
76 -- error ER_DUP_ENTRY,1062
77 INSERT INTO t1 (c2) VALUES ('innodb');
78 SELECT * FROM t1;
79 DROP TABLE t1;
80 #
81 # BIGINT
82 #
83 CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
84 INSERT INTO t1 VALUES (9223372036854775807, null);
85 -- error ER_DUP_ENTRY,1062
86 INSERT INTO t1 (c2) VALUES ('innodb');
87 SELECT * FROM t1;
88 DROP TABLE t1;
89
90 CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
91 INSERT INTO t1 VALUES (18446744073709551615, null);
92 -- error ER_AUTOINC_READ_FAILED,1467
93 INSERT INTO t1 (c2) VALUES ('innodb');
94 SELECT * FROM t1;
95 DROP TABLE t1;
96
97 #
98 # Bug 37531
99 # After truncate, auto_increment behaves incorrectly for InnoDB
100 #
101 CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
102 INSERT INTO t1 VALUES (1), (2), (3);
103 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
104 SELECT c1 FROM t1;
105 SHOW CREATE TABLE t1;
106 TRUNCATE TABLE t1;
107 SHOW CREATE TABLE t1;
108 INSERT INTO t1 VALUES (1), (2), (3);
109 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
110 SELECT c1 FROM t1;
111 SHOW CREATE TABLE t1;
112 DROP TABLE t1;
113
114 #
115 # Deleting all records should not reset the AUTOINC counter.
116 #
117 CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
118 INSERT INTO t1 VALUES (1), (2), (3);
119 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
120 SELECT c1 FROM t1;
121 SHOW CREATE TABLE t1;
122 DELETE FROM t1;
123 SHOW CREATE TABLE t1;
124 INSERT INTO t1 VALUES (1), (2), (3);
125 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
126 SELECT c1 FROM t1;
127 SHOW CREATE TABLE t1;
128 DROP TABLE t1;
129
130 #
131 # Bug 38839
132 # Reset the last value generated at end of statement
133 #
134 DROP TABLE IF EXISTS t1;
135 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB;
136 INSERT INTO t1 VALUES (NULL, 1);
137 DELETE FROM t1 WHERE c1 = 1;
138 INSERT INTO t1 VALUES (2,1);
139 INSERT INTO t1 VALUES (NULL,8);
140 SELECT * FROM t1;
141 DROP TABLE t1;
142 # Bug 38839 -- same as above but for multi value insert
143 DROP TABLE IF EXISTS t1;
144 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB;
145 INSERT INTO t1 VALUES (NULL, 1);
146 DELETE FROM t1 WHERE c1 = 1;
147 INSERT INTO t1 VALUES (2,1), (NULL, 8);
148 INSERT INTO t1 VALUES (NULL,9);
149 SELECT * FROM t1;
150 DROP TABLE t1;
151
152 #
153 # Test changes to AUTOINC next value calculation
154 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
155 SHOW VARIABLES LIKE "%auto_inc%";
156 DROP TABLE IF EXISTS t1;
157 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
158 INSERT INTO t1 VALUES (NULL),(5),(NULL);
159 INSERT INTO t1 VALUES (250),(NULL);
160 SELECT * FROM t1;
161 INSERT INTO t1 VALUES (1000);
162 SET @@INSERT_ID=400;
163 INSERT INTO t1 VALUES(NULL),(NULL);
164 SELECT * FROM t1;
165 DROP TABLE t1;
166
167 # Test with SIGNED INT column, by inserting a 0 for the first column value
168 # 0 is treated in the same was NULL.
169 # Reset the AUTOINC session variables
170 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
171 SET @@INSERT_ID=1;
172 SHOW VARIABLES LIKE "%auto_inc%";
173 DROP TABLE IF EXISTS t1;
174 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
175 INSERT INTO t1 VALUES(0);
176 SELECT * FROM t1;
177 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
178 INSERT INTO t1 VALUES (-1), (NULL),(2),(NULL);
179 INSERT INTO t1 VALUES (250),(NULL);
180 SELECT * FROM t1;
181 SET @@INSERT_ID=400;
182 # Duplicate error expected here for autoinc_lock_mode != TRADITIONAL
183 -- error ER_DUP_ENTRY,1062
184 INSERT INTO t1 VALUES(NULL),(NULL);
185 SELECT * FROM t1;
186 DROP TABLE t1;
187
188 # Test with SIGNED INT column
189 # Reset the AUTOINC session variables
190 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
191 SET @@INSERT_ID=1;
192 SHOW VARIABLES LIKE "%auto_inc%";
193 DROP TABLE IF EXISTS t1;
194 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
195 INSERT INTO t1 VALUES(-1);
196 SELECT * FROM t1;
197 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
198 SHOW VARIABLES LIKE "%auto_inc%";
199 INSERT INTO t1 VALUES (-2), (NULL),(2),(NULL);
200 INSERT INTO t1 VALUES (250),(NULL);
201 SELECT * FROM t1;
202 INSERT INTO t1 VALUES (1000);
203 SET @@INSERT_ID=400;
204 INSERT INTO t1 VALUES(NULL),(NULL);
205 SELECT * FROM t1;
206 DROP TABLE t1;
207
208 # Test with UNSIGNED INT column, single insert
209 # The sign in the value is ignored and a new column value is generated
210 # Reset the AUTOINC session variables
211 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
212 SET @@INSERT_ID=1;
213 SHOW VARIABLES LIKE "%auto_inc%";
214 DROP TABLE IF EXISTS t1;
215 CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
216 INSERT IGNORE INTO t1 VALUES(-1);
217 SELECT * FROM t1;
218 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
219 SHOW VARIABLES LIKE "%auto_inc%";
220 INSERT IGNORE INTO t1 VALUES (-2);
221 INSERT INTO t1 VALUES (NULL);
222 INSERT INTO t1 VALUES (2);
223 INSERT INTO t1 VALUES (NULL);
224 INSERT INTO t1 VALUES (250);
225 INSERT INTO t1 VALUES (NULL);
226 SELECT * FROM t1;
227 INSERT INTO t1 VALUES (1000);
228 SET @@INSERT_ID=400;
229 INSERT INTO t1 VALUES(NULL);
230 INSERT INTO t1 VALUES(NULL);
231 SELECT * FROM t1;
232 DROP TABLE t1;
233
234 # Test with UNSIGNED INT column, multi-value inserts
235 # The sign in the value is ignored and a new column value is generated
236 # Reset the AUTOINC session variables
237 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
238 SET @@INSERT_ID=1;
239 SHOW VARIABLES LIKE "%auto_inc%";
240 DROP TABLE IF EXISTS t1;
241 CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
242 INSERT IGNORE INTO t1 VALUES(-1);
243 SELECT * FROM t1;
244 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10;
245 SHOW VARIABLES LIKE "%auto_inc%";
246 INSERT IGNORE INTO t1 VALUES (-2),(NULL),(2),(NULL);
247 INSERT INTO t1 VALUES (250),(NULL);
248 SELECT * FROM t1;
249 INSERT INTO t1 VALUES (1000);
250 SET @@INSERT_ID=400;
251 # Duplicate error expected here for autoinc_lock_mode != TRADITIONAL
252 -- error ER_DUP_ENTRY,1062
253 INSERT INTO t1 VALUES(NULL),(NULL);
254 SELECT * FROM t1;
255 DROP TABLE t1;
256
257 #
258 # Check for overflow handling when increment is > 1
259 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
260 SET @@INSERT_ID=1;
261 SHOW VARIABLES LIKE "%auto_inc%";
262 DROP TABLE IF EXISTS t1;
263 CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
264 # TODO: Fix the autoinc init code
265 # We have to do this because of a bug in the AUTOINC init code.
266 INSERT INTO t1 VALUES(NULL);
267 INSERT INTO t1 VALUES (9223372036854775794); #-- 2^63 - 14
268 SELECT * FROM t1;
269 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10;
270 SHOW VARIABLES LIKE "%auto_inc%";
271 # This should just fit
272 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
273 SELECT * FROM t1;
274 DROP TABLE t1;
275
276 #
277 # Check for overflow handling when increment and offser are > 1
278 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
279 SET @@INSERT_ID=1;
280 SHOW VARIABLES LIKE "%auto_inc%";
281 DROP TABLE IF EXISTS t1;
282 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
283 # TODO: Fix the autoinc init code
284 # We have to do this because of a bug in the AUTOINC init code.
285 INSERT INTO t1 VALUES(NULL);
286 INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13
287 SELECT * FROM t1;
288 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10;
289 SHOW VARIABLES LIKE "%auto_inc%";
290 --error ER_AUTOINC_READ_FAILED
291 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
292 SELECT * FROM t1;
293 DROP TABLE t1;
294
295 #
296 # Check for overflow handling when increment and offset are odd numbers
297 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
298 SET @@INSERT_ID=1;
299 SHOW VARIABLES LIKE "%auto_inc%";
300 DROP TABLE IF EXISTS t1;
301 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
302 # TODO: Fix the autoinc init code
303 # We have to do this because of a bug in the AUTOINC init code.
304 INSERT INTO t1 VALUES(NULL);
305 INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13
306 SELECT * FROM t1;
307 SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7;
308 SHOW VARIABLES LIKE "%auto_inc%";
309 --error ER_AUTOINC_READ_FAILED
310 INSERT INTO t1 VALUES (NULL),(NULL), (NULL);
311 SELECT * FROM t1;
312 DROP TABLE t1;
313
314 # Check for overflow handling when increment and offset are odd numbers
315 # and check for large -ve numbers
316 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
317 SET @@INSERT_ID=1;
318 SHOW VARIABLES LIKE "%auto_inc%";
319 DROP TABLE IF EXISTS t1;
320 CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
321 # TODO: Fix the autoinc init code
322 # We have to do this because of a bug in the AUTOINC init code.
323 INSERT INTO t1 VALUES(NULL);
324 INSERT INTO t1 VALUES(-9223372036854775806); #-- -2^63 + 2
325 INSERT INTO t1 VALUES(-9223372036854775807); #-- -2^63 + 1
326 INSERT INTO t1 VALUES(-9223372036854775808); #-- -2^63
327 SELECT * FROM t1;
328 SET @@SESSION.AUTO_INCREMENT_INCREMENT=3, @@SESSION.AUTO_INCREMENT_OFFSET=3;
329 SHOW VARIABLES LIKE "%auto_inc%";
330 INSERT INTO t1 VALUES (NULL),(NULL), (NULL);
331 SELECT * FROM t1;
332 DROP TABLE t1;
333 #
334 # Check for overflow handling when increment and offset are very
335 # large numbers 2^60
336 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
337 SET @@INSERT_ID=1;
338 SHOW VARIABLES LIKE "%auto_inc%";
339 DROP TABLE IF EXISTS t1;
340 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
341 # TODO: Fix the autoinc init code
342 # We have to do this because of a bug in the AUTOINC init code.
343 INSERT INTO t1 VALUES(NULL);
344 INSERT INTO t1 VALUES (18446744073709551610); #-- 2^64 - 2
345 SELECT * FROM t1;
346 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976;
347 SHOW VARIABLES LIKE "%auto_inc%";
348 --error ER_WARN_DATA_OUT_OF_RANGE
349 INSERT INTO t1 VALUES (NULL),(NULL);
350 SELECT * FROM t1;
351 DROP TABLE t1;
352
353 #
354 # Check for floating point autoinc column handling
355 #
356 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
357 SET @@INSERT_ID=1;
358 SHOW VARIABLES LIKE "%auto_inc%";
359 CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
360 INSERT INTO t1 VALUES(NULL, 1);
361 INSERT INTO t1 VALUES(NULL, 2);
362 SELECT * FROM t1;
363 ALTER TABLE t1 CHANGE c1 c1 SERIAL;
364 SELECT * FROM t1;
365 INSERT INTO t1 VALUES(NULL, 3);
366 INSERT INTO t1 VALUES(NULL, 4);
367 SELECT * FROM t1;
368 DROP TABLE IF EXISTS t1;
369 CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB;
370 INSERT INTO t1 VALUES(NULL, 1);
371 INSERT INTO t1 VALUES(NULL, 2);
372 SELECT * FROM t1;
373 ALTER TABLE t1 CHANGE c1 c1 SERIAL;
374 SELECT * FROM t1;
375 INSERT INTO t1 VALUES(NULL, 3);
376 INSERT INTO t1 VALUES(NULL, 4);
377 SELECT * FROM t1;
378 DROP TABLE t1;
379
380 #
381 # Bug# 42714: AUTOINC column calculated next value not greater than highest
382 # value stored in table.
383 #
384 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5;
385 DROP TABLE IF EXISTS t1;
386 DROP TABLE IF EXISTS t2;
387 CREATE TABLE t1 (
388 a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
389 b INT(10) UNSIGNED NOT NULL,
390 c ENUM('FALSE','TRUE') DEFAULT NULL,
391 PRIMARY KEY (a)) ENGINE = InnoDB;
392 CREATE TABLE t2 (
393 m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
394 n INT(10) UNSIGNED NOT NULL,
395 o enum('FALSE','TRUE') DEFAULT NULL,
396 PRIMARY KEY (m)) ENGINE = InnoDB;
397 INSERT INTO t2 (n,o) VALUES
398 (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'),
399 (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false');
400 SHOW CREATE TABLE t2;
401 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
402 SHOW CREATE TABLE t1;
403 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
404 SELECT * FROM t1;
405 SHOW CREATE TABLE t1;
406 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
407 SELECT * FROM t1;
408 SHOW CREATE TABLE t1;
409 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
410 SELECT * FROM t1;
411 SHOW CREATE TABLE t1;
412 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
413 SHOW CREATE TABLE t1;
414 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
415 SHOW CREATE TABLE t1;
416 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
417 SELECT * FROM t1;
418 SHOW CREATE TABLE t1;
419 DROP TABLE t1;
420 DROP TABLE t2;
421 #
422 # 43203: Overflow from auto incrementing causes server segv
423 #
424
425 DROP TABLE IF EXISTS t1;
426 DROP TABLE IF EXISTS t2;
427 CREATE TABLE t1(
428 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
429 PRIMARY KEY) ENGINE=InnoDB;
430 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
431 CREATE TABLE t2(
432 c1 TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT
433 PRIMARY KEY) ENGINE=InnoDB;
434 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
435 -- error ER_DUP_ENTRY,1062
436 INSERT INTO t2 SELECT c1 FROM t1;
437 SET sql_mode = default;
438 -- error ER_DUP_ENTRY,1467
439 INSERT INTO t2 SELECT NULL FROM t1;
440 DROP TABLE t1;
441 DROP TABLE t2;
442
443 # If the user has specified negative values for an AUTOINC column then
444 # InnoDB should ignore those values when setting the table's max value.
445 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
446 SHOW VARIABLES LIKE "%auto_inc%";
447 # TINYINT
448 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
449 INSERT INTO t1 VALUES (1, NULL);
450 INSERT INTO t1 VALUES (-1, 'innodb');
451 INSERT INTO t1 VALUES (-127, 'innodb');
452 INSERT INTO t1 VALUES (NULL, NULL);
453 SHOW CREATE TABLE t1;
454 SELECT * FROM t1;
455 DROP TABLE t1;
456
457 CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
458 INSERT INTO t1 VALUES (1, NULL);
459 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
460 INSERT IGNORE INTO t1 VALUES (-127, 'innodb');
461 INSERT INTO t1 VALUES (NULL, NULL);
462 SHOW CREATE TABLE t1;
463 SELECT * FROM t1;
464 DROP TABLE t1;
465 #
466 # SMALLINT
467 #
468 CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
469 INSERT INTO t1 VALUES (1, NULL);
470 INSERT INTO t1 VALUES (-1, 'innodb');
471 INSERT INTO t1 VALUES (-32767, 'innodb');
472 INSERT INTO t1 VALUES (NULL, NULL);
473 SHOW CREATE TABLE t1;
474 SELECT * FROM t1;
475 DROP TABLE t1;
476
477 CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
478 INSERT INTO t1 VALUES (1, NULL);
479 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
480 INSERT IGNORE INTO t1 VALUES (-32757, 'innodb');
481 INSERT INTO t1 VALUES (NULL, NULL);
482 SHOW CREATE TABLE t1;
483 SELECT * FROM t1;
484 DROP TABLE t1;
485 #
486 # MEDIUMINT
487 #
488 CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
489 INSERT INTO t1 VALUES (1, NULL);
490 INSERT INTO t1 VALUES (-1, 'innodb');
491 INSERT INTO t1 VALUES (-8388607, 'innodb');
492 INSERT INTO t1 VALUES (NULL, NULL);
493 SHOW CREATE TABLE t1;
494 SELECT * FROM t1;
495 DROP TABLE t1;
496
497 CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
498 INSERT INTO t1 VALUES (1, NULL);
499 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
500 INSERT IGNORE INTO t1 VALUES (-8388607, 'innodb');
501 INSERT INTO t1 VALUES (NULL, NULL);
502 SHOW CREATE TABLE t1;
503 SELECT * FROM t1;
504 DROP TABLE t1;
505 #
506 # INT
507 #
508 CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
509 INSERT INTO t1 VALUES (1, NULL);
510 INSERT INTO t1 VALUES (-1, 'innodb');
511 INSERT INTO t1 VALUES (-2147483647, 'innodb');
512 INSERT INTO t1 VALUES (NULL, NULL);
513 SHOW CREATE TABLE t1;
514 SELECT * FROM t1;
515 DROP TABLE t1;
516
517 CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
518 INSERT INTO t1 VALUES (1, NULL);
519 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
520 INSERT IGNORE INTO t1 VALUES (-2147483647, 'innodb');
521 INSERT INTO t1 VALUES (NULL, NULL);
522 SHOW CREATE TABLE t1;
523 SELECT * FROM t1;
524 DROP TABLE t1;
525 #
526 # BIGINT
527 #
528 CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
529 INSERT INTO t1 VALUES (1, NULL);
530 INSERT INTO t1 VALUES (-1, 'innodb');
531 INSERT INTO t1 VALUES (-9223372036854775807, 'innodb');
532 INSERT INTO t1 VALUES (NULL, NULL);
533 SHOW CREATE TABLE t1;
534 SELECT * FROM t1;
535 DROP TABLE t1;
536
537 CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
538 INSERT INTO t1 VALUES (1, NULL);
539 INSERT IGNORE INTO t1 VALUES (-1, 'innodb');
540 INSERT IGNORE INTO t1 VALUES (-9223372036854775807, 'innodb');
541 INSERT INTO t1 VALUES (NULL, NULL);
542 SHOW CREATE TABLE t1;
543 SELECT * FROM t1;
544 DROP TABLE t1;
545 #
546 # End negative number check
547
548 ##
549 # 47125: auto_increment start value is ignored if an index is created
550 # and engine=innodb
551 #
552 CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB;
553 CREATE INDEX i1 on t1(c2);
554 SHOW CREATE TABLE t1;
555 INSERT INTO t1 (c2) values (0);
556 SELECT * FROM t1;
557 DROP TABLE t1;
558
559 ##
560 # 49032: Use the correct function to read the AUTOINC column value
561 #
562 DROP TABLE IF EXISTS t1;
563 CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB;
564 INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb');
565 # Restart the server
566 -- source include/restart_mysqld.inc
567 INSERT INTO t1(C2) VALUES ('innodb');
568 SHOW CREATE TABLE t1;
569 DROP TABLE t1;
570 CREATE TABLE t1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB;
571 INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb');
572 # Restart the server
573 -- source include/restart_mysqld.inc
574 INSERT INTO t1(C2) VALUES ('innodb');
575 SHOW CREATE TABLE t1;
576 DROP TABLE t1;
577
578 ##
579 # 47720: REPLACE INTO Autoincrement column with negative values
580 #
581 DROP TABLE IF EXISTS t1;
582 CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
583 INSERT INTO t1 SET c1 = 1;
584 SHOW CREATE TABLE t1;
585 INSERT INTO t1 SET c1 = 2;
586 INSERT INTO t1 SET c1 = -1;
587 SELECT * FROM t1;
588 -- error ER_DUP_ENTRY,1062
589 INSERT INTO t1 SET c1 = -1;
590 SHOW CREATE TABLE t1;
591 REPLACE INTO t1 VALUES (-1);
592 SELECT * FROM t1;
593 SHOW CREATE TABLE t1;
594 DROP TABLE t1;
595
596 ##
597 # 49497: Error 1467 (ER_AUTOINC_READ_FAILED) on inserting a negative value
598 #
599 DROP TABLE IF EXISTS t1;
600 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
601 INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL);
602 SELECT * FROM t1;
603 SHOW CREATE TABLE t1;
604 DROP TABLE t1;
605 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
606 INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL);
607 SELECT * FROM t1;
608 SHOW CREATE TABLE t1;
609 DROP TABLE t1;
610 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
611 INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL);
612 INSERT INTO t1 VALUES (4), (5), (6), (NULL);
613 SELECT * FROM t1;
614 SHOW CREATE TABLE t1;
615 DROP TABLE t1;
616 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
617 INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5);
618 SELECT * FROM t1;
619 SHOW CREATE TABLE t1;
620 DROP TABLE t1;
621 CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB;
622 INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL);
623 SELECT * FROM t1;
624 SHOW CREATE TABLE t1;
625 DROP TABLE t1;
626
627 ##
628 # 55277: Failing assertion: auto_inc > 0
629 #
630 DROP TABLE IF EXISTS t1;
631 CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
632 INSERT INTO t1 VALUES (NULL);
633 INSERT INTO t1 VALUES (18446744073709551615);
634 # Restart the server
635 -- source include/restart_mysqld.inc
636 SHOW CREATE TABLE t1;
637 DROP TABLE t1;
638
639
640 # Check if we handle offset > column max value properly
641 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=256;
642 SHOW VARIABLES LIKE "%auto_inc%";
643 # TINYINT
644 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
645 INSERT INTO t1 VALUES (1, NULL);
646 SHOW CREATE TABLE t1;
647 SELECT * FROM t1;
648 DROP TABLE t1;
649
650 # Check if we handle the case where a current value is greater than the max
651 # of the column. IMO, this should not be allowed and the assertion that fails
652 # is actually an invariant.
653 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
654 SHOW VARIABLES LIKE "%auto_inc%";
655 # TINYINT
656 CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
657 INSERT INTO t1 VALUES (2147483648, 'a');
658 SHOW CREATE TABLE t1;
659 SELECT * FROM t1;
660 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
661 ALTER TABLE t1 CHANGE c1 c1 INT;
662 SHOW CREATE TABLE t1;
663 INSERT INTO t1(c2) VALUES('b');
664 SELECT * FROM t1;
665 SHOW CREATE TABLE t1;
666 DROP TABLE t1;
667 SET sql_mode = default;
668
669
670 CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE = MyISAM;
671 INSERT INTO t1 (c1) VALUES (NULL), (-290783232), (NULL);
672 SHOW CREATE TABLE t1;
673 SELECT * FROM t1;
674 ALTER TABLE t1 ENGINE = InnoDB;
675 SELECT * FROM t1;
676 SHOW CREATE TABLE t1;
677 REPLACE INTO t1 (c2 ) VALUES (0);
678 SELECT * FROM t1;
679 DROP TABLE t1;
680
681 #DOUBLE
682 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
683 AUTO_INCREMENT=10000000000000000000;
684 SHOW CREATE TABLE t1;
685 --error 1467
686 INSERT INTO t1 VALUES ();
687 DROP TABLE t1;
688
689 --echo #
690 --echo # Bug #14049391 INNODB MISCALCULATES AUTO-INCREMENT
691 --echo # AFTER CHANGING AUTO_INCREMENT_INCREMEMENT
692 --echo #
693 CREATE TABLE t ( i INT AUTO_INCREMENT, KEY(i) ) ENGINE=InnoDB;
694 SET auto_increment_increment = 300;
695 INSERT INTO t VALUES (NULL), (NULL);
696 SHOW CREATE TABLE t;
697 SET auto_increment_increment = 50;
698 INSERT INTO t VALUES (NULL);
699 SELECT * FROM t;
700 SHOW CREATE TABLE t;
701 DROP TABLE t;
702
703 --echo #
704 --echo # Bug#15851528 DUPLICATE KEY ERROR ON AUTO-INC PK WITH MIXED AUTO_INCREMENT_INCREMENT CLIENTS
705 --echo #
706 --echo # This test shows that the next record to be inserted is not affected
707 --echo # by a change in auto_increment_increment.
708 --echo # In addition, current value of auto_increment_increment by the client
709 --echo # that uses the existing autoinc value with be used to set next autoinc
710 --echo # value, which will be used by next client reguardless of its own session
711 --echo # setting for auto_increment_increment.
712 --echo #
713
714 --connection default
715 --echo # Client 1: Insert a record with auto_increment_increment=2
716 CREATE TABLE t(
717 a SERIAL PRIMARY KEY,
718 b VARCHAR(200)) ENGINE=InnoDB;
719 SET SESSION auto_increment_increment=2;
720 SHOW CREATE TABLE t;
721 INSERT INTO t(b) VALUES('S1');
722 SELECT a,b FROM t;
723 --connect(con1,localhost,root,,)
724
725 --connection con1
726 --echo # Client 2: Insert records with auto_increment_increment 2,1
727 SET SESSION auto_increment_increment=2;
728 SHOW CREATE TABLE t;
729 INSERT INTO t(b) VALUES('S2');
730 SELECT a,b FROM t;
731 SET SESSION auto_increment_increment=1;
732 SHOW CREATE TABLE t;
733 INSERT INTO t(b) VALUES('S2');
734 SELECT a,b FROM t;
735 disconnect con1;
736
737 --connection default
738 --echo # Client 1: Insert a record with auto_increment_increment=1
739 SET SESSION auto_increment_increment=1;
740 SHOW CREATE TABLE t;
741 INSERT INTO t(b) VALUES('S1');
742 SELECT a,b FROM t;
743 DROP TABLE t;
744
745 --echo # Autoincrement behaviour with mixed insert.
746 CREATE TABLE t(
747 a TINYINT AUTO_INCREMENT PRIMARY KEY,
748 b VARCHAR(200)) ENGINE=InnoDB;
749 SET SESSION auto_increment_increment=100;
750 SHOW CREATE TABLE t;
751 INSERT INTO t(b) VALUES('S0'),('S1');
752 SELECT a,b FROM t;
753 SHOW CREATE TABLE t;
754 INSERT INTO t(a,b) VALUES(28,'S2');
755 SELECT a,b FROM t;
756 SET SESSION auto_increment_increment=1;
757 SHOW CREATE TABLE t;
758 INSERT INTO t(b) VALUES('S3');
759 SELECT a,b FROM t;
760 SHOW CREATE TABLE t;
761 -- error ER_DUP_ENTRY
762 INSERT INTO t(b) VALUES('S4');
763 DROP TABLE t;
764
765 --echo #
766 --echo # Bug 26906787 - INCORRECT AUTO_INCREMENT IDS ISSUED WHEN CLOSE TO ULLONG_MAX
767 --echo #
768
769 SET auto_increment_increment = 300;
770 CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8))
771 ENGINE=innodb;
772 INSERT INTO t1 VALUES (18446744073709551613, 'a');
773 SHOW CREATE TABLE t1;
774 --error ER_WARN_DATA_OUT_OF_RANGE
775 INSERT INTO t1 VALUES (NULL, 'b');
776 SHOW CREATE TABLE t1;
777 DROP TABLE t1;
778
779 CREATE TABLE t1 (
780 a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
781 b CHAR(8)
782 ) ENGINE=innodb
783 PARTITION BY RANGE (a) (
784 PARTITION p0 VALUES LESS THAN (6),
785 PARTITION p1 VALUES LESS THAN MAXVALUE
786 );
787 INSERT INTO t1 VALUES (18446744073709551613, 'a');
788 SHOW CREATE TABLE t1;
789 --error ER_WARN_DATA_OUT_OF_RANGE
790 INSERT INTO t1 VALUES (NULL, 'b');
791 SHOW CREATE TABLE t1;
792 DROP TABLE t1;