Debian Codegraph @

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

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