Debian Codegraph @

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

raw
1 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
2 INSERT INTO t1 VALUES(347),(33101),(123),(45),(6);
3 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
4 ALTER TABLE t1 ADD PRIMARY KEY(a);
5
6 # We cannot assign AUTO_INCREMENT values during online index creation.
7 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
8 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
9 LOCK=NONE;
10
11 --error ER_WRONG_AUTO_KEY
12 ALTER TABLE t1 ADD id INT AUTO_INCREMENT;
13
14 --error ER_WRONG_AUTO_KEY
15 ALTER TABLE t1 ADD id INT AUTO_INCREMENT, ADD INDEX(a, id);
16
17 ALTER TABLE t1 ADD id INT NOT NULL, ADD INDEX(id, a);
18
19 SELECT * FROM t1;
20
21 # Test with a non-default increment and offset
22 SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 30;
23
24 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
25 DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=COPY;
26
27 SHOW CREATE TABLE t1;
28
29 # The autoinc next value should increase. It is not rolled back.
30 BEGIN;
31 INSERT INTO t1 VALUES(7,0);
32 SELECT * FROM t1;
33 ROLLBACK;
34
35 SHOW CREATE TABLE t1;
36
37 # We cannot assign AUTO_INCREMENT values during online index creation.
38 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
39 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
40 DROP COLUMN id, AUTO_INCREMENT = 42, LOCK=NONE;
41
42 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
43 DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE;
44
45 SELECT * FROM t1;
46 SHOW CREATE TABLE t1;
47
48 INSERT INTO t1 SET a=123;
49 INSERT INTO t1 VALUES(-123,-45);
50
51 ALTER TABLE t1 AUTO_INCREMENT = 75;
52
53 INSERT INTO t1 SET a=123;
54 SELECT * FROM t1;
55
56 SHOW CREATE TABLE t1;
57
58 DROP TABLE t1;
59
60 # ALGORITHM=INPLACE should deliver identical results to ALGORITHM=COPY.
61 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
62 INSERT INTO t1 VALUES(347),(33101),(123),(45),(6);
63 ALTER TABLE t1 ADD PRIMARY KEY(a);
64
65 ALTER TABLE t1 ADD id INT NOT NULL, ADD INDEX(id, a);
66
67 SELECT * FROM t1;
68
69 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
70 DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE;
71
72 SHOW CREATE TABLE t1;
73
74 # The autoinc next value should increase. It is not rolled back.
75 BEGIN;
76 INSERT INTO t1 VALUES(7,0);
77 SELECT * FROM t1;
78 ROLLBACK;
79
80 SHOW CREATE TABLE t1;
81
82 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
83 DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=COPY;
84
85 SELECT * FROM t1;
86
87 SHOW CREATE TABLE t1;
88
89 INSERT INTO t1 SET a=123;
90 INSERT INTO t1 VALUES(-123,-45);
91
92 ALTER TABLE t1 AUTO_INCREMENT = 75;
93
94 INSERT INTO t1 SET a=123;
95 SELECT * FROM t1;
96
97 SHOW CREATE TABLE t1;
98
99 DROP TABLE t1;