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