Debian Codegraph @

mysql-test/suite/innodb/r/innodb-alter-autoinc.result

raw
1 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
2 INSERT INTO t1 VALUES(347),(33101),(123),(45),(6);
3 ALTER TABLE t1 ADD PRIMARY KEY(a);
4 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
5 LOCK=NONE;
6 ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED.
7 ALTER TABLE t1 ADD id INT AUTO_INCREMENT;
8 ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
9 ALTER TABLE t1 ADD id INT AUTO_INCREMENT, ADD INDEX(a, id);
10 ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
11 ALTER TABLE t1 ADD id INT NOT NULL, ADD INDEX(id, a);
12 SELECT * FROM t1;
13 a id
14 6 0
15 45 0
16 123 0
17 347 0
18 33101 0
19 SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 30;
20 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
21 DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=COPY;
22 SHOW CREATE TABLE t1;
23 Table Create Table
24 t1 CREATE TABLE `t1` (
25 `a` int NOT NULL,
26 `id` int NOT NULL AUTO_INCREMENT,
27 PRIMARY KEY (`id`),
28 KEY `id` (`id`,`a`)
29 ) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
30 BEGIN;
31 INSERT INTO t1 VALUES(7,0);
32 SELECT * FROM t1;
33 a id
34 6 45
35 45 50
36 123 55
37 347 60
38 33101 65
39 7 70
40 ROLLBACK;
41 SHOW CREATE TABLE t1;
42 Table Create Table
43 t1 CREATE TABLE `t1` (
44 `a` int NOT NULL,
45 `id` int NOT NULL AUTO_INCREMENT,
46 PRIMARY KEY (`id`),
47 KEY `id` (`id`,`a`)
48 ) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
49 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
50 DROP COLUMN id, AUTO_INCREMENT = 42, LOCK=NONE;
51 ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED.
52 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
53 DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE;
54 SELECT * FROM t1;
55 a id
56 6 45
57 45 50
58 123 55
59 347 60
60 33101 65
61 SHOW CREATE TABLE t1;
62 Table Create Table
63 t1 CREATE TABLE `t1` (
64 `a` int NOT NULL,
65 `id` int NOT NULL AUTO_INCREMENT,
66 PRIMARY KEY (`id`),
67 KEY `id` (`id`,`a`)
68 ) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
69 INSERT INTO t1 SET a=123;
70 INSERT INTO t1 VALUES(-123,-45);
71 ALTER TABLE t1 AUTO_INCREMENT = 75;
72 INSERT INTO t1 SET a=123;
73 SELECT * FROM t1;
74 a id
75 -123 -45
76 6 45
77 45 50
78 123 55
79 347 60
80 33101 65
81 123 70
82 123 75
83 SHOW CREATE TABLE t1;
84 Table Create Table
85 t1 CREATE TABLE `t1` (
86 `a` int NOT NULL,
87 `id` int NOT NULL AUTO_INCREMENT,
88 PRIMARY KEY (`id`),
89 KEY `id` (`id`,`a`)
90 ) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
91 DROP TABLE t1;
92 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
93 INSERT INTO t1 VALUES(347),(33101),(123),(45),(6);
94 ALTER TABLE t1 ADD PRIMARY KEY(a);
95 ALTER TABLE t1 ADD id INT NOT NULL, ADD INDEX(id, a);
96 SELECT * FROM t1;
97 a id
98 6 0
99 45 0
100 123 0
101 347 0
102 33101 0
103 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
104 DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE;
105 SHOW CREATE TABLE t1;
106 Table Create Table
107 t1 CREATE TABLE `t1` (
108 `a` int NOT NULL,
109 `id` int NOT NULL AUTO_INCREMENT,
110 PRIMARY KEY (`id`),
111 KEY `id` (`id`,`a`)
112 ) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
113 BEGIN;
114 INSERT INTO t1 VALUES(7,0);
115 SELECT * FROM t1;
116 a id
117 6 45
118 45 50
119 123 55
120 347 60
121 33101 65
122 7 70
123 ROLLBACK;
124 SHOW CREATE TABLE t1;
125 Table Create Table
126 t1 CREATE TABLE `t1` (
127 `a` int NOT NULL,
128 `id` int NOT NULL AUTO_INCREMENT,
129 PRIMARY KEY (`id`),
130 KEY `id` (`id`,`a`)
131 ) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
132 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
133 DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=COPY;
134 SELECT * FROM t1;
135 a id
136 6 45
137 45 50
138 123 55
139 347 60
140 33101 65
141 SHOW CREATE TABLE t1;
142 Table Create Table
143 t1 CREATE TABLE `t1` (
144 `a` int NOT NULL,
145 `id` int NOT NULL AUTO_INCREMENT,
146 PRIMARY KEY (`id`),
147 KEY `id` (`id`,`a`)
148 ) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
149 INSERT INTO t1 SET a=123;
150 INSERT INTO t1 VALUES(-123,-45);
151 ALTER TABLE t1 AUTO_INCREMENT = 75;
152 INSERT INTO t1 SET a=123;
153 SELECT * FROM t1;
154 a id
155 -123 -45
156 6 45
157 45 50
158 123 55
159 347 60
160 33101 65
161 123 70
162 123 75
163 SHOW CREATE TABLE t1;
164 Table Create Table
165 t1 CREATE TABLE `t1` (
166 `a` int NOT NULL,
167 `id` int NOT NULL AUTO_INCREMENT,
168 PRIMARY KEY (`id`),
169 KEY `id` (`id`,`a`)
170 ) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
171 DROP TABLE t1;