Debian Codegraph @

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

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