Debian Codegraph @

mysql-test/suite/innodb/r/instant_alter.result

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
3 SET GLOBAL innodb_stats_persistent = 0;
4 #
5 # MDEV-11369: Instant ADD COLUMN for InnoDB
6 #
7 SET @saved_allowance = @@GLOBAL.innodb_instant_alter_column_allowed;
8 SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT;
9 call mtr.add_suppression("Cannot add field `.*` in table `test`.`.*` because after adding it, the row size is");
10 CREATE TABLE t(a INT UNIQUE)ENGINE=InnoDB ROW_FORMAT=COMPACT;
11 ALTER TABLE t ADD e INT, ROW_FORMAT=COMPRESSED;
12 INSERT INTO t SET a=1;
13 SET @old_instant=
14 (SELECT variable_value FROM information_schema.global_status
15 WHERE variable_name = 'innodb_instant_alter_column');
16 ALTER TABLE t ADD b INT NOT NULL, ALGORITHM=COPY;
17 affected rows: 1
18 info: Records: 1 Duplicates: 0 Warnings: 0
19 ALTER TABLE t ADD c INT NOT NULL, FORCE;
20 affected rows: 0
21 info: Records: 0 Duplicates: 0 Warnings: 0
22 SELECT variable_value-@old_instant instants
23 FROM information_schema.global_status
24 WHERE variable_name = 'innodb_instant_alter_column';
25 instants
26 0
27 ALTER TABLE t ADD d INT NOT NULL;
28 affected rows: 0
29 info: Records: 0 Duplicates: 0 Warnings: 0
30 SELECT * FROM t;
31 a e b c d
32 1 NULL 0 0 0
33 SHOW CREATE TABLE t;
34 Table Create Table
35 t CREATE TABLE `t` (
36 `a` int(11) DEFAULT NULL,
37 `e` int(11) DEFAULT NULL,
38 `b` int(11) NOT NULL,
39 `c` int(11) NOT NULL,
40 `d` int(11) NOT NULL,
41 UNIQUE KEY `a` (`a`)
42 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED
43 SELECT variable_value-@old_instant instants
44 FROM information_schema.global_status
45 WHERE variable_name = 'innodb_instant_alter_column';
46 instants
47 0
48 SELECT variable_value-@old_instant instants
49 FROM information_schema.global_status
50 WHERE variable_name = 'innodb_instant_alter_column';
51 instants
52 0
53 DROP TABLE t;
54 connect analyze, localhost, root;
55 connection default;
56 SET timestamp = 42;
57 SET time_zone='+03:00';
58 SET @old_instant=
59 (SELECT variable_value FROM information_schema.global_status
60 WHERE variable_name = 'innodb_instant_alter_column');
61 CREATE TABLE t1
62 (id INT PRIMARY KEY, c2 INT UNIQUE,
63 c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'),
64 SPATIAL INDEX(c3)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
65 INSERT INTO t1 (id, c2) values(1,1);
66 SELECT id,c2,ST_AsText(c3) c3 FROM t1;
67 id c2 c3
68 1 1 POINT(3 4)
69 ALTER TABLE t1 ADD COLUMN (
70 d1 INT, d2 INT UNSIGNED DEFAULT 10, d3 VARCHAR(20) NOT NULL DEFAULT 'abcde',
71 d4 TIMESTAMP NOT NULL DEFAULT current_timestamp());
72 affected rows: 0
73 info: Records: 0 Duplicates: 0 Warnings: 0
74 ALTER TABLE t1 ADD INDEX(d3);
75 affected rows: 0
76 info: Records: 0 Duplicates: 0 Warnings: 0
77 BEGIN;
78 UPDATE t1 SET d3='';
79 ROLLBACK;
80 SELECT id,c2,ST_AsText(c3) c3, d1, d2, d3, d4 FROM t1;
81 id c2 c3 d1 d2 d3 d4
82 1 1 POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
83 INSERT INTO t1 (id) VALUES(2),(3),(4),(5),(6);
84 ALTER TABLE t1 CHANGE d1 d1 INT DEFAULT 5, CHANGE d2 d2 INT DEFAULT 15,
85 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'fghij',
86 CHANGE d4 dfour TIMESTAMP NOT NULL DEFAULT now();
87 affected rows: 6
88 info: Records: 6 Duplicates: 0 Warnings: 0
89 UPDATE t1 SET d3='foo' WHERE id = 2;
90 UPDATE t1 SET d3=DEFAULT WHERE id = 4;
91 INSERT INTO t1 SET id = 7;
92 SELECT id,c2,ST_AsText(c3) c3, d1, d2, d3, dfour FROM t1;
93 id c2 c3 d1 d2 d3 dfour
94 1 1 POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
95 2 NULL POINT(3 4) NULL 10 foo 1970-01-01 03:00:42
96 3 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
97 4 NULL POINT(3 4) NULL 10 fghij 1970-01-01 03:00:42
98 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
99 6 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
100 7 NULL POINT(3 4) 5 15 fghij 1970-01-01 03:00:42
101 CHECK TABLE t1;
102 Table Op Msg_type Msg_text
103 test.t1 check status OK
104 ALTER TABLE t1 ADD COLUMN e1 INT AS (id * 3);
105 affected rows: 0
106 info: Records: 0 Duplicates: 0 Warnings: 0
107 ALTER TABLE t1 ADD COLUMN e2 VARCHAR(30) AS (d3);
108 affected rows: 0
109 info: Records: 0 Duplicates: 0 Warnings: 0
110 ALTER TABLE t1 ADD COLUMN e3 INT AS (id * 2);
111 affected rows: 0
112 info: Records: 0 Duplicates: 0 Warnings: 0
113 ALTER TABLE t1 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'foobar',
114 ADD COLUMN (d5 CHAR(20) DEFAULT 'hijkl', d6 INT DEFAULT -12345, d7 INT),
115 DROP INDEX d3;
116 affected rows: 0
117 info: Records: 0 Duplicates: 0 Warnings: 0
118 INSERT INTO t1 SET id = 8;
119 UPDATE t1 SET d3 = 'yyyyy' WHERE id = 1;
120 UPDATE t1 SET d3 = 'xxxxx' WHERE id = 2;
121 BEGIN;
122 UPDATE t1 SET d3 = 'xxxxx' WHERE id = 3;
123 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 3;
124 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
125 3 NULL POINT(3 4) NULL 10 xxxxx 1970-01-01 03:00:42 9 xxxxx 6 hijkl -12345 NULL
126 ROLLBACK;
127 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 3;
128 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
129 3 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 9 abcde 6 hijkl -12345 NULL
130 BEGIN;
131 UPDATE t1 SET d7 = NULL WHERE ID = 5;
132 ROLLBACK;
133 BEGIN;
134 UPDATE t1 SET d7 = NULL, d6 = 10 WHERE id = 5;
135 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 5;
136 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
137 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl 10 NULL
138 ROLLBACK;
139 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 5;
140 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
141 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl -12345 NULL
142 ALTER TABLE t1 ADD COLUMN (f1 VARCHAR(20) AS (concat('x', e2)) STORED);
143 affected rows: 8
144 info: Records: 8 Duplicates: 0 Warnings: 0
145 ALTER TABLE t1 ADD COLUMN (d8 VARCHAR(20) DEFAULT 'omnopq');
146 affected rows: 0
147 info: Records: 0 Duplicates: 0 Warnings: 0
148 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7, f1, d8 FROM t1;
149 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7 f1 d8
150 1 1 POINT(3 4) NULL 10 yyyyy 1970-01-01 03:00:42 3 yyyyy 2 hijkl -12345 NULL xyyyyy omnopq
151 2 NULL POINT(3 4) NULL 10 xxxxx 1970-01-01 03:00:42 6 xxxxx 4 hijkl -12345 NULL xxxxxx omnopq
152 3 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 9 abcde 6 hijkl -12345 NULL xabcde omnopq
153 4 NULL POINT(3 4) NULL 10 fghij 1970-01-01 03:00:42 12 fghij 8 hijkl -12345 NULL xfghij omnopq
154 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl -12345 NULL xabcde omnopq
155 6 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 18 abcde 12 hijkl -12345 NULL xabcde omnopq
156 7 NULL POINT(3 4) 5 15 fghij 1970-01-01 03:00:42 21 fghij 14 hijkl -12345 NULL xfghij omnopq
157 8 NULL POINT(3 4) 5 15 foobar 1970-01-01 03:00:42 24 foobar 16 hijkl -12345 NULL xfoobar omnopq
158 SHOW CREATE TABLE t1;
159 Table Create Table
160 t1 CREATE TABLE `t1` (
161 `id` int(11) NOT NULL,
162 `c2` int(11) DEFAULT NULL,
163 `c3` point NOT NULL DEFAULT st_geometryfromtext('POINT(3 4)'),
164 `d1` int(11) DEFAULT 5,
165 `d2` int(11) DEFAULT 15,
166 `d3` varchar(20) NOT NULL DEFAULT 'foobar',
167 `dfour` timestamp NOT NULL DEFAULT current_timestamp(),
168 `e1` int(11) GENERATED ALWAYS AS (`id` * 3) VIRTUAL,
169 `e2` varchar(30) GENERATED ALWAYS AS (`d3`) VIRTUAL,
170 `e3` int(11) GENERATED ALWAYS AS (`id` * 2) VIRTUAL,
171 `d5` char(20) DEFAULT 'hijkl',
172 `d6` int(11) DEFAULT -12345,
173 `d7` int(11) DEFAULT NULL,
174 `f1` varchar(20) GENERATED ALWAYS AS (concat('x',`e2`)) STORED,
175 `d8` varchar(20) DEFAULT 'omnopq',
176 PRIMARY KEY (`id`),
177 UNIQUE KEY `c2` (`c2`),
178 SPATIAL KEY `c3` (`c3`)
179 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=REDUNDANT
180 ALTER TABLE t1
181 CHANGE c2 c2 INT DEFAULT 42,
182 CHANGE d1 d1 INT DEFAULT 1,
183 CHANGE d2 d2 INT DEFAULT 20,
184 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'boofar';
185 affected rows: 0
186 info: Records: 0 Duplicates: 0 Warnings: 0
187 INSERT INTO t1 SET id=9;
188 ALTER TABLE t1 DROP c3;
189 affected rows: 0
190 info: Records: 0 Duplicates: 0 Warnings: 0
191 SHOW CREATE TABLE t1;
192 Table Create Table
193 t1 CREATE TABLE `t1` (
194 `id` int(11) NOT NULL,
195 `c2` int(11) DEFAULT 42,
196 `d1` int(11) DEFAULT 1,
197 `d2` int(11) DEFAULT 20,
198 `d3` varchar(20) NOT NULL DEFAULT 'boofar',
199 `dfour` timestamp NOT NULL DEFAULT current_timestamp(),
200 `e1` int(11) GENERATED ALWAYS AS (`id` * 3) VIRTUAL,
201 `e2` varchar(30) GENERATED ALWAYS AS (`d3`) VIRTUAL,
202 `e3` int(11) GENERATED ALWAYS AS (`id` * 2) VIRTUAL,
203 `d5` char(20) DEFAULT 'hijkl',
204 `d6` int(11) DEFAULT -12345,
205 `d7` int(11) DEFAULT NULL,
206 `f1` varchar(20) GENERATED ALWAYS AS (concat('x',`e2`)) STORED,
207 `d8` varchar(20) DEFAULT 'omnopq',
208 PRIMARY KEY (`id`),
209 UNIQUE KEY `c2` (`c2`)
210 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=REDUNDANT
211 SELECT * FROM t1;
212 id c2 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7 f1 d8
213 1 1 NULL 10 yyyyy 1970-01-01 03:00:42 3 yyyyy 2 hijkl -12345 NULL xyyyyy omnopq
214 2 NULL NULL 10 xxxxx 1970-01-01 03:00:42 6 xxxxx 4 hijkl -12345 NULL xxxxxx omnopq
215 3 NULL NULL 10 abcde 1970-01-01 03:00:42 9 abcde 6 hijkl -12345 NULL xabcde omnopq
216 4 NULL NULL 10 fghij 1970-01-01 03:00:42 12 fghij 8 hijkl -12345 NULL xfghij omnopq
217 5 NULL NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl -12345 NULL xabcde omnopq
218 6 NULL NULL 10 abcde 1970-01-01 03:00:42 18 abcde 12 hijkl -12345 NULL xabcde omnopq
219 7 NULL 5 15 fghij 1970-01-01 03:00:42 21 fghij 14 hijkl -12345 NULL xfghij omnopq
220 8 NULL 5 15 foobar 1970-01-01 03:00:42 24 foobar 16 hijkl -12345 NULL xfoobar omnopq
221 9 42 1 20 boofar 1970-01-01 03:00:42 27 boofar 18 hijkl -12345 NULL xboofar omnopq
222 CREATE TABLE t2
223 (id INT primary key, c1 VARCHAR(4000),
224 p GEOMETRY NOT NULL DEFAULT ST_GeomFromText('LINESTRING(0 0,0 1,1 1)'),
225 SPATIAL INDEX(p))
226 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
227 BEGIN;
228 INSERT INTO t2 SET id=1, c1=REPEAT('a', 4000);
229 INSERT INTO t2 SET id=2, c1=REPEAT('a', 4000), p=ST_GeomFromText('POINT(1 1)');
230 COMMIT;
231 ALTER TABLE t2 ADD COLUMN d1 VARCHAR(2000) DEFAULT REPEAT('asdf',500);
232 affected rows: 0
233 info: Records: 0 Duplicates: 0 Warnings: 0
234 SELECT id, c1, ST_AsText(p) p, d1 FROM t2;
235 id c1 p d1
236 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa LINESTRING(0 0,0 1,1 1) asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
237 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa POINT(1 1) asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
238 BEGIN;
239 UPDATE t2 SET c1 = repeat(id, 4000);
240 connection analyze;
241 ANALYZE TABLE t2;
242 Table Op Msg_type Msg_text
243 test.t2 analyze status Engine-independent statistics collected
244 test.t2 analyze status OK
245 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
246 WHERE name = 'test/t2';
247 clust_index_size
248 1
249 connection default;
250 ROLLBACK;
251 connection analyze;
252 ANALYZE TABLE t2;
253 Table Op Msg_type Msg_text
254 test.t2 analyze status Engine-independent statistics collected
255 test.t2 analyze status OK
256 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
257 WHERE name = 'test/t2';
258 clust_index_size
259 1
260 connection default;
261 BEGIN;
262 UPDATE t2 SET d1 = repeat(id, 200);
263 connection analyze;
264 ANALYZE TABLE t2;
265 Table Op Msg_type Msg_text
266 test.t2 analyze status Engine-independent statistics collected
267 test.t2 analyze status OK
268 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
269 WHERE name = 'test/t2';
270 clust_index_size
271 1
272 connection default;
273 ROLLBACK;
274 connection analyze;
275 ANALYZE TABLE t2;
276 Table Op Msg_type Msg_text
277 test.t2 analyze status Engine-independent statistics collected
278 test.t2 analyze status OK
279 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
280 WHERE name = 'test/t2';
281 clust_index_size
282 1
283 connection default;
284 ALTER TABLE t2 DROP p;
285 affected rows: 0
286 info: Records: 0 Duplicates: 0 Warnings: 0
287 SELECT * FROM t2;
288 id c1 d1
289 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
290 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
291 CREATE TABLE t3
292 (id INT PRIMARY KEY, c2 INT UNSIGNED NOT NULL UNIQUE,
293 c3 POLYGON NOT NULL DEFAULT ST_PolyFromText('POLYGON((1 1,2 2,3 3,1 1))'),
294 SPATIAL INDEX(c3))
295 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
296 INSERT INTO t3(id,c2) VALUES(1,1),(2,2),(3,3);
297 SELECT id, c2, ST_AsText(c3) c3 FROM t3;
298 id c2 c3
299 1 1 POLYGON((1 1,2 2,3 3,1 1))
300 2 2 POLYGON((1 1,2 2,3 3,1 1))
301 3 3 POLYGON((1 1,2 2,3 3,1 1))
302 ALTER TABLE t3 ADD COLUMN
303 (c4 DATETIME DEFAULT current_timestamp(),
304 c5 TIMESTAMP NOT NULL DEFAULT current_timestamp(),
305 c6 POINT);
306 affected rows: 0
307 info: Records: 0 Duplicates: 0 Warnings: 0
308 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6 FROM t3;
309 id c2 c3 c4 c5 c6
310 1 1 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL
311 2 2 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL
312 3 3 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL
313 affected rows: 3
314 ALTER TABLE t3 ADD COLUMN c7 TIME NOT NULL DEFAULT current_timestamp();
315 affected rows: 0
316 info: Records: 0 Duplicates: 0 Warnings: 1
317 Warnings:
318 Note 1265 Data truncated for column 'c7' at row 0
319 ALTER TABLE t3 ADD COLUMN c8 DATE NOT NULL DEFAULT current_timestamp();
320 affected rows: 0
321 info: Records: 0 Duplicates: 0 Warnings: 1
322 Warnings:
323 Note 1265 Data truncated for column 'c8' at row 0
324 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6, c7, c8 FROM t3;
325 id c2 c3 c4 c5 c6 c7 c8
326 1 1 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01
327 2 2 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01
328 3 3 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01
329 ALTER TABLE t3 ADD COLUMN t TEXT CHARSET utf8
330 DEFAULT 'The quick brown fox jumps over the lazy dog';
331 affected rows: 0
332 info: Records: 0 Duplicates: 0 Warnings: 0
333 ALTER TABLE t3 ADD COLUMN b BLOB NOT NULL;
334 affected rows: 0
335 info: Records: 0 Duplicates: 0 Warnings: 0
336 INSERT INTO t3 SET id=4;
337 ERROR HY000: Field 'c2' doesn't have a default value
338 INSERT INTO t3 SET id=4, c2=0, b=0xf09f98b1;
339 affected rows: 1
340 Warnings:
341 Note 1265 Data truncated for column 'c7' at row 1
342 Note 1265 Data truncated for column 'c8' at row 1
343 SET innodb_strict_mode = OFF;
344 affected rows: 0
345 ALTER TABLE t3 CHANGE t phrase TEXT DEFAULT 0xc3a4c3a448,
346 CHANGE b b BLOB NOT NULL DEFAULT 'binary line of business';
347 affected rows: 4
348 info: Records: 4 Duplicates: 0 Warnings: 0
349 INSERT INTO t3 SET id=5, c2=9;
350 Warnings:
351 Note 1265 Data truncated for column 'c7' at row 1
352 Note 1265 Data truncated for column 'c8' at row 1
353 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6, c7, c8, phrase, b FROM t3;
354 id c2 c3 c4 c5 c6 c7 c8 phrase b
355 1 1 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog
356 2 2 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog
357 3 3 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog
358 4 0 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog 😱
359 5 9 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 ääH binary line of business
360 ALTER TABLE t3 DROP c3, DROP c7;
361 affected rows: 0
362 info: Records: 0 Duplicates: 0 Warnings: 0
363 SELECT * FROM t3;
364 id c2 c4 c5 c6 c8 phrase b
365 1 1 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog
366 2 2 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog
367 3 3 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog
368 4 0 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog 😱
369 5 9 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 ääH binary line of business
370 CREATE TABLE t4
371 (id INT, foo INT DEFAULT 0, c1 VARCHAR(4000),
372 p GEOMETRY NOT NULL DEFAULT ST_GeomFromText('LINESTRING(0 0,0 1,1 1)'),
373 PRIMARY KEY(id,foo))
374 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
375 INSERT INTO t4 (id,c1) VALUES (1, REPEAT('a', 4000)), (2, REPEAT('a', 4000));
376 ALTER TABLE t4 ADD COLUMN d1 INT;
377 BEGIN;
378 UPDATE t4 SET c1 = repeat('1', 4000), foo=1 WHERE id=1;
379 INSERT INTO t4 (id,c1) VALUES (1, REPEAT('a', 4000));
380 UPDATE t4 SET c1 = repeat('2', 4000), foo=1 WHERE id=2;
381 ROLLBACK;
382 BEGIN;
383 UPDATE t4 SET d1 = 1,foo=2 WHERE id=1;
384 INSERT INTO t4 (id,foo,c1) VALUES (1, 1, REPEAT('1', 4000));
385 COMMIT;
386 CREATE TABLE big
387 (id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000),
388 p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'), SPATIAL INDEX(p))
389 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
390 BEGIN;
391 INSERT INTO big
392 SET id=1, c1=REPEAT('a', 200), c2=REPEAT('b', 200), c3=REPEAT('c', 159);
393 SET @i:=1;
394 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
395 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
396 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
397 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
398 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
399 COMMIT;
400 connection analyze;
401 ANALYZE TABLE big;
402 Table Op Msg_type Msg_text
403 test.big analyze status Engine-independent statistics collected
404 test.big analyze status OK
405 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
406 WHERE name = 'test/big';
407 clust_index_size
408 3
409 connection default;
410 ALTER TABLE big ADD COLUMN
411 (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde',
412 d3 TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp);
413 affected rows: 0
414 info: Records: 0 Duplicates: 0 Warnings: 0
415 CHECKSUM TABLE big;
416 Table Checksum
417 test.big 1705165209
418 BEGIN;
419 INSERT INTO big(id, c1, c2, c3) SELECT @i:=@i+1, c1, c2, c3 FROM big;
420 INSERT INTO big(id, c1, c2, c3) SELECT @i:=@i+1, c1, c2, c3 FROM big;
421 CHECKSUM TABLE big;
422 Table Checksum
423 test.big 385477733
424 connection analyze;
425 ANALYZE TABLE big;
426 Table Op Msg_type Msg_text
427 test.big analyze status Engine-independent statistics collected
428 test.big analyze status OK
429 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
430 WHERE name = 'test/big';
431 clust_index_size
432 7
433 connection default;
434 ROLLBACK;
435 CHECKSUM TABLE big;
436 Table Checksum
437 test.big 1705165209
438 connection analyze;
439 ANALYZE TABLE big;
440 Table Op Msg_type Msg_text
441 test.big analyze status Engine-independent statistics collected
442 test.big analyze status OK
443 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
444 WHERE name = 'test/big';
445 clust_index_size
446 3
447 connection default;
448 InnoDB 0 transactions not purged
449 DROP TABLE t1,t2,t3,t4,big;
450 CREATE TABLE t1 (a VARCHAR(1) PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
451 INSERT INTO t1 SET a='a';
452 ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 0;
453 UPDATE t1 SET b = 1;
454 INSERT INTO t1 SET a='a';
455 ERROR 23000: Duplicate entry 'a' for key 'PRIMARY'
456 SELECT * FROM t1;
457 a b
458 a 1
459 DROP TABLE t1;
460 CREATE TABLE t1 (a INT, b VARCHAR(8), PRIMARY KEY(b,a)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
461 INSERT INTO t1 VALUES (1,'foo');
462 ALTER TABLE t1 ADD COLUMN c INT;
463 UPDATE t1 SET c = 1;
464 UPDATE t1 SET c = 2;
465 DROP TABLE t1;
466 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
467 CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY(b) REFERENCES t1(a))
468 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
469 INSERT INTO t1 SET a=1;
470 INSERT INTO t2 SET b=1;
471 ALTER TABLE t2 ADD COLUMN a INT, DROP FOREIGN KEY t2_ibfk_1;
472 ALTER TABLE t2 ADD INDEX(a);
473 ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a),
474 ALGORITHM=INSTANT;
475 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
476 SET foreign_key_checks=0;
477 ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a),
478 ALGORITHM=INSTANT;
479 ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a),
480 ALGORITHM=INSTANT;
481 SET foreign_key_checks=1;
482 ALTER TABLE t2 COMMENT 'domestic keys only', DROP FOREIGN KEY fk;
483 ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1;
484 ALTER TABLE t1 ADD COLUMN big BLOB NOT NULL
485 DEFAULT REPEAT('a', @@GLOBAL.innodb_page_size * .75);
486 CHECK TABLE t2, t1;
487 Table Op Msg_type Msg_text
488 test.t2 check status OK
489 test.t1 check status OK
490 DROP TABLE t2, t1;
491 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
492 INSERT INTO t1 SET a = 1;
493 ALTER TABLE t1 ADD COLUMN b TEXT;
494 BEGIN;
495 UPDATE t1 SET b = REPEAT('1', 32768);
496 UPDATE t1 SET a = 2;
497 INSERT INTO t1 SET a = 1;
498 SELECT a,LENGTH(b) FROM t1;
499 a LENGTH(b)
500 1 NULL
501 2 32768
502 DELETE FROM t1;
503 COMMIT;
504 InnoDB 0 transactions not purged
505 DROP TABLE t1;
506 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
507 INSERT INTO t1 VALUES (7);
508 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 0;
509 ALTER TABLE t1 ADD INDEX (c);
510 BEGIN;
511 DELETE FROM t1;
512 INSERT INTO t1 VALUES (4,0),(7,77);
513 COMMIT;
514 BEGIN;
515 DELETE FROM t1 WHERE a=7;
516 UPDATE t1 SET a=7;
517 COMMIT;
518 SELECT * FROM t1 FORCE INDEX(PRIMARY);
519 a c
520 7 0
521 SELECT * FROM t1 FORCE INDEX(c);
522 a c
523 7 0
524 DELETE FROM t1;
525 CHECK TABLE t1;
526 Table Op Msg_type Msg_text
527 test.t1 check status OK
528 DROP TABLE t1;
529 SET innodb_strict_mode = ON;
530 CREATE TABLE t1 (a INT, b VARCHAR(500), c TEXT, UNIQUE(a,b)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
531 ALTER TABLE t1 ADD d TEXT;
532 ALTER TABLE t1 ADD PRIMARY KEY (b,a);
533 ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
534 DROP TABLE t1;
535 SET innodb_strict_mode = OFF;
536 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
537 INSERT INTO t1 SET a=42;
538 SET GLOBAL innodb_instant_alter_column_allowed = never;
539 ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
540 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
541 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
542 ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
543 SET GLOBAL innodb_instant_alter_column_allowed = never;
544 ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
545 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
546 ALTER TABLE t1 MODIFY a INT DEFAULT 0;
547 affected rows: 0
548 info: Records: 0 Duplicates: 0 Warnings: 0
549 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
550 ALTER TABLE t1 DROP b, ALGORITHM=INSTANT;
551 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
552 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
553 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
554 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
555 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
556 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
557 ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT;
558 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
559 SET GLOBAL innodb_instant_alter_column_allowed = add_drop_reorder;
560 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
561 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
562 ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
563 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
564 ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT;
565 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
566 ALTER TABLE t1 MODIFY a INT DEFAULT 0, ALGORITHM=INSTANT;
567 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
568 ALTER TABLE t1 MODIFY a INT DEFAULT 0;
569 affected rows: 0
570 info: Records: 0 Duplicates: 0 Warnings: 0
571 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
572 DROP TABLE t1;
573 SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT;
574 CREATE TABLE t1 (a INT, b INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
575 INSERT INTO t1 (a) VALUES (NULL), (NULL);
576 ALTER TABLE t1 DROP a, ADD COLUMN a INT;
577 DELETE FROM t1;
578 BEGIN;
579 INSERT INTO t1 SET a=NULL;
580 ROLLBACK;
581 DELETE FROM t1;
582 DROP TABLE t1;
583 CREATE TABLE t1 (a INT PRIMARY KEY, t VARCHAR(33101) NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
584 INSERT INTO t1 VALUES(347,'');
585 ALTER TABLE t1 DROP COLUMN t, ALGORITHM=INSTANT;
586 SELECT * FROM t1;
587 a
588 347
589 DROP TABLE t1;
590 CREATE TABLE t1 (a INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
591 INSERT INTO t1() VALUES();
592 ALTER TABLE t1 ADD COLUMN b INT FIRST, ADD COLUMN c INT AFTER b;
593 SELECT * FROM t1;
594 b c a
595 NULL NULL NULL
596 DROP TABLE t1;
597 CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
598 SET @t=REPEAT('x',@@innodb_page_size / 2);
599 INSERT INTO t1 VALUES (@t),(@t),(@t),(@t),(@t),(@t),(NULL),(@t),(@t),(@t),(@t);
600 ALTER TABLE t1 ADD COLUMN a INT FIRST;
601 UPDATE t1 SET a = 0;
602 DROP TABLE t1;
603 CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
604 INSERT INTO t1 SET t = @x;
605 ALTER TABLE t1 DROP COLUMN t, ADD COLUMN i INT NOT NULL DEFAULT 1;
606 ALTER TABLE t1 ADD COLUMN t TEXT;
607 SELECT * FROM t1;
608 i t
609 1 NULL
610 DROP TABLE t1;
611 CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
612 INSERT INTO t1 SET a=NULL;
613 ALTER TABLE t1 DROP COLUMN b;
614 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 42;
615 INSERT INTO t1 SET a=NULL;
616 UPDATE t1 SET a=a+2;
617 SELECT * FROM t1;
618 a c
619 3 42
620 4 42
621 DROP TABLE t1;
622 CREATE TABLE t1 (i INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
623 INSERT INTO t1 SET i=1;
624 ALTER TABLE t1 ADD COLUMN b BIT FIRST;
625 ALTER TABLE t1 ADD COLUMN v INT AS (i) VIRTUAL;
626 SELECT * FROM t1;
627 b i v
628 NULL 1 1
629 DROP TABLE t1;
630 CREATE TABLE t1 (ts TIMESTAMP) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
631 ALTER TABLE t1 ADD COLUMN f VARCHAR(8), ADD COLUMN dt DATETIME;
632 ALTER TABLE t1 ADD COLUMN b BIT, DROP COLUMN f, ADD COLUMN t TIME FIRST;
633 ALTER TABLE t1 ADD COLUMN ts2 TIMESTAMP;
634 DROP TABLE t1;
635 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
636 INSERT INTO t1 VALUES (4,4,4);
637 ALTER TABLE t1 DROP f1, DROP f2, ADD f4 INT, ADD f5 INT;
638 DELETE FROM t1;
639 ALTER TABLE t1 DROP COLUMN f4;
640 DROP TABLE t1;
641 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
642 ALTER TABLE t1 DROP f2, ADD COLUMN f4 INT;
643 ALTER TABLE t1 DROP f4;
644 ALTER TABLE t1 DROP f1;
645 DROP TABLE t1;
646 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
647 ALTER TABLE t1 DROP COLUMN id;
648 INSERT INTO t1 () VALUES (),();
649 SELECT * FROM t1;
650 f
651 NULL
652 NULL
653 ALTER TABLE t1 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD KEY(id);
654 SELECT * FROM t1;
655 id f
656 1 NULL
657 2 NULL
658 DROP TABLE t1;
659 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
660 INSERT INTO t1 SET f=NULL;
661 ALTER TABLE t1 DROP COLUMN id;
662 INSERT INTO t1 SET f=NULL;
663 SELECT * FROM t1;
664 f
665 NULL
666 NULL
667 DROP TABLE t1;
668 CREATE TABLE t1(f INT, k INT NOT NULL AUTO_INCREMENT, KEY(k)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
669 ALTER TABLE t1 DROP COLUMN f;
670 INSERT INTO t1 VALUES (1);
671 DROP TABLE t1;
672 CREATE TABLE t1(pk INT PRIMARY KEY, f INT, k INT AUTO_INCREMENT, KEY(k))
673 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
674 ALTER TABLE t1 DROP COLUMN f;
675 INSERT INTO t1 (pk) VALUES (1);
676 DROP TABLE t1;
677 CREATE TABLE t1 (
678 pk INT PRIMARY KEY,
679 f1 INT,
680 f2 CHAR(255),
681 f3 BIGINT,
682 f4 INT,
683 f5 CHAR(255),
684 f6 CHAR(255),
685 f7 CHAR(255) NOT NULL,
686 f8 INT,
687 f9 CHAR(10)
688 ) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
689 INSERT INTO t1 VALUES
690 (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'),
691 (2, 2, 'b', 2, 2, 'b', 'b', 'b', 2, 'b'),
692 (3, 3, 'c', 3, 3, 'c', 'c', 'c', 3, 'c'),
693 (4, 4, 'd', 4, 4, 'd', 'd', 'd', 4, 'd'),
694 (5, 5, 'e', 5, 5, 'e', 'e', 'e', 5, 'e'),
695 (6, 6, 'f', 6, 6, 'f', 'f', 'f', 6, 'f'),
696 (7, 7, 'g', 7, 7, 'g', 'g', 'g', 7, 'g'),
697 (8, 8, 'h', 8, 8, 'h', 'h', 'h', 8, 'h'),
698 (9, 9, 'i', 9, 9, 'i', 'i', 'i', 9, 'i'),
699 (10, 0, 'j', 0, 0, 'j', 'j', 'j', 0, 'j'),
700 (11, 1, 'k', 1, 1, 'k', 'k', 'k', 1, 'k'),
701 (12, 2, 'l', 2, 2, 'l', 'l', 'l', 2, 'l'),
702 (13, 3, 'm', 3, 3, 'm', 'm', 'm', 3, 'm'),
703 (14, 4, 'n', 4, 4, 'n', 'n', 'n', 4, 'n'),
704 (15, 5, 'o', 5, 5, 'o', 'o', 'o', 5, 'o');
705 DELETE FROM t1 WHERE pk=1;
706 InnoDB 0 transactions not purged
707 INSERT INTO t1 VALUES
708 (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a');
709 ALTER TABLE t1 DROP COLUMN f1;
710 DROP TABLE t1;
711 CREATE TABLE t1 (
712 pk INT PRIMARY KEY,
713 f1 INT, f2 CHAR(32) NOT NULL,
714 f3 INT NOT NULL, f4 INT NOT NULL, f5 INT, f6 CHAR(32) NOT NULL,
715 f7 CHAR(32), f8 CHAR(32)
716 ) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
717 INSERT INTO t1 VALUES
718 (1,9,'',2,88,88,'','',''),(2,48,'',8,68,92,'','',''),
719 (3,41,'',56,84,37,'','',''),(4,NULL,'',6,6,NULL,'','',''),
720 (5,52,'',37,44,20,'','',''),(6,44,'',53,4,NULL,'','',''),
721 (7,24,'',54,8,54,'','',''),(8,80,'',3,52,20,'','',''),
722 (9,71,'',34,32,NULL,'','',''),(10,14,'',6,64,88,'','',''),
723 (11,48,'',8,25,42,'','',''),(12,16,'',8,7,NULL,'','',''),
724 (13,NULL,'',22,0,95,'','',''),(14,4,'',72,48,NULL,'','',''),
725 (15,4,'',5,64,2,'','',''),(16,NULL,'',9,40,30,'','',''),
726 (17,92,'',48,2,NULL,'','',''),(18,36,'',48,51,7,'','',''),
727 (19,NULL,'',80,96,NULL,'','',''),(20,96,'',9,80,NULL,'','',''),
728 (21,50,'',16,40,NULL,'','',''),(22,NULL,'',7,84,8,'','',''),
729 (23,28,'',93,80,NULL,'','',''),(24,31,'',40,38,NULL,'','',''),
730 (25,85,'',8,5,88,'','',''),(26,66,'',8,32,4,'','',''),
731 (51,52,'',6,92,15,'','',''),(52,77,'',24,24,28,'','',''),
732 (53,8,'',75,31,NULL,'','',''),(54,48,'',5,8,1,'','',''),
733 (55,90,'',56,12,5,'','',''),(56,92,'',4,9,88,'','',''),
734 (57,83,'',23,40,72,'','',''),(58,7,'',4,40,32,'','',''),
735 (59,28,'',2,3,32,'','',''),(60,16,'',80,4,NULL,'','',''),
736 (61,44,'',88,24,NULL,'','',''),(62,4,'',5,25,3,'','',''),
737 (63,NULL,'',7,24,76,'','',''),(64,0,'',13,40,73,'','',''),
738 (101,NULL,'',1,49,75,'','',''),(102,34,'',10,17,20,'','',''),
739 (103,8,'',2,2,NULL,'','',''),(104,12,'',44,48,52,'','',''),
740 (105,8,'',4,19,38,'','',''),(106,20,'',6,80,9,'','',''),
741 (107,72,'',72,16,56,'','',''),(108,76,'',98,24,21,'','',''),
742 (109,67,'',16,91,NULL,'','',''),(110,72,'',72,3,48,'','',''),
743 (151,8,'',3,86,NULL,'','',''),(152,NULL,'',52,72,0,'','',''),
744 (153,NULL,'',46,30,92,'','',''),(154,80,'',1,40,48,'','',''),
745 (155,24,'',68,68,8,'','',''),(156,85,'',85,72,60,'','',''),
746 (157,7,'',7,12,6,'','',''),(158,NULL,'',48,48,80,'','',''),
747 (159,12,'',0,36,0,'','',''),(160,2,'',6,52,NULL,'','',''),
748 (201,0,'',1,3,NULL,'','',''),(202,NULL,'',3,53,14,'','',''),
749 (203,84,'',6,20,NULL,'','',''),(204,38,'',25,13,88,'','',''),
750 (205,1,'',2,69,5,'','',''),(206,7,'',60,22,NULL,'','',''),
751 (207,NULL,'',5,4,NULL,'','',''),(251,7,'',0,4,40,'','',''),
752 (252,4,'',16,8,NULL,'','',''),(253,14,'',60,12,99,'','',''),
753 (254,84,'',68,16,5,'','',''),(255,3,'',70,36,61,'','',''),
754 (256,7,'',18,48,NULL,'','',''),(257,NULL,'',68,53,NULL,'','',''),
755 (258,29,'',52,16,64,'','',''),(259,NULL,'',80,92,40,'','',''),
756 (301,68,'',1,48,48,'','',''),(302,2,'',1,1,32,'','',''),
757 (303,44,'',60,96,16,'','',''),(304,32,'',52,64,32,'','',''),
758 (305,88,'',37,72,NULL,'','',''),(306,5,'',35,60,20,'','',''),
759 (307,35,'',4,48,NULL,'','',''),(308,4,'',92,44,80,'','',''),
760 (351,48,'',60,4,40,'','',''),(352,7,'',9,61,13,'','',''),
761 (353,0,'',5,93,53,'','',''),(354,7,'',1,20,NULL,'','',''),
762 (355,84,'',5,48,96,'','',''),(356,NULL,'',39,92,36,'','',''),
763 (357,88,'',9,76,44,'','',''),(358,66,'',34,67,80,'','',''),
764 (359,8,'',8,52,NULL,'','',''),(360,3,'',53,83,NULL,'','',''),
765 (361,23,'',44,9,48,'','',''),(362,4,'',0,54,48,'','',''),
766 (363,75,'',66,76,52,'','','');
767 ALTER TABLE t1 ADD COLUMN x VARCHAR(255) DEFAULT ' foobar ';
768 UPDATE t1 SET f1 = 0;
769 ALTER TABLE t1 DROP COLUMN x;
770 DROP TABLE t1;
771 CREATE TABLE t1 (f1 VARCHAR(1), f2 VARCHAR(2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
772 ALTER TABLE t1 MODIFY f2 VARCHAR (8) FIRST;
773 DROP TABLE t1;
774 CREATE TABLE t1 (a INT UNIQUE, b INT UNIQUE, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
775 ALTER TABLE t1 DROP PRIMARY KEY;
776 ALTER TABLE t1 CHANGE COLUMN a a INT;
777 DELETE FROM t1 WHERE a = NULL OR a IS NULL;
778 DROP TABLE t1;
779 CREATE TABLE t1 (a INT, b INT, c INT NOT NULL, d INT,
780 e INT, f INT, g INT, h INT, j INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
781 ALTER TABLE t1 MODIFY COLUMN c INT, MODIFY COLUMN a INT AFTER b;
782 DROP TABLE t1;
783 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
784 INSERT INTO t1 VALUES (0,0);
785 ALTER TABLE t1 MODIFY a INT AFTER b;
786 ALTER TABLE t1 ADD COLUMN v INT AS (a) VIRTUAL;
787 ALTER TABLE t1 MODIFY b INT NOT NULL AFTER a;
788 DROP TABLE t1;
789 CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
790 INSERT INTO t1 VALUES (1);
791 ALTER TABLE t1 ADD COLUMN b INT;
792 ALTER TABLE t1 MODIFY COLUMN a INT NULL;
793 DROP TABLE t1;
794 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
795 INSERT INTO t1 SET a=1;
796 ALTER TABLE t1 DROP c;
797 ALTER TABLE t1 DROP b, ADD v INT AS (a);
798 DROP TABLE t1;
799 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
800 INSERT INTO t1 SET a=1;
801 ALTER TABLE t1 DROP c;
802 ALTER TABLE t1 DROP b, ADD v INT AS (a);
803 DROP TABLE t1;
804 CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, b BLOB NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
805 INSERT INTO t1 VALUES (1,10,REPEAT('foobar',2000));
806 ALTER TABLE t1 DROP COLUMN b;
807 INSERT INTO t1 VALUES (2,20);
808 ALTER TABLE t1 ADD COLUMN vpk INT AS (pk);
809 ALTER TABLE t1 DROP COLUMN i;
810 DROP TABLE t1;
811 CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
812 INSERT INTO t1 VALUES (1,1);
813 ALTER TABLE t1 ADD f DATE AFTER a;
814 ALTER TABLE t1 DROP b, DROP f;
815 DROP TABLE t1;
816 CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
817 INSERT INTO t1 VALUES (1,1);
818 ALTER TABLE t1 ADD COLUMN f INT AFTER a;
819 ALTER TABLE t1 DROP b, DROP f;
820 DROP TABLE t1;
821 CREATE TABLE t1(t TEXT NOT NULL, FULLTEXT(t)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
822 ALTER TABLE t1 MODIFY COLUMN t TEXT;
823 DROP TABLE t1;
824 CREATE TABLE t1 (f TINYINT, g SMALLINT UNSIGNED) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
825 INSERT INTO t1 VALUES(127,6502),(-128,33101);
826 ALTER TABLE t1 MODIFY f SMALLINT DEFAULT 12345,
827 MODIFY g BIGINT UNSIGNED DEFAULT 1234567;
828 affected rows: 2
829 info: Records: 2 Duplicates: 0 Warnings: 0
830 SELECT * FROM t1;
831 f g
832 127 6502
833 -128 33101
834 DROP TABLE t1;
835 CREATE TABLE t1 (f BIT(8)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
836 INSERT INTO t1 VALUES (b'10000000'),(b'00000001');
837 ALTER TABLE t1 MODIFY f BIT(16);
838 affected rows: 2
839 info: Records: 2 Duplicates: 0 Warnings: 0
840 INSERT INTO t1 VALUES (b'1000000010101111'),(b'10000000');
841 SELECT HEX(f) FROM t1;
842 HEX(f)
843 80
844 1
845 80AF
846 80
847 ALTER TABLE t1 MODIFY f SMALLINT;
848 ERROR 22003: Out of range value for column 'f' at row 3
849 ALTER TABLE t1 MODIFY f SMALLINT UNSIGNED;
850 affected rows: 4
851 info: Records: 4 Duplicates: 0 Warnings: 0
852 SELECT * FROM t1;
853 f
854 128
855 1
856 32943
857 128
858 ALTER TABLE t1 MODIFY f BIT;
859 ERROR 22001: Data too long for column 'f' at row 1
860 ALTER TABLE t1 MODIFY f BIT(15);
861 ERROR 22001: Data too long for column 'f' at row 3
862 DELETE FROM t1 LIMIT 3;
863 ALTER TABLE t1 MODIFY f BIT(15);
864 affected rows: 1
865 info: Records: 1 Duplicates: 0 Warnings: 0
866 ALTER TABLE t1 MODIFY f BIT(8);
867 affected rows: 1
868 info: Records: 1 Duplicates: 0 Warnings: 0
869 SELECT HEX(f) FROM t1;
870 HEX(f)
871 80
872 DROP TABLE t1;
873 CREATE TABLE t1 (b BIT NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT DEFAULT CHARSET utf16;
874 INSERT INTO t1 SET b=b'1';
875 ALTER TABLE t1 CHANGE b c BIT NOT NULL;
876 affected rows: 0
877 info: Records: 0 Duplicates: 0 Warnings: 0
878 SELECT HEX(c) FROM t1;
879 HEX(c)
880 1
881 DROP TABLE t1;
882 CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
883 INSERT INTO t1() VALUES();
884 ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42;
885 affected rows: 0
886 info: Records: 0 Duplicates: 0 Warnings: 0
887 ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802,
888 MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory',
889 ADD d DATETIME;
890 affected rows: 1
891 info: Records: 1 Duplicates: 0 Warnings: 0
892 INSERT INTO t1() VALUES();
893 INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now());
894 SELECT * FROM t1;
895 c f d
896 scary -42 NULL
897 gory 64802 NULL
898 fury -8388608 1970-01-01 03:00:42
899 DROP TABLE t1;
900 CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
901 SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
902 WHERE name = 'test/t1';
903 INSERT INTO t1 VALUES (-42, -123456);
904 ALTER TABLE t1 CHANGE t s SMALLINT;
905 affected rows: 1
906 info: Records: 1 Duplicates: 0 Warnings: 0
907 SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
908 WHERE name = 'test/t1';
909 affected rows: 1
910 ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT;
911 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
912 ALTER TABLE t1 CHANGE m i INT;
913 affected rows: 1
914 info: Records: 1 Duplicates: 0 Warnings: 0
915 SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
916 WHERE name = 'test/t1';
917 affected rows: 1
918 SELECT @table_id1 = @table_id2, @table_id2 = @table_id3;
919 @table_id1 = @table_id2 @table_id2 = @table_id3
920 0 0
921 INSERT IGNORE INTO t1 VALUES (0, -123456);
922 Warnings:
923 Warning 1062 Duplicate entry '-123456' for key 'm'
924 REPLACE INTO t1 VALUES(-42, 123456);
925 INSERT IGNORE INTO t1 VALUES(32768, 2147483648);
926 Warnings:
927 Warning 1264 Out of range value for column 's' at row 1
928 Warning 1264 Out of range value for column 'i' at row 1
929 SELECT * FROM t1;
930 s i
931 -42 123456
932 32767 2147483647
933 DROP TABLE t1;
934 CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
935 INSERT INTO t1 (c) VALUES(1),(2),(3);
936 ALTER TABLE t1 MODIFY c BIGINT;
937 affected rows: 3
938 info: Records: 3 Duplicates: 0 Warnings: 0
939 UPDATE t1 SET b=1 WHERE c=2;
940 UPDATE t1 SET c=4 WHERE a=3;
941 UPDATE t1 SET b=2 WHERE c>3;
942 UPDATE t1 SET c=c+1;
943 ERROR 23000: Duplicate entry '2' for key 'c'
944 SELECT * FROM t1;
945 a b c
946 1 NULL 1
947 2 1 2
948 3 2 4
949 DROP TABLE t1;
950 CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
951 INSERT INTO t1 VALUES(1,'a');
952 ALTER TABLE t1 MODIFY b VARCHAR(256), ADD COLUMN c INT;
953 INSERT INTO t1 VALUES(2,'bah',3);
954 SELECT * FROM t1;
955 a b c
956 1 a NULL
957 2 bah 3
958 DROP TABLE t1;
959 CREATE TABLE t1(a CHAR(5) CHARACTER SET utf8 PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
960 INSERT INTO t1 VALUES('barf');
961 ALTER TABLE t1 ADD b INT FIRST, ALGORITHM=INSTANT;
962 ALTER TABLE t1 ADD vb INT AS (b);
963 SELECT * FROM t1;
964 b a vb
965 NULL barf NULL
966 DROP TABLE t1;
967 CREATE TABLE t1 (a INT, b TEXT, PRIMARY KEY(b(9))) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
968 ALTER TABLE t1 ADD COLUMN c TEXT FIRST;
969 ALTER TABLE t1 ADD COLUMN d TEXT GENERATED ALWAYS AS (SUBSTR(b,1,499)) FIRST;
970 DROP TABLE t1;
971 CREATE TABLE t1(a CHAR(5), b INT, c CHAR(1), d CHAR(1), PRIMARY KEY(a,b))
972 DEFAULT CHARACTER SET utf8 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
973 INSERT INTO t1 SET a='fubar',b=42;
974 ALTER TABLE t1 DROP c, DROP d, ALGORITHM=INSTANT;
975 ALTER TABLE t1 ADD vb INT AS (b);
976 SELECT * FROM t1;
977 a b vb
978 fubar 42 42
979 DROP TABLE t1;
980 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
981 ALTER TABLE t1 MODIFY b INT FIRST;
982 DROP TABLE t1;
983 CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
984 INSERT INTO t1 SET pk=1;
985 ALTER TABLE t1 ADD COLUMN b INT;
986 BEGIN;
987 UPDATE t1 SET pk=2;
988 UPDATE t1 SET pk=1;
989 connection analyze;
990 SELECT * FROM t1;
991 pk b
992 1 NULL
993 connection default;
994 DROP TABLE t1;
995 CREATE TABLE t1
996 (id INT PRIMARY KEY, c2 INT UNIQUE,
997 c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'),
998 SPATIAL INDEX(c3)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
999 INSERT INTO t1 (id, c2) values(1,1);
1000 SELECT id,c2,ST_AsText(c3) c3 FROM t1;
1001 id c2 c3
1002 1 1 POINT(3 4)
1003 ALTER TABLE t1 ADD COLUMN (
1004 d1 INT, d2 INT UNSIGNED DEFAULT 10, d3 VARCHAR(20) NOT NULL DEFAULT 'abcde',
1005 d4 TIMESTAMP NOT NULL DEFAULT current_timestamp());
1006 affected rows: 0
1007 info: Records: 0 Duplicates: 0 Warnings: 0
1008 ALTER TABLE t1 ADD INDEX(d3);
1009 affected rows: 0
1010 info: Records: 0 Duplicates: 0 Warnings: 0
1011 BEGIN;
1012 UPDATE t1 SET d3='';
1013 ROLLBACK;
1014 SELECT id,c2,ST_AsText(c3) c3, d1, d2, d3, d4 FROM t1;
1015 id c2 c3 d1 d2 d3 d4
1016 1 1 POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1017 INSERT INTO t1 (id) VALUES(2),(3),(4),(5),(6);
1018 ALTER TABLE t1 CHANGE d1 d1 INT DEFAULT 5, CHANGE d2 d2 INT DEFAULT 15,
1019 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'fghij',
1020 CHANGE d4 dfour TIMESTAMP NOT NULL DEFAULT now();
1021 affected rows: 6
1022 info: Records: 6 Duplicates: 0 Warnings: 0
1023 UPDATE t1 SET d3='foo' WHERE id = 2;
1024 UPDATE t1 SET d3=DEFAULT WHERE id = 4;
1025 INSERT INTO t1 SET id = 7;
1026 SELECT id,c2,ST_AsText(c3) c3, d1, d2, d3, dfour FROM t1;
1027 id c2 c3 d1 d2 d3 dfour
1028 1 1 POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1029 2 NULL POINT(3 4) NULL 10 foo 1970-01-01 03:00:42
1030 3 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1031 4 NULL POINT(3 4) NULL 10 fghij 1970-01-01 03:00:42
1032 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1033 6 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1034 7 NULL POINT(3 4) 5 15 fghij 1970-01-01 03:00:42
1035 CHECK TABLE t1;
1036 Table Op Msg_type Msg_text
1037 test.t1 check status OK
1038 ALTER TABLE t1 ADD COLUMN e1 INT AS (id * 3);
1039 affected rows: 0
1040 info: Records: 0 Duplicates: 0 Warnings: 0
1041 ALTER TABLE t1 ADD COLUMN e2 VARCHAR(30) AS (d3);
1042 affected rows: 0
1043 info: Records: 0 Duplicates: 0 Warnings: 0
1044 ALTER TABLE t1 ADD COLUMN e3 INT AS (id * 2);
1045 affected rows: 0
1046 info: Records: 0 Duplicates: 0 Warnings: 0
1047 ALTER TABLE t1 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'foobar',
1048 ADD COLUMN (d5 CHAR(20) DEFAULT 'hijkl', d6 INT DEFAULT -12345, d7 INT),
1049 DROP INDEX d3;
1050 affected rows: 0
1051 info: Records: 0 Duplicates: 0 Warnings: 0
1052 INSERT INTO t1 SET id = 8;
1053 UPDATE t1 SET d3 = 'yyyyy' WHERE id = 1;
1054 UPDATE t1 SET d3 = 'xxxxx' WHERE id = 2;
1055 BEGIN;
1056 UPDATE t1 SET d3 = 'xxxxx' WHERE id = 3;
1057 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 3;
1058 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
1059 3 NULL POINT(3 4) NULL 10 xxxxx 1970-01-01 03:00:42 9 xxxxx 6 hijkl -12345 NULL
1060 ROLLBACK;
1061 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 3;
1062 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
1063 3 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 9 abcde 6 hijkl -12345 NULL
1064 BEGIN;
1065 UPDATE t1 SET d7 = NULL WHERE ID = 5;
1066 ROLLBACK;
1067 BEGIN;
1068 UPDATE t1 SET d7 = NULL, d6 = 10 WHERE id = 5;
1069 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 5;
1070 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
1071 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl 10 NULL
1072 ROLLBACK;
1073 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 5;
1074 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
1075 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl -12345 NULL
1076 ALTER TABLE t1 ADD COLUMN (f1 VARCHAR(20) AS (concat('x', e2)) STORED);
1077 affected rows: 8
1078 info: Records: 8 Duplicates: 0 Warnings: 0
1079 ALTER TABLE t1 ADD COLUMN (d8 VARCHAR(20) DEFAULT 'omnopq');
1080 affected rows: 0
1081 info: Records: 0 Duplicates: 0 Warnings: 0
1082 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7, f1, d8 FROM t1;
1083 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7 f1 d8
1084 1 1 POINT(3 4) NULL 10 yyyyy 1970-01-01 03:00:42 3 yyyyy 2 hijkl -12345 NULL xyyyyy omnopq
1085 2 NULL POINT(3 4) NULL 10 xxxxx 1970-01-01 03:00:42 6 xxxxx 4 hijkl -12345 NULL xxxxxx omnopq
1086 3 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 9 abcde 6 hijkl -12345 NULL xabcde omnopq
1087 4 NULL POINT(3 4) NULL 10 fghij 1970-01-01 03:00:42 12 fghij 8 hijkl -12345 NULL xfghij omnopq
1088 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl -12345 NULL xabcde omnopq
1089 6 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 18 abcde 12 hijkl -12345 NULL xabcde omnopq
1090 7 NULL POINT(3 4) 5 15 fghij 1970-01-01 03:00:42 21 fghij 14 hijkl -12345 NULL xfghij omnopq
1091 8 NULL POINT(3 4) 5 15 foobar 1970-01-01 03:00:42 24 foobar 16 hijkl -12345 NULL xfoobar omnopq
1092 SHOW CREATE TABLE t1;
1093 Table Create Table
1094 t1 CREATE TABLE `t1` (
1095 `id` int(11) NOT NULL,
1096 `c2` int(11) DEFAULT NULL,
1097 `c3` point NOT NULL DEFAULT st_geometryfromtext('POINT(3 4)'),
1098 `d1` int(11) DEFAULT 5,
1099 `d2` int(11) DEFAULT 15,
1100 `d3` varchar(20) NOT NULL DEFAULT 'foobar',
1101 `dfour` timestamp NOT NULL DEFAULT current_timestamp(),
1102 `e1` int(11) GENERATED ALWAYS AS (`id` * 3) VIRTUAL,
1103 `e2` varchar(30) GENERATED ALWAYS AS (`d3`) VIRTUAL,
1104 `e3` int(11) GENERATED ALWAYS AS (`id` * 2) VIRTUAL,
1105 `d5` char(20) DEFAULT 'hijkl',
1106 `d6` int(11) DEFAULT -12345,
1107 `d7` int(11) DEFAULT NULL,
1108 `f1` varchar(20) GENERATED ALWAYS AS (concat('x',`e2`)) STORED,
1109 `d8` varchar(20) DEFAULT 'omnopq',
1110 PRIMARY KEY (`id`),
1111 UNIQUE KEY `c2` (`c2`),
1112 SPATIAL KEY `c3` (`c3`)
1113 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPACT
1114 ALTER TABLE t1
1115 CHANGE c2 c2 INT DEFAULT 42,
1116 CHANGE d1 d1 INT DEFAULT 1,
1117 CHANGE d2 d2 INT DEFAULT 20,
1118 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'boofar';
1119 affected rows: 0
1120 info: Records: 0 Duplicates: 0 Warnings: 0
1121 INSERT INTO t1 SET id=9;
1122 ALTER TABLE t1 DROP c3;
1123 affected rows: 0
1124 info: Records: 0 Duplicates: 0 Warnings: 0
1125 SHOW CREATE TABLE t1;
1126 Table Create Table
1127 t1 CREATE TABLE `t1` (
1128 `id` int(11) NOT NULL,
1129 `c2` int(11) DEFAULT 42,
1130 `d1` int(11) DEFAULT 1,
1131 `d2` int(11) DEFAULT 20,
1132 `d3` varchar(20) NOT NULL DEFAULT 'boofar',
1133 `dfour` timestamp NOT NULL DEFAULT current_timestamp(),
1134 `e1` int(11) GENERATED ALWAYS AS (`id` * 3) VIRTUAL,
1135 `e2` varchar(30) GENERATED ALWAYS AS (`d3`) VIRTUAL,
1136 `e3` int(11) GENERATED ALWAYS AS (`id` * 2) VIRTUAL,
1137 `d5` char(20) DEFAULT 'hijkl',
1138 `d6` int(11) DEFAULT -12345,
1139 `d7` int(11) DEFAULT NULL,
1140 `f1` varchar(20) GENERATED ALWAYS AS (concat('x',`e2`)) STORED,
1141 `d8` varchar(20) DEFAULT 'omnopq',
1142 PRIMARY KEY (`id`),
1143 UNIQUE KEY `c2` (`c2`)
1144 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPACT
1145 SELECT * FROM t1;
1146 id c2 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7 f1 d8
1147 1 1 NULL 10 yyyyy 1970-01-01 03:00:42 3 yyyyy 2 hijkl -12345 NULL xyyyyy omnopq
1148 2 NULL NULL 10 xxxxx 1970-01-01 03:00:42 6 xxxxx 4 hijkl -12345 NULL xxxxxx omnopq
1149 3 NULL NULL 10 abcde 1970-01-01 03:00:42 9 abcde 6 hijkl -12345 NULL xabcde omnopq
1150 4 NULL NULL 10 fghij 1970-01-01 03:00:42 12 fghij 8 hijkl -12345 NULL xfghij omnopq
1151 5 NULL NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl -12345 NULL xabcde omnopq
1152 6 NULL NULL 10 abcde 1970-01-01 03:00:42 18 abcde 12 hijkl -12345 NULL xabcde omnopq
1153 7 NULL 5 15 fghij 1970-01-01 03:00:42 21 fghij 14 hijkl -12345 NULL xfghij omnopq
1154 8 NULL 5 15 foobar 1970-01-01 03:00:42 24 foobar 16 hijkl -12345 NULL xfoobar omnopq
1155 9 42 1 20 boofar 1970-01-01 03:00:42 27 boofar 18 hijkl -12345 NULL xboofar omnopq
1156 CREATE TABLE t2
1157 (id INT primary key, c1 VARCHAR(4000),
1158 p GEOMETRY NOT NULL DEFAULT ST_GeomFromText('LINESTRING(0 0,0 1,1 1)'),
1159 SPATIAL INDEX(p))
1160 ENGINE=InnoDB ROW_FORMAT=COMPACT;
1161 BEGIN;
1162 INSERT INTO t2 SET id=1, c1=REPEAT('a', 4000);
1163 INSERT INTO t2 SET id=2, c1=REPEAT('a', 4000), p=ST_GeomFromText('POINT(1 1)');
1164 COMMIT;
1165 ALTER TABLE t2 ADD COLUMN d1 VARCHAR(2000) DEFAULT REPEAT('asdf',500);
1166 affected rows: 0
1167 info: Records: 0 Duplicates: 0 Warnings: 0
1168 SELECT id, c1, ST_AsText(p) p, d1 FROM t2;
1169 id c1 p d1
1170 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa LINESTRING(0 0,0 1,1 1) asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
1171 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa POINT(1 1) asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
1172 BEGIN;
1173 UPDATE t2 SET c1 = repeat(id, 4000);
1174 connection analyze;
1175 ANALYZE TABLE t2;
1176 Table Op Msg_type Msg_text
1177 test.t2 analyze status Engine-independent statistics collected
1178 test.t2 analyze status OK
1179 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1180 WHERE name = 'test/t2';
1181 clust_index_size
1182 1
1183 connection default;
1184 ROLLBACK;
1185 connection analyze;
1186 ANALYZE TABLE t2;
1187 Table Op Msg_type Msg_text
1188 test.t2 analyze status Engine-independent statistics collected
1189 test.t2 analyze status OK
1190 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1191 WHERE name = 'test/t2';
1192 clust_index_size
1193 1
1194 connection default;
1195 BEGIN;
1196 UPDATE t2 SET d1 = repeat(id, 200);
1197 connection analyze;
1198 ANALYZE TABLE t2;
1199 Table Op Msg_type Msg_text
1200 test.t2 analyze status Engine-independent statistics collected
1201 test.t2 analyze status OK
1202 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1203 WHERE name = 'test/t2';
1204 clust_index_size
1205 1
1206 connection default;
1207 ROLLBACK;
1208 connection analyze;
1209 ANALYZE TABLE t2;
1210 Table Op Msg_type Msg_text
1211 test.t2 analyze status Engine-independent statistics collected
1212 test.t2 analyze status OK
1213 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1214 WHERE name = 'test/t2';
1215 clust_index_size
1216 1
1217 connection default;
1218 ALTER TABLE t2 DROP p;
1219 affected rows: 0
1220 info: Records: 0 Duplicates: 0 Warnings: 0
1221 SELECT * FROM t2;
1222 id c1 d1
1223 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
1224 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
1225 CREATE TABLE t3
1226 (id INT PRIMARY KEY, c2 INT UNSIGNED NOT NULL UNIQUE,
1227 c3 POLYGON NOT NULL DEFAULT ST_PolyFromText('POLYGON((1 1,2 2,3 3,1 1))'),
1228 SPATIAL INDEX(c3))
1229 ENGINE=InnoDB ROW_FORMAT=COMPACT;
1230 INSERT INTO t3(id,c2) VALUES(1,1),(2,2),(3,3);
1231 SELECT id, c2, ST_AsText(c3) c3 FROM t3;
1232 id c2 c3
1233 1 1 POLYGON((1 1,2 2,3 3,1 1))
1234 2 2 POLYGON((1 1,2 2,3 3,1 1))
1235 3 3 POLYGON((1 1,2 2,3 3,1 1))
1236 ALTER TABLE t3 ADD COLUMN
1237 (c4 DATETIME DEFAULT current_timestamp(),
1238 c5 TIMESTAMP NOT NULL DEFAULT current_timestamp(),
1239 c6 POINT);
1240 affected rows: 0
1241 info: Records: 0 Duplicates: 0 Warnings: 0
1242 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6 FROM t3;
1243 id c2 c3 c4 c5 c6
1244 1 1 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL
1245 2 2 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL
1246 3 3 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL
1247 affected rows: 3
1248 ALTER TABLE t3 ADD COLUMN c7 TIME NOT NULL DEFAULT current_timestamp();
1249 affected rows: 0
1250 info: Records: 0 Duplicates: 0 Warnings: 1
1251 Warnings:
1252 Note 1265 Data truncated for column 'c7' at row 0
1253 ALTER TABLE t3 ADD COLUMN c8 DATE NOT NULL DEFAULT current_timestamp();
1254 affected rows: 0
1255 info: Records: 0 Duplicates: 0 Warnings: 1
1256 Warnings:
1257 Note 1265 Data truncated for column 'c8' at row 0
1258 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6, c7, c8 FROM t3;
1259 id c2 c3 c4 c5 c6 c7 c8
1260 1 1 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01
1261 2 2 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01
1262 3 3 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01
1263 ALTER TABLE t3 ADD COLUMN t TEXT CHARSET utf8
1264 DEFAULT 'The quick brown fox jumps over the lazy dog';
1265 affected rows: 0
1266 info: Records: 0 Duplicates: 0 Warnings: 0
1267 ALTER TABLE t3 ADD COLUMN b BLOB NOT NULL;
1268 affected rows: 0
1269 info: Records: 0 Duplicates: 0 Warnings: 0
1270 INSERT INTO t3 SET id=4;
1271 ERROR HY000: Field 'c2' doesn't have a default value
1272 INSERT INTO t3 SET id=4, c2=0, b=0xf09f98b1;
1273 affected rows: 1
1274 Warnings:
1275 Note 1265 Data truncated for column 'c7' at row 1
1276 Note 1265 Data truncated for column 'c8' at row 1
1277 SET innodb_strict_mode = OFF;
1278 affected rows: 0
1279 ALTER TABLE t3 CHANGE t phrase TEXT DEFAULT 0xc3a4c3a448,
1280 CHANGE b b BLOB NOT NULL DEFAULT 'binary line of business';
1281 affected rows: 4
1282 info: Records: 4 Duplicates: 0 Warnings: 0
1283 INSERT INTO t3 SET id=5, c2=9;
1284 Warnings:
1285 Note 1265 Data truncated for column 'c7' at row 1
1286 Note 1265 Data truncated for column 'c8' at row 1
1287 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6, c7, c8, phrase, b FROM t3;
1288 id c2 c3 c4 c5 c6 c7 c8 phrase b
1289 1 1 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog
1290 2 2 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog
1291 3 3 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog
1292 4 0 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog 😱
1293 5 9 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 ääH binary line of business
1294 ALTER TABLE t3 DROP c3, DROP c7;
1295 affected rows: 0
1296 info: Records: 0 Duplicates: 0 Warnings: 0
1297 SELECT * FROM t3;
1298 id c2 c4 c5 c6 c8 phrase b
1299 1 1 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog
1300 2 2 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog
1301 3 3 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog
1302 4 0 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog 😱
1303 5 9 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 ääH binary line of business
1304 CREATE TABLE t4
1305 (id INT, foo INT DEFAULT 0, c1 VARCHAR(4000),
1306 p GEOMETRY NOT NULL DEFAULT ST_GeomFromText('LINESTRING(0 0,0 1,1 1)'),
1307 PRIMARY KEY(id,foo))
1308 ENGINE=InnoDB ROW_FORMAT=COMPACT;
1309 INSERT INTO t4 (id,c1) VALUES (1, REPEAT('a', 4000)), (2, REPEAT('a', 4000));
1310 ALTER TABLE t4 ADD COLUMN d1 INT;
1311 BEGIN;
1312 UPDATE t4 SET c1 = repeat('1', 4000), foo=1 WHERE id=1;
1313 INSERT INTO t4 (id,c1) VALUES (1, REPEAT('a', 4000));
1314 UPDATE t4 SET c1 = repeat('2', 4000), foo=1 WHERE id=2;
1315 ROLLBACK;
1316 BEGIN;
1317 UPDATE t4 SET d1 = 1,foo=2 WHERE id=1;
1318 INSERT INTO t4 (id,foo,c1) VALUES (1, 1, REPEAT('1', 4000));
1319 COMMIT;
1320 CREATE TABLE big
1321 (id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000),
1322 p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'), SPATIAL INDEX(p))
1323 ENGINE=InnoDB ROW_FORMAT=COMPACT;
1324 BEGIN;
1325 INSERT INTO big
1326 SET id=1, c1=REPEAT('a', 200), c2=REPEAT('b', 200), c3=REPEAT('c', 159);
1327 SET @i:=1;
1328 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
1329 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
1330 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
1331 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
1332 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
1333 COMMIT;
1334 connection analyze;
1335 ANALYZE TABLE big;
1336 Table Op Msg_type Msg_text
1337 test.big analyze status Engine-independent statistics collected
1338 test.big analyze status OK
1339 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1340 WHERE name = 'test/big';
1341 clust_index_size
1342 3
1343 connection default;
1344 ALTER TABLE big ADD COLUMN
1345 (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde',
1346 d3 TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp);
1347 affected rows: 0
1348 info: Records: 0 Duplicates: 0 Warnings: 0
1349 CHECKSUM TABLE big;
1350 Table Checksum
1351 test.big 1705165209
1352 BEGIN;
1353 INSERT INTO big(id, c1, c2, c3) SELECT @i:=@i+1, c1, c2, c3 FROM big;
1354 INSERT INTO big(id, c1, c2, c3) SELECT @i:=@i+1, c1, c2, c3 FROM big;
1355 CHECKSUM TABLE big;
1356 Table Checksum
1357 test.big 385477733
1358 connection analyze;
1359 ANALYZE TABLE big;
1360 Table Op Msg_type Msg_text
1361 test.big analyze status Engine-independent statistics collected
1362 test.big analyze status OK
1363 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1364 WHERE name = 'test/big';
1365 clust_index_size
1366 7
1367 connection default;
1368 ROLLBACK;
1369 CHECKSUM TABLE big;
1370 Table Checksum
1371 test.big 1705165209
1372 connection analyze;
1373 ANALYZE TABLE big;
1374 Table Op Msg_type Msg_text
1375 test.big analyze status Engine-independent statistics collected
1376 test.big analyze status OK
1377 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1378 WHERE name = 'test/big';
1379 clust_index_size
1380 3
1381 connection default;
1382 InnoDB 0 transactions not purged
1383 DROP TABLE t1,t2,t3,t4,big;
1384 CREATE TABLE t1 (a VARCHAR(1) PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1385 INSERT INTO t1 SET a='a';
1386 ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 0;
1387 UPDATE t1 SET b = 1;
1388 INSERT INTO t1 SET a='a';
1389 ERROR 23000: Duplicate entry 'a' for key 'PRIMARY'
1390 SELECT * FROM t1;
1391 a b
1392 a 1
1393 DROP TABLE t1;
1394 CREATE TABLE t1 (a INT, b VARCHAR(8), PRIMARY KEY(b,a)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1395 INSERT INTO t1 VALUES (1,'foo');
1396 ALTER TABLE t1 ADD COLUMN c INT;
1397 UPDATE t1 SET c = 1;
1398 UPDATE t1 SET c = 2;
1399 DROP TABLE t1;
1400 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1401 CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY(b) REFERENCES t1(a))
1402 ENGINE=InnoDB ROW_FORMAT=COMPACT;
1403 INSERT INTO t1 SET a=1;
1404 INSERT INTO t2 SET b=1;
1405 ALTER TABLE t2 ADD COLUMN a INT, DROP FOREIGN KEY t2_ibfk_1;
1406 ALTER TABLE t2 ADD INDEX(a);
1407 ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a),
1408 ALGORITHM=INSTANT;
1409 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
1410 SET foreign_key_checks=0;
1411 ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a),
1412 ALGORITHM=INSTANT;
1413 ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a),
1414 ALGORITHM=INSTANT;
1415 SET foreign_key_checks=1;
1416 ALTER TABLE t2 COMMENT 'domestic keys only', DROP FOREIGN KEY fk;
1417 ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1;
1418 ALTER TABLE t1 ADD COLUMN big BLOB NOT NULL
1419 DEFAULT REPEAT('a', @@GLOBAL.innodb_page_size * .75);
1420 CHECK TABLE t2, t1;
1421 Table Op Msg_type Msg_text
1422 test.t2 check status OK
1423 test.t1 check status OK
1424 DROP TABLE t2, t1;
1425 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1426 INSERT INTO t1 SET a = 1;
1427 ALTER TABLE t1 ADD COLUMN b TEXT;
1428 BEGIN;
1429 UPDATE t1 SET b = REPEAT('1', 32768);
1430 UPDATE t1 SET a = 2;
1431 INSERT INTO t1 SET a = 1;
1432 SELECT a,LENGTH(b) FROM t1;
1433 a LENGTH(b)
1434 1 NULL
1435 2 32768
1436 DELETE FROM t1;
1437 COMMIT;
1438 InnoDB 0 transactions not purged
1439 DROP TABLE t1;
1440 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1441 INSERT INTO t1 VALUES (7);
1442 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 0;
1443 ALTER TABLE t1 ADD INDEX (c);
1444 BEGIN;
1445 DELETE FROM t1;
1446 INSERT INTO t1 VALUES (4,0),(7,77);
1447 COMMIT;
1448 BEGIN;
1449 DELETE FROM t1 WHERE a=7;
1450 UPDATE t1 SET a=7;
1451 COMMIT;
1452 SELECT * FROM t1 FORCE INDEX(PRIMARY);
1453 a c
1454 7 0
1455 SELECT * FROM t1 FORCE INDEX(c);
1456 a c
1457 7 0
1458 DELETE FROM t1;
1459 CHECK TABLE t1;
1460 Table Op Msg_type Msg_text
1461 test.t1 check status OK
1462 DROP TABLE t1;
1463 SET innodb_strict_mode = ON;
1464 CREATE TABLE t1 (a INT, b VARCHAR(500), c TEXT, UNIQUE(a,b)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1465 ALTER TABLE t1 ADD d TEXT;
1466 ALTER TABLE t1 ADD PRIMARY KEY (b,a);
1467 ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
1468 DROP TABLE t1;
1469 SET innodb_strict_mode = OFF;
1470 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1471 INSERT INTO t1 SET a=42;
1472 SET GLOBAL innodb_instant_alter_column_allowed = never;
1473 ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
1474 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
1475 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
1476 ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
1477 SET GLOBAL innodb_instant_alter_column_allowed = never;
1478 ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
1479 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
1480 ALTER TABLE t1 MODIFY a INT DEFAULT 0;
1481 affected rows: 0
1482 info: Records: 0 Duplicates: 0 Warnings: 0
1483 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
1484 ALTER TABLE t1 DROP b, ALGORITHM=INSTANT;
1485 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
1486 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
1487 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
1488 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
1489 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
1490 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
1491 ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT;
1492 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
1493 SET GLOBAL innodb_instant_alter_column_allowed = add_drop_reorder;
1494 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
1495 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
1496 ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
1497 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
1498 ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT;
1499 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
1500 ALTER TABLE t1 MODIFY a INT DEFAULT 0, ALGORITHM=INSTANT;
1501 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
1502 ALTER TABLE t1 MODIFY a INT DEFAULT 0;
1503 affected rows: 0
1504 info: Records: 0 Duplicates: 0 Warnings: 0
1505 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
1506 DROP TABLE t1;
1507 SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT;
1508 CREATE TABLE t1 (a INT, b INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1509 INSERT INTO t1 (a) VALUES (NULL), (NULL);
1510 ALTER TABLE t1 DROP a, ADD COLUMN a INT;
1511 DELETE FROM t1;
1512 BEGIN;
1513 INSERT INTO t1 SET a=NULL;
1514 ROLLBACK;
1515 DELETE FROM t1;
1516 DROP TABLE t1;
1517 CREATE TABLE t1 (a INT PRIMARY KEY, t VARCHAR(33101) NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1518 INSERT INTO t1 VALUES(347,'');
1519 ALTER TABLE t1 DROP COLUMN t, ALGORITHM=INSTANT;
1520 SELECT * FROM t1;
1521 a
1522 347
1523 DROP TABLE t1;
1524 CREATE TABLE t1 (a INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1525 INSERT INTO t1() VALUES();
1526 ALTER TABLE t1 ADD COLUMN b INT FIRST, ADD COLUMN c INT AFTER b;
1527 SELECT * FROM t1;
1528 b c a
1529 NULL NULL NULL
1530 DROP TABLE t1;
1531 CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1532 SET @t=REPEAT('x',@@innodb_page_size / 2);
1533 INSERT INTO t1 VALUES (@t),(@t),(@t),(@t),(@t),(@t),(NULL),(@t),(@t),(@t),(@t);
1534 ALTER TABLE t1 ADD COLUMN a INT FIRST;
1535 UPDATE t1 SET a = 0;
1536 DROP TABLE t1;
1537 CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1538 INSERT INTO t1 SET t = @x;
1539 ALTER TABLE t1 DROP COLUMN t, ADD COLUMN i INT NOT NULL DEFAULT 1;
1540 ALTER TABLE t1 ADD COLUMN t TEXT;
1541 SELECT * FROM t1;
1542 i t
1543 1 NULL
1544 DROP TABLE t1;
1545 CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1546 INSERT INTO t1 SET a=NULL;
1547 ALTER TABLE t1 DROP COLUMN b;
1548 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 42;
1549 INSERT INTO t1 SET a=NULL;
1550 UPDATE t1 SET a=a+2;
1551 SELECT * FROM t1;
1552 a c
1553 3 42
1554 4 42
1555 DROP TABLE t1;
1556 CREATE TABLE t1 (i INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1557 INSERT INTO t1 SET i=1;
1558 ALTER TABLE t1 ADD COLUMN b BIT FIRST;
1559 ALTER TABLE t1 ADD COLUMN v INT AS (i) VIRTUAL;
1560 SELECT * FROM t1;
1561 b i v
1562 NULL 1 1
1563 DROP TABLE t1;
1564 CREATE TABLE t1 (ts TIMESTAMP) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1565 ALTER TABLE t1 ADD COLUMN f VARCHAR(8), ADD COLUMN dt DATETIME;
1566 ALTER TABLE t1 ADD COLUMN b BIT, DROP COLUMN f, ADD COLUMN t TIME FIRST;
1567 ALTER TABLE t1 ADD COLUMN ts2 TIMESTAMP;
1568 DROP TABLE t1;
1569 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1570 INSERT INTO t1 VALUES (4,4,4);
1571 ALTER TABLE t1 DROP f1, DROP f2, ADD f4 INT, ADD f5 INT;
1572 DELETE FROM t1;
1573 ALTER TABLE t1 DROP COLUMN f4;
1574 DROP TABLE t1;
1575 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1576 ALTER TABLE t1 DROP f2, ADD COLUMN f4 INT;
1577 ALTER TABLE t1 DROP f4;
1578 ALTER TABLE t1 DROP f1;
1579 DROP TABLE t1;
1580 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1581 ALTER TABLE t1 DROP COLUMN id;
1582 INSERT INTO t1 () VALUES (),();
1583 SELECT * FROM t1;
1584 f
1585 NULL
1586 NULL
1587 ALTER TABLE t1 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD KEY(id);
1588 SELECT * FROM t1;
1589 id f
1590 1 NULL
1591 2 NULL
1592 DROP TABLE t1;
1593 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1594 INSERT INTO t1 SET f=NULL;
1595 ALTER TABLE t1 DROP COLUMN id;
1596 INSERT INTO t1 SET f=NULL;
1597 SELECT * FROM t1;
1598 f
1599 NULL
1600 NULL
1601 DROP TABLE t1;
1602 CREATE TABLE t1(f INT, k INT NOT NULL AUTO_INCREMENT, KEY(k)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1603 ALTER TABLE t1 DROP COLUMN f;
1604 INSERT INTO t1 VALUES (1);
1605 DROP TABLE t1;
1606 CREATE TABLE t1(pk INT PRIMARY KEY, f INT, k INT AUTO_INCREMENT, KEY(k))
1607 ENGINE=InnoDB ROW_FORMAT=COMPACT;
1608 ALTER TABLE t1 DROP COLUMN f;
1609 INSERT INTO t1 (pk) VALUES (1);
1610 DROP TABLE t1;
1611 CREATE TABLE t1 (
1612 pk INT PRIMARY KEY,
1613 f1 INT,
1614 f2 CHAR(255),
1615 f3 BIGINT,
1616 f4 INT,
1617 f5 CHAR(255),
1618 f6 CHAR(255),
1619 f7 CHAR(255) NOT NULL,
1620 f8 INT,
1621 f9 CHAR(10)
1622 ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1623 INSERT INTO t1 VALUES
1624 (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'),
1625 (2, 2, 'b', 2, 2, 'b', 'b', 'b', 2, 'b'),
1626 (3, 3, 'c', 3, 3, 'c', 'c', 'c', 3, 'c'),
1627 (4, 4, 'd', 4, 4, 'd', 'd', 'd', 4, 'd'),
1628 (5, 5, 'e', 5, 5, 'e', 'e', 'e', 5, 'e'),
1629 (6, 6, 'f', 6, 6, 'f', 'f', 'f', 6, 'f'),
1630 (7, 7, 'g', 7, 7, 'g', 'g', 'g', 7, 'g'),
1631 (8, 8, 'h', 8, 8, 'h', 'h', 'h', 8, 'h'),
1632 (9, 9, 'i', 9, 9, 'i', 'i', 'i', 9, 'i'),
1633 (10, 0, 'j', 0, 0, 'j', 'j', 'j', 0, 'j'),
1634 (11, 1, 'k', 1, 1, 'k', 'k', 'k', 1, 'k'),
1635 (12, 2, 'l', 2, 2, 'l', 'l', 'l', 2, 'l'),
1636 (13, 3, 'm', 3, 3, 'm', 'm', 'm', 3, 'm'),
1637 (14, 4, 'n', 4, 4, 'n', 'n', 'n', 4, 'n'),
1638 (15, 5, 'o', 5, 5, 'o', 'o', 'o', 5, 'o');
1639 DELETE FROM t1 WHERE pk=1;
1640 InnoDB 0 transactions not purged
1641 INSERT INTO t1 VALUES
1642 (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a');
1643 ALTER TABLE t1 DROP COLUMN f1;
1644 DROP TABLE t1;
1645 CREATE TABLE t1 (
1646 pk INT PRIMARY KEY,
1647 f1 INT, f2 CHAR(32) NOT NULL,
1648 f3 INT NOT NULL, f4 INT NOT NULL, f5 INT, f6 CHAR(32) NOT NULL,
1649 f7 CHAR(32), f8 CHAR(32)
1650 ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1651 INSERT INTO t1 VALUES
1652 (1,9,'',2,88,88,'','',''),(2,48,'',8,68,92,'','',''),
1653 (3,41,'',56,84,37,'','',''),(4,NULL,'',6,6,NULL,'','',''),
1654 (5,52,'',37,44,20,'','',''),(6,44,'',53,4,NULL,'','',''),
1655 (7,24,'',54,8,54,'','',''),(8,80,'',3,52,20,'','',''),
1656 (9,71,'',34,32,NULL,'','',''),(10,14,'',6,64,88,'','',''),
1657 (11,48,'',8,25,42,'','',''),(12,16,'',8,7,NULL,'','',''),
1658 (13,NULL,'',22,0,95,'','',''),(14,4,'',72,48,NULL,'','',''),
1659 (15,4,'',5,64,2,'','',''),(16,NULL,'',9,40,30,'','',''),
1660 (17,92,'',48,2,NULL,'','',''),(18,36,'',48,51,7,'','',''),
1661 (19,NULL,'',80,96,NULL,'','',''),(20,96,'',9,80,NULL,'','',''),
1662 (21,50,'',16,40,NULL,'','',''),(22,NULL,'',7,84,8,'','',''),
1663 (23,28,'',93,80,NULL,'','',''),(24,31,'',40,38,NULL,'','',''),
1664 (25,85,'',8,5,88,'','',''),(26,66,'',8,32,4,'','',''),
1665 (51,52,'',6,92,15,'','',''),(52,77,'',24,24,28,'','',''),
1666 (53,8,'',75,31,NULL,'','',''),(54,48,'',5,8,1,'','',''),
1667 (55,90,'',56,12,5,'','',''),(56,92,'',4,9,88,'','',''),
1668 (57,83,'',23,40,72,'','',''),(58,7,'',4,40,32,'','',''),
1669 (59,28,'',2,3,32,'','',''),(60,16,'',80,4,NULL,'','',''),
1670 (61,44,'',88,24,NULL,'','',''),(62,4,'',5,25,3,'','',''),
1671 (63,NULL,'',7,24,76,'','',''),(64,0,'',13,40,73,'','',''),
1672 (101,NULL,'',1,49,75,'','',''),(102,34,'',10,17,20,'','',''),
1673 (103,8,'',2,2,NULL,'','',''),(104,12,'',44,48,52,'','',''),
1674 (105,8,'',4,19,38,'','',''),(106,20,'',6,80,9,'','',''),
1675 (107,72,'',72,16,56,'','',''),(108,76,'',98,24,21,'','',''),
1676 (109,67,'',16,91,NULL,'','',''),(110,72,'',72,3,48,'','',''),
1677 (151,8,'',3,86,NULL,'','',''),(152,NULL,'',52,72,0,'','',''),
1678 (153,NULL,'',46,30,92,'','',''),(154,80,'',1,40,48,'','',''),
1679 (155,24,'',68,68,8,'','',''),(156,85,'',85,72,60,'','',''),
1680 (157,7,'',7,12,6,'','',''),(158,NULL,'',48,48,80,'','',''),
1681 (159,12,'',0,36,0,'','',''),(160,2,'',6,52,NULL,'','',''),
1682 (201,0,'',1,3,NULL,'','',''),(202,NULL,'',3,53,14,'','',''),
1683 (203,84,'',6,20,NULL,'','',''),(204,38,'',25,13,88,'','',''),
1684 (205,1,'',2,69,5,'','',''),(206,7,'',60,22,NULL,'','',''),
1685 (207,NULL,'',5,4,NULL,'','',''),(251,7,'',0,4,40,'','',''),
1686 (252,4,'',16,8,NULL,'','',''),(253,14,'',60,12,99,'','',''),
1687 (254,84,'',68,16,5,'','',''),(255,3,'',70,36,61,'','',''),
1688 (256,7,'',18,48,NULL,'','',''),(257,NULL,'',68,53,NULL,'','',''),
1689 (258,29,'',52,16,64,'','',''),(259,NULL,'',80,92,40,'','',''),
1690 (301,68,'',1,48,48,'','',''),(302,2,'',1,1,32,'','',''),
1691 (303,44,'',60,96,16,'','',''),(304,32,'',52,64,32,'','',''),
1692 (305,88,'',37,72,NULL,'','',''),(306,5,'',35,60,20,'','',''),
1693 (307,35,'',4,48,NULL,'','',''),(308,4,'',92,44,80,'','',''),
1694 (351,48,'',60,4,40,'','',''),(352,7,'',9,61,13,'','',''),
1695 (353,0,'',5,93,53,'','',''),(354,7,'',1,20,NULL,'','',''),
1696 (355,84,'',5,48,96,'','',''),(356,NULL,'',39,92,36,'','',''),
1697 (357,88,'',9,76,44,'','',''),(358,66,'',34,67,80,'','',''),
1698 (359,8,'',8,52,NULL,'','',''),(360,3,'',53,83,NULL,'','',''),
1699 (361,23,'',44,9,48,'','',''),(362,4,'',0,54,48,'','',''),
1700 (363,75,'',66,76,52,'','','');
1701 ALTER TABLE t1 ADD COLUMN x VARCHAR(255) DEFAULT ' foobar ';
1702 UPDATE t1 SET f1 = 0;
1703 ALTER TABLE t1 DROP COLUMN x;
1704 DROP TABLE t1;
1705 CREATE TABLE t1 (f1 VARCHAR(1), f2 VARCHAR(2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1706 ALTER TABLE t1 MODIFY f2 VARCHAR (8) FIRST;
1707 DROP TABLE t1;
1708 CREATE TABLE t1 (a INT UNIQUE, b INT UNIQUE, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1709 ALTER TABLE t1 DROP PRIMARY KEY;
1710 ALTER TABLE t1 CHANGE COLUMN a a INT;
1711 DELETE FROM t1 WHERE a = NULL OR a IS NULL;
1712 DROP TABLE t1;
1713 CREATE TABLE t1 (a INT, b INT, c INT NOT NULL, d INT,
1714 e INT, f INT, g INT, h INT, j INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1715 ALTER TABLE t1 MODIFY COLUMN c INT, MODIFY COLUMN a INT AFTER b;
1716 DROP TABLE t1;
1717 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1718 INSERT INTO t1 VALUES (0,0);
1719 ALTER TABLE t1 MODIFY a INT AFTER b;
1720 ALTER TABLE t1 ADD COLUMN v INT AS (a) VIRTUAL;
1721 ALTER TABLE t1 MODIFY b INT NOT NULL AFTER a;
1722 DROP TABLE t1;
1723 CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1724 INSERT INTO t1 VALUES (1);
1725 ALTER TABLE t1 ADD COLUMN b INT;
1726 ALTER TABLE t1 MODIFY COLUMN a INT NULL;
1727 DROP TABLE t1;
1728 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1729 INSERT INTO t1 SET a=1;
1730 ALTER TABLE t1 DROP c;
1731 ALTER TABLE t1 DROP b, ADD v INT AS (a);
1732 DROP TABLE t1;
1733 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1734 INSERT INTO t1 SET a=1;
1735 ALTER TABLE t1 DROP c;
1736 ALTER TABLE t1 DROP b, ADD v INT AS (a);
1737 DROP TABLE t1;
1738 CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, b BLOB NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1739 INSERT INTO t1 VALUES (1,10,REPEAT('foobar',2000));
1740 ALTER TABLE t1 DROP COLUMN b;
1741 INSERT INTO t1 VALUES (2,20);
1742 ALTER TABLE t1 ADD COLUMN vpk INT AS (pk);
1743 ALTER TABLE t1 DROP COLUMN i;
1744 DROP TABLE t1;
1745 CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1746 INSERT INTO t1 VALUES (1,1);
1747 ALTER TABLE t1 ADD f DATE AFTER a;
1748 ALTER TABLE t1 DROP b, DROP f;
1749 DROP TABLE t1;
1750 CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1751 INSERT INTO t1 VALUES (1,1);
1752 ALTER TABLE t1 ADD COLUMN f INT AFTER a;
1753 ALTER TABLE t1 DROP b, DROP f;
1754 DROP TABLE t1;
1755 CREATE TABLE t1(t TEXT NOT NULL, FULLTEXT(t)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1756 ALTER TABLE t1 MODIFY COLUMN t TEXT;
1757 DROP TABLE t1;
1758 CREATE TABLE t1 (f TINYINT, g SMALLINT UNSIGNED) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1759 INSERT INTO t1 VALUES(127,6502),(-128,33101);
1760 ALTER TABLE t1 MODIFY f SMALLINT DEFAULT 12345,
1761 MODIFY g BIGINT UNSIGNED DEFAULT 1234567;
1762 affected rows: 2
1763 info: Records: 2 Duplicates: 0 Warnings: 0
1764 SELECT * FROM t1;
1765 f g
1766 127 6502
1767 -128 33101
1768 DROP TABLE t1;
1769 CREATE TABLE t1 (f BIT(8)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1770 INSERT INTO t1 VALUES (b'10000000'),(b'00000001');
1771 ALTER TABLE t1 MODIFY f BIT(16);
1772 affected rows: 2
1773 info: Records: 2 Duplicates: 0 Warnings: 0
1774 INSERT INTO t1 VALUES (b'1000000010101111'),(b'10000000');
1775 SELECT HEX(f) FROM t1;
1776 HEX(f)
1777 80
1778 1
1779 80AF
1780 80
1781 ALTER TABLE t1 MODIFY f SMALLINT;
1782 ERROR 22003: Out of range value for column 'f' at row 3
1783 ALTER TABLE t1 MODIFY f SMALLINT UNSIGNED;
1784 affected rows: 4
1785 info: Records: 4 Duplicates: 0 Warnings: 0
1786 SELECT * FROM t1;
1787 f
1788 128
1789 1
1790 32943
1791 128
1792 ALTER TABLE t1 MODIFY f BIT;
1793 ERROR 22001: Data too long for column 'f' at row 1
1794 ALTER TABLE t1 MODIFY f BIT(15);
1795 ERROR 22001: Data too long for column 'f' at row 3
1796 DELETE FROM t1 LIMIT 3;
1797 ALTER TABLE t1 MODIFY f BIT(15);
1798 affected rows: 1
1799 info: Records: 1 Duplicates: 0 Warnings: 0
1800 ALTER TABLE t1 MODIFY f BIT(8);
1801 affected rows: 1
1802 info: Records: 1 Duplicates: 0 Warnings: 0
1803 SELECT HEX(f) FROM t1;
1804 HEX(f)
1805 80
1806 DROP TABLE t1;
1807 CREATE TABLE t1 (b BIT NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET utf16;
1808 INSERT INTO t1 SET b=b'1';
1809 ALTER TABLE t1 CHANGE b c BIT NOT NULL;
1810 affected rows: 0
1811 info: Records: 0 Duplicates: 0 Warnings: 0
1812 SELECT HEX(c) FROM t1;
1813 HEX(c)
1814 1
1815 DROP TABLE t1;
1816 CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') ENGINE=InnoDB ROW_FORMAT=COMPACT;
1817 INSERT INTO t1() VALUES();
1818 ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42;
1819 affected rows: 0
1820 info: Records: 0 Duplicates: 0 Warnings: 0
1821 ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802,
1822 MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory',
1823 ADD d DATETIME;
1824 affected rows: 1
1825 info: Records: 1 Duplicates: 0 Warnings: 0
1826 INSERT INTO t1() VALUES();
1827 INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now());
1828 SELECT * FROM t1;
1829 c f d
1830 scary -42 NULL
1831 gory 64802 NULL
1832 fury -8388608 1970-01-01 03:00:42
1833 DROP TABLE t1;
1834 CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1835 SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1836 WHERE name = 'test/t1';
1837 INSERT INTO t1 VALUES (-42, -123456);
1838 ALTER TABLE t1 CHANGE t s SMALLINT;
1839 affected rows: 1
1840 info: Records: 1 Duplicates: 0 Warnings: 0
1841 SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1842 WHERE name = 'test/t1';
1843 affected rows: 1
1844 ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT;
1845 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
1846 ALTER TABLE t1 CHANGE m i INT;
1847 affected rows: 1
1848 info: Records: 1 Duplicates: 0 Warnings: 0
1849 SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
1850 WHERE name = 'test/t1';
1851 affected rows: 1
1852 SELECT @table_id1 = @table_id2, @table_id2 = @table_id3;
1853 @table_id1 = @table_id2 @table_id2 = @table_id3
1854 0 0
1855 INSERT IGNORE INTO t1 VALUES (0, -123456);
1856 Warnings:
1857 Warning 1062 Duplicate entry '-123456' for key 'm'
1858 REPLACE INTO t1 VALUES(-42, 123456);
1859 INSERT IGNORE INTO t1 VALUES(32768, 2147483648);
1860 Warnings:
1861 Warning 1264 Out of range value for column 's' at row 1
1862 Warning 1264 Out of range value for column 'i' at row 1
1863 SELECT * FROM t1;
1864 s i
1865 -42 123456
1866 32767 2147483647
1867 DROP TABLE t1;
1868 CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1869 INSERT INTO t1 (c) VALUES(1),(2),(3);
1870 ALTER TABLE t1 MODIFY c BIGINT;
1871 affected rows: 3
1872 info: Records: 3 Duplicates: 0 Warnings: 0
1873 UPDATE t1 SET b=1 WHERE c=2;
1874 UPDATE t1 SET c=4 WHERE a=3;
1875 UPDATE t1 SET b=2 WHERE c>3;
1876 UPDATE t1 SET c=c+1;
1877 ERROR 23000: Duplicate entry '2' for key 'c'
1878 SELECT * FROM t1;
1879 a b c
1880 1 NULL 1
1881 2 1 2
1882 3 2 4
1883 DROP TABLE t1;
1884 CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1885 INSERT INTO t1 VALUES(1,'a');
1886 ALTER TABLE t1 MODIFY b VARCHAR(256), ADD COLUMN c INT;
1887 INSERT INTO t1 VALUES(2,'bah',3);
1888 SELECT * FROM t1;
1889 a b c
1890 1 a NULL
1891 2 bah 3
1892 DROP TABLE t1;
1893 CREATE TABLE t1(a CHAR(5) CHARACTER SET utf8 PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1894 INSERT INTO t1 VALUES('barf');
1895 ALTER TABLE t1 ADD b INT FIRST, ALGORITHM=INSTANT;
1896 ALTER TABLE t1 ADD vb INT AS (b);
1897 SELECT * FROM t1;
1898 b a vb
1899 NULL barf NULL
1900 DROP TABLE t1;
1901 CREATE TABLE t1 (a INT, b TEXT, PRIMARY KEY(b(9))) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1902 ALTER TABLE t1 ADD COLUMN c TEXT FIRST;
1903 ALTER TABLE t1 ADD COLUMN d TEXT GENERATED ALWAYS AS (SUBSTR(b,1,499)) FIRST;
1904 DROP TABLE t1;
1905 CREATE TABLE t1(a CHAR(5), b INT, c CHAR(1), d CHAR(1), PRIMARY KEY(a,b))
1906 DEFAULT CHARACTER SET utf8 ENGINE=InnoDB ROW_FORMAT=COMPACT;
1907 INSERT INTO t1 SET a='fubar',b=42;
1908 ALTER TABLE t1 DROP c, DROP d, ALGORITHM=INSTANT;
1909 ALTER TABLE t1 ADD vb INT AS (b);
1910 SELECT * FROM t1;
1911 a b vb
1912 fubar 42 42
1913 DROP TABLE t1;
1914 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1915 ALTER TABLE t1 MODIFY b INT FIRST;
1916 DROP TABLE t1;
1917 CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1918 INSERT INTO t1 SET pk=1;
1919 ALTER TABLE t1 ADD COLUMN b INT;
1920 BEGIN;
1921 UPDATE t1 SET pk=2;
1922 UPDATE t1 SET pk=1;
1923 connection analyze;
1924 SELECT * FROM t1;
1925 pk b
1926 1 NULL
1927 connection default;
1928 DROP TABLE t1;
1929 CREATE TABLE t1
1930 (id INT PRIMARY KEY, c2 INT UNIQUE,
1931 c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'),
1932 SPATIAL INDEX(c3)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
1933 INSERT INTO t1 (id, c2) values(1,1);
1934 SELECT id,c2,ST_AsText(c3) c3 FROM t1;
1935 id c2 c3
1936 1 1 POINT(3 4)
1937 ALTER TABLE t1 ADD COLUMN (
1938 d1 INT, d2 INT UNSIGNED DEFAULT 10, d3 VARCHAR(20) NOT NULL DEFAULT 'abcde',
1939 d4 TIMESTAMP NOT NULL DEFAULT current_timestamp());
1940 affected rows: 0
1941 info: Records: 0 Duplicates: 0 Warnings: 0
1942 ALTER TABLE t1 ADD INDEX(d3);
1943 affected rows: 0
1944 info: Records: 0 Duplicates: 0 Warnings: 0
1945 BEGIN;
1946 UPDATE t1 SET d3='';
1947 ROLLBACK;
1948 SELECT id,c2,ST_AsText(c3) c3, d1, d2, d3, d4 FROM t1;
1949 id c2 c3 d1 d2 d3 d4
1950 1 1 POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1951 INSERT INTO t1 (id) VALUES(2),(3),(4),(5),(6);
1952 ALTER TABLE t1 CHANGE d1 d1 INT DEFAULT 5, CHANGE d2 d2 INT DEFAULT 15,
1953 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'fghij',
1954 CHANGE d4 dfour TIMESTAMP NOT NULL DEFAULT now();
1955 affected rows: 6
1956 info: Records: 6 Duplicates: 0 Warnings: 0
1957 UPDATE t1 SET d3='foo' WHERE id = 2;
1958 UPDATE t1 SET d3=DEFAULT WHERE id = 4;
1959 INSERT INTO t1 SET id = 7;
1960 SELECT id,c2,ST_AsText(c3) c3, d1, d2, d3, dfour FROM t1;
1961 id c2 c3 d1 d2 d3 dfour
1962 1 1 POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1963 2 NULL POINT(3 4) NULL 10 foo 1970-01-01 03:00:42
1964 3 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1965 4 NULL POINT(3 4) NULL 10 fghij 1970-01-01 03:00:42
1966 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1967 6 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42
1968 7 NULL POINT(3 4) 5 15 fghij 1970-01-01 03:00:42
1969 CHECK TABLE t1;
1970 Table Op Msg_type Msg_text
1971 test.t1 check status OK
1972 ALTER TABLE t1 ADD COLUMN e1 INT AS (id * 3);
1973 affected rows: 0
1974 info: Records: 0 Duplicates: 0 Warnings: 0
1975 ALTER TABLE t1 ADD COLUMN e2 VARCHAR(30) AS (d3);
1976 affected rows: 0
1977 info: Records: 0 Duplicates: 0 Warnings: 0
1978 ALTER TABLE t1 ADD COLUMN e3 INT AS (id * 2);
1979 affected rows: 0
1980 info: Records: 0 Duplicates: 0 Warnings: 0
1981 ALTER TABLE t1 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'foobar',
1982 ADD COLUMN (d5 CHAR(20) DEFAULT 'hijkl', d6 INT DEFAULT -12345, d7 INT),
1983 DROP INDEX d3;
1984 affected rows: 0
1985 info: Records: 0 Duplicates: 0 Warnings: 0
1986 INSERT INTO t1 SET id = 8;
1987 UPDATE t1 SET d3 = 'yyyyy' WHERE id = 1;
1988 UPDATE t1 SET d3 = 'xxxxx' WHERE id = 2;
1989 BEGIN;
1990 UPDATE t1 SET d3 = 'xxxxx' WHERE id = 3;
1991 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 3;
1992 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
1993 3 NULL POINT(3 4) NULL 10 xxxxx 1970-01-01 03:00:42 9 xxxxx 6 hijkl -12345 NULL
1994 ROLLBACK;
1995 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 3;
1996 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
1997 3 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 9 abcde 6 hijkl -12345 NULL
1998 BEGIN;
1999 UPDATE t1 SET d7 = NULL WHERE ID = 5;
2000 ROLLBACK;
2001 BEGIN;
2002 UPDATE t1 SET d7 = NULL, d6 = 10 WHERE id = 5;
2003 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 5;
2004 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
2005 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl 10 NULL
2006 ROLLBACK;
2007 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 5;
2008 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7
2009 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl -12345 NULL
2010 ALTER TABLE t1 ADD COLUMN (f1 VARCHAR(20) AS (concat('x', e2)) STORED);
2011 affected rows: 8
2012 info: Records: 8 Duplicates: 0 Warnings: 0
2013 ALTER TABLE t1 ADD COLUMN (d8 VARCHAR(20) DEFAULT 'omnopq');
2014 affected rows: 0
2015 info: Records: 0 Duplicates: 0 Warnings: 0
2016 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7, f1, d8 FROM t1;
2017 id c2 c3 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7 f1 d8
2018 1 1 POINT(3 4) NULL 10 yyyyy 1970-01-01 03:00:42 3 yyyyy 2 hijkl -12345 NULL xyyyyy omnopq
2019 2 NULL POINT(3 4) NULL 10 xxxxx 1970-01-01 03:00:42 6 xxxxx 4 hijkl -12345 NULL xxxxxx omnopq
2020 3 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 9 abcde 6 hijkl -12345 NULL xabcde omnopq
2021 4 NULL POINT(3 4) NULL 10 fghij 1970-01-01 03:00:42 12 fghij 8 hijkl -12345 NULL xfghij omnopq
2022 5 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl -12345 NULL xabcde omnopq
2023 6 NULL POINT(3 4) NULL 10 abcde 1970-01-01 03:00:42 18 abcde 12 hijkl -12345 NULL xabcde omnopq
2024 7 NULL POINT(3 4) 5 15 fghij 1970-01-01 03:00:42 21 fghij 14 hijkl -12345 NULL xfghij omnopq
2025 8 NULL POINT(3 4) 5 15 foobar 1970-01-01 03:00:42 24 foobar 16 hijkl -12345 NULL xfoobar omnopq
2026 SHOW CREATE TABLE t1;
2027 Table Create Table
2028 t1 CREATE TABLE `t1` (
2029 `id` int(11) NOT NULL,
2030 `c2` int(11) DEFAULT NULL,
2031 `c3` point NOT NULL DEFAULT st_geometryfromtext('POINT(3 4)'),
2032 `d1` int(11) DEFAULT 5,
2033 `d2` int(11) DEFAULT 15,
2034 `d3` varchar(20) NOT NULL DEFAULT 'foobar',
2035 `dfour` timestamp NOT NULL DEFAULT current_timestamp(),
2036 `e1` int(11) GENERATED ALWAYS AS (`id` * 3) VIRTUAL,
2037 `e2` varchar(30) GENERATED ALWAYS AS (`d3`) VIRTUAL,
2038 `e3` int(11) GENERATED ALWAYS AS (`id` * 2) VIRTUAL,
2039 `d5` char(20) DEFAULT 'hijkl',
2040 `d6` int(11) DEFAULT -12345,
2041 `d7` int(11) DEFAULT NULL,
2042 `f1` varchar(20) GENERATED ALWAYS AS (concat('x',`e2`)) STORED,
2043 `d8` varchar(20) DEFAULT 'omnopq',
2044 PRIMARY KEY (`id`),
2045 UNIQUE KEY `c2` (`c2`),
2046 SPATIAL KEY `c3` (`c3`)
2047 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
2048 ALTER TABLE t1
2049 CHANGE c2 c2 INT DEFAULT 42,
2050 CHANGE d1 d1 INT DEFAULT 1,
2051 CHANGE d2 d2 INT DEFAULT 20,
2052 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'boofar';
2053 affected rows: 0
2054 info: Records: 0 Duplicates: 0 Warnings: 0
2055 INSERT INTO t1 SET id=9;
2056 ALTER TABLE t1 DROP c3;
2057 affected rows: 0
2058 info: Records: 0 Duplicates: 0 Warnings: 0
2059 SHOW CREATE TABLE t1;
2060 Table Create Table
2061 t1 CREATE TABLE `t1` (
2062 `id` int(11) NOT NULL,
2063 `c2` int(11) DEFAULT 42,
2064 `d1` int(11) DEFAULT 1,
2065 `d2` int(11) DEFAULT 20,
2066 `d3` varchar(20) NOT NULL DEFAULT 'boofar',
2067 `dfour` timestamp NOT NULL DEFAULT current_timestamp(),
2068 `e1` int(11) GENERATED ALWAYS AS (`id` * 3) VIRTUAL,
2069 `e2` varchar(30) GENERATED ALWAYS AS (`d3`) VIRTUAL,
2070 `e3` int(11) GENERATED ALWAYS AS (`id` * 2) VIRTUAL,
2071 `d5` char(20) DEFAULT 'hijkl',
2072 `d6` int(11) DEFAULT -12345,
2073 `d7` int(11) DEFAULT NULL,
2074 `f1` varchar(20) GENERATED ALWAYS AS (concat('x',`e2`)) STORED,
2075 `d8` varchar(20) DEFAULT 'omnopq',
2076 PRIMARY KEY (`id`),
2077 UNIQUE KEY `c2` (`c2`)
2078 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
2079 SELECT * FROM t1;
2080 id c2 d1 d2 d3 dfour e1 e2 e3 d5 d6 d7 f1 d8
2081 1 1 NULL 10 yyyyy 1970-01-01 03:00:42 3 yyyyy 2 hijkl -12345 NULL xyyyyy omnopq
2082 2 NULL NULL 10 xxxxx 1970-01-01 03:00:42 6 xxxxx 4 hijkl -12345 NULL xxxxxx omnopq
2083 3 NULL NULL 10 abcde 1970-01-01 03:00:42 9 abcde 6 hijkl -12345 NULL xabcde omnopq
2084 4 NULL NULL 10 fghij 1970-01-01 03:00:42 12 fghij 8 hijkl -12345 NULL xfghij omnopq
2085 5 NULL NULL 10 abcde 1970-01-01 03:00:42 15 abcde 10 hijkl -12345 NULL xabcde omnopq
2086 6 NULL NULL 10 abcde 1970-01-01 03:00:42 18 abcde 12 hijkl -12345 NULL xabcde omnopq
2087 7 NULL 5 15 fghij 1970-01-01 03:00:42 21 fghij 14 hijkl -12345 NULL xfghij omnopq
2088 8 NULL 5 15 foobar 1970-01-01 03:00:42 24 foobar 16 hijkl -12345 NULL xfoobar omnopq
2089 9 42 1 20 boofar 1970-01-01 03:00:42 27 boofar 18 hijkl -12345 NULL xboofar omnopq
2090 CREATE TABLE t2
2091 (id INT primary key, c1 VARCHAR(4000),
2092 p GEOMETRY NOT NULL DEFAULT ST_GeomFromText('LINESTRING(0 0,0 1,1 1)'),
2093 SPATIAL INDEX(p))
2094 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2095 BEGIN;
2096 INSERT INTO t2 SET id=1, c1=REPEAT('a', 4000);
2097 INSERT INTO t2 SET id=2, c1=REPEAT('a', 4000), p=ST_GeomFromText('POINT(1 1)');
2098 COMMIT;
2099 ALTER TABLE t2 ADD COLUMN d1 VARCHAR(2000) DEFAULT REPEAT('asdf',500);
2100 affected rows: 0
2101 info: Records: 0 Duplicates: 0 Warnings: 0
2102 SELECT id, c1, ST_AsText(p) p, d1 FROM t2;
2103 id c1 p d1
2104 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa LINESTRING(0 0,0 1,1 1) asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
2105 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa POINT(1 1) asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
2106 BEGIN;
2107 UPDATE t2 SET c1 = repeat(id, 4000);
2108 connection analyze;
2109 ANALYZE TABLE t2;
2110 Table Op Msg_type Msg_text
2111 test.t2 analyze status Engine-independent statistics collected
2112 test.t2 analyze status OK
2113 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2114 WHERE name = 'test/t2';
2115 clust_index_size
2116 1
2117 connection default;
2118 ROLLBACK;
2119 connection analyze;
2120 ANALYZE TABLE t2;
2121 Table Op Msg_type Msg_text
2122 test.t2 analyze status Engine-independent statistics collected
2123 test.t2 analyze status OK
2124 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2125 WHERE name = 'test/t2';
2126 clust_index_size
2127 1
2128 connection default;
2129 BEGIN;
2130 UPDATE t2 SET d1 = repeat(id, 200);
2131 connection analyze;
2132 ANALYZE TABLE t2;
2133 Table Op Msg_type Msg_text
2134 test.t2 analyze status Engine-independent statistics collected
2135 test.t2 analyze status OK
2136 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2137 WHERE name = 'test/t2';
2138 clust_index_size
2139 1
2140 connection default;
2141 ROLLBACK;
2142 connection analyze;
2143 ANALYZE TABLE t2;
2144 Table Op Msg_type Msg_text
2145 test.t2 analyze status Engine-independent statistics collected
2146 test.t2 analyze status OK
2147 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2148 WHERE name = 'test/t2';
2149 clust_index_size
2150 1
2151 connection default;
2152 ALTER TABLE t2 DROP p;
2153 affected rows: 0
2154 info: Records: 0 Duplicates: 0 Warnings: 0
2155 SELECT * FROM t2;
2156 id c1 d1
2157 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
2158 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
2159 CREATE TABLE t3
2160 (id INT PRIMARY KEY, c2 INT UNSIGNED NOT NULL UNIQUE,
2161 c3 POLYGON NOT NULL DEFAULT ST_PolyFromText('POLYGON((1 1,2 2,3 3,1 1))'),
2162 SPATIAL INDEX(c3))
2163 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2164 INSERT INTO t3(id,c2) VALUES(1,1),(2,2),(3,3);
2165 SELECT id, c2, ST_AsText(c3) c3 FROM t3;
2166 id c2 c3
2167 1 1 POLYGON((1 1,2 2,3 3,1 1))
2168 2 2 POLYGON((1 1,2 2,3 3,1 1))
2169 3 3 POLYGON((1 1,2 2,3 3,1 1))
2170 ALTER TABLE t3 ADD COLUMN
2171 (c4 DATETIME DEFAULT current_timestamp(),
2172 c5 TIMESTAMP NOT NULL DEFAULT current_timestamp(),
2173 c6 POINT);
2174 affected rows: 0
2175 info: Records: 0 Duplicates: 0 Warnings: 0
2176 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6 FROM t3;
2177 id c2 c3 c4 c5 c6
2178 1 1 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL
2179 2 2 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL
2180 3 3 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL
2181 affected rows: 3
2182 ALTER TABLE t3 ADD COLUMN c7 TIME NOT NULL DEFAULT current_timestamp();
2183 affected rows: 0
2184 info: Records: 0 Duplicates: 0 Warnings: 1
2185 Warnings:
2186 Note 1265 Data truncated for column 'c7' at row 0
2187 ALTER TABLE t3 ADD COLUMN c8 DATE NOT NULL DEFAULT current_timestamp();
2188 affected rows: 0
2189 info: Records: 0 Duplicates: 0 Warnings: 1
2190 Warnings:
2191 Note 1265 Data truncated for column 'c8' at row 0
2192 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6, c7, c8 FROM t3;
2193 id c2 c3 c4 c5 c6 c7 c8
2194 1 1 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01
2195 2 2 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01
2196 3 3 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01
2197 ALTER TABLE t3 ADD COLUMN t TEXT CHARSET utf8
2198 DEFAULT 'The quick brown fox jumps over the lazy dog';
2199 affected rows: 0
2200 info: Records: 0 Duplicates: 0 Warnings: 0
2201 ALTER TABLE t3 ADD COLUMN b BLOB NOT NULL;
2202 affected rows: 0
2203 info: Records: 0 Duplicates: 0 Warnings: 0
2204 INSERT INTO t3 SET id=4;
2205 ERROR HY000: Field 'c2' doesn't have a default value
2206 INSERT INTO t3 SET id=4, c2=0, b=0xf09f98b1;
2207 affected rows: 1
2208 Warnings:
2209 Note 1265 Data truncated for column 'c7' at row 1
2210 Note 1265 Data truncated for column 'c8' at row 1
2211 SET innodb_strict_mode = OFF;
2212 affected rows: 0
2213 ALTER TABLE t3 CHANGE t phrase TEXT DEFAULT 0xc3a4c3a448,
2214 CHANGE b b BLOB NOT NULL DEFAULT 'binary line of business';
2215 affected rows: 4
2216 info: Records: 4 Duplicates: 0 Warnings: 0
2217 INSERT INTO t3 SET id=5, c2=9;
2218 Warnings:
2219 Note 1265 Data truncated for column 'c7' at row 1
2220 Note 1265 Data truncated for column 'c8' at row 1
2221 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6, c7, c8, phrase, b FROM t3;
2222 id c2 c3 c4 c5 c6 c7 c8 phrase b
2223 1 1 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog
2224 2 2 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog
2225 3 3 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog
2226 4 0 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 The quick brown fox jumps over the lazy dog 😱
2227 5 9 POLYGON((1 1,2 2,3 3,1 1)) 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 03:00:42 1970-01-01 ääH binary line of business
2228 ALTER TABLE t3 DROP c3, DROP c7;
2229 affected rows: 0
2230 info: Records: 0 Duplicates: 0 Warnings: 0
2231 SELECT * FROM t3;
2232 id c2 c4 c5 c6 c8 phrase b
2233 1 1 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog
2234 2 2 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog
2235 3 3 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog
2236 4 0 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 The quick brown fox jumps over the lazy dog 😱
2237 5 9 1970-01-01 03:00:42 1970-01-01 03:00:42 NULL 1970-01-01 ääH binary line of business
2238 CREATE TABLE t4
2239 (id INT, foo INT DEFAULT 0, c1 VARCHAR(4000),
2240 p GEOMETRY NOT NULL DEFAULT ST_GeomFromText('LINESTRING(0 0,0 1,1 1)'),
2241 PRIMARY KEY(id,foo))
2242 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2243 INSERT INTO t4 (id,c1) VALUES (1, REPEAT('a', 4000)), (2, REPEAT('a', 4000));
2244 ALTER TABLE t4 ADD COLUMN d1 INT;
2245 BEGIN;
2246 UPDATE t4 SET c1 = repeat('1', 4000), foo=1 WHERE id=1;
2247 INSERT INTO t4 (id,c1) VALUES (1, REPEAT('a', 4000));
2248 UPDATE t4 SET c1 = repeat('2', 4000), foo=1 WHERE id=2;
2249 ROLLBACK;
2250 BEGIN;
2251 UPDATE t4 SET d1 = 1,foo=2 WHERE id=1;
2252 INSERT INTO t4 (id,foo,c1) VALUES (1, 1, REPEAT('1', 4000));
2253 COMMIT;
2254 CREATE TABLE big
2255 (id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000),
2256 p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'), SPATIAL INDEX(p))
2257 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2258 BEGIN;
2259 INSERT INTO big
2260 SET id=1, c1=REPEAT('a', 200), c2=REPEAT('b', 200), c3=REPEAT('c', 159);
2261 SET @i:=1;
2262 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
2263 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
2264 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
2265 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
2266 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
2267 COMMIT;
2268 connection analyze;
2269 ANALYZE TABLE big;
2270 Table Op Msg_type Msg_text
2271 test.big analyze status Engine-independent statistics collected
2272 test.big analyze status OK
2273 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2274 WHERE name = 'test/big';
2275 clust_index_size
2276 3
2277 connection default;
2278 ALTER TABLE big ADD COLUMN
2279 (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde',
2280 d3 TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp);
2281 affected rows: 0
2282 info: Records: 0 Duplicates: 0 Warnings: 0
2283 CHECKSUM TABLE big;
2284 Table Checksum
2285 test.big 1705165209
2286 BEGIN;
2287 INSERT INTO big(id, c1, c2, c3) SELECT @i:=@i+1, c1, c2, c3 FROM big;
2288 INSERT INTO big(id, c1, c2, c3) SELECT @i:=@i+1, c1, c2, c3 FROM big;
2289 CHECKSUM TABLE big;
2290 Table Checksum
2291 test.big 385477733
2292 connection analyze;
2293 ANALYZE TABLE big;
2294 Table Op Msg_type Msg_text
2295 test.big analyze status Engine-independent statistics collected
2296 test.big analyze status OK
2297 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2298 WHERE name = 'test/big';
2299 clust_index_size
2300 7
2301 connection default;
2302 ROLLBACK;
2303 CHECKSUM TABLE big;
2304 Table Checksum
2305 test.big 1705165209
2306 connection analyze;
2307 ANALYZE TABLE big;
2308 Table Op Msg_type Msg_text
2309 test.big analyze status Engine-independent statistics collected
2310 test.big analyze status OK
2311 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2312 WHERE name = 'test/big';
2313 clust_index_size
2314 3
2315 connection default;
2316 InnoDB 0 transactions not purged
2317 DROP TABLE t1,t2,t3,t4,big;
2318 CREATE TABLE t1 (a VARCHAR(1) PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2319 INSERT INTO t1 SET a='a';
2320 ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 0;
2321 UPDATE t1 SET b = 1;
2322 INSERT INTO t1 SET a='a';
2323 ERROR 23000: Duplicate entry 'a' for key 'PRIMARY'
2324 SELECT * FROM t1;
2325 a b
2326 a 1
2327 DROP TABLE t1;
2328 CREATE TABLE t1 (a INT, b VARCHAR(8), PRIMARY KEY(b,a)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2329 INSERT INTO t1 VALUES (1,'foo');
2330 ALTER TABLE t1 ADD COLUMN c INT;
2331 UPDATE t1 SET c = 1;
2332 UPDATE t1 SET c = 2;
2333 DROP TABLE t1;
2334 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2335 CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY(b) REFERENCES t1(a))
2336 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2337 INSERT INTO t1 SET a=1;
2338 INSERT INTO t2 SET b=1;
2339 ALTER TABLE t2 ADD COLUMN a INT, DROP FOREIGN KEY t2_ibfk_1;
2340 ALTER TABLE t2 ADD INDEX(a);
2341 ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a),
2342 ALGORITHM=INSTANT;
2343 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
2344 SET foreign_key_checks=0;
2345 ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a),
2346 ALGORITHM=INSTANT;
2347 ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a),
2348 ALGORITHM=INSTANT;
2349 SET foreign_key_checks=1;
2350 ALTER TABLE t2 COMMENT 'domestic keys only', DROP FOREIGN KEY fk;
2351 ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1;
2352 ALTER TABLE t1 ADD COLUMN big BLOB NOT NULL
2353 DEFAULT REPEAT('a', @@GLOBAL.innodb_page_size * .75);
2354 CHECK TABLE t2, t1;
2355 Table Op Msg_type Msg_text
2356 test.t2 check status OK
2357 test.t1 check status OK
2358 DROP TABLE t2, t1;
2359 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2360 INSERT INTO t1 SET a = 1;
2361 ALTER TABLE t1 ADD COLUMN b TEXT;
2362 BEGIN;
2363 UPDATE t1 SET b = REPEAT('1', 32768);
2364 UPDATE t1 SET a = 2;
2365 INSERT INTO t1 SET a = 1;
2366 SELECT a,LENGTH(b) FROM t1;
2367 a LENGTH(b)
2368 1 NULL
2369 2 32768
2370 DELETE FROM t1;
2371 COMMIT;
2372 InnoDB 0 transactions not purged
2373 DROP TABLE t1;
2374 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2375 INSERT INTO t1 VALUES (7);
2376 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 0;
2377 ALTER TABLE t1 ADD INDEX (c);
2378 BEGIN;
2379 DELETE FROM t1;
2380 INSERT INTO t1 VALUES (4,0),(7,77);
2381 COMMIT;
2382 BEGIN;
2383 DELETE FROM t1 WHERE a=7;
2384 UPDATE t1 SET a=7;
2385 COMMIT;
2386 SELECT * FROM t1 FORCE INDEX(PRIMARY);
2387 a c
2388 7 0
2389 SELECT * FROM t1 FORCE INDEX(c);
2390 a c
2391 7 0
2392 DELETE FROM t1;
2393 CHECK TABLE t1;
2394 Table Op Msg_type Msg_text
2395 test.t1 check status OK
2396 DROP TABLE t1;
2397 SET innodb_strict_mode = ON;
2398 CREATE TABLE t1 (a INT, b VARCHAR(500), c TEXT, UNIQUE(a,b)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2399 ALTER TABLE t1 ADD d TEXT;
2400 ALTER TABLE t1 ADD PRIMARY KEY (b,a);
2401 ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
2402 DROP TABLE t1;
2403 SET innodb_strict_mode = OFF;
2404 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2405 INSERT INTO t1 SET a=42;
2406 SET GLOBAL innodb_instant_alter_column_allowed = never;
2407 ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
2408 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
2409 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
2410 ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
2411 SET GLOBAL innodb_instant_alter_column_allowed = never;
2412 ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
2413 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
2414 ALTER TABLE t1 MODIFY a INT DEFAULT 0;
2415 affected rows: 0
2416 info: Records: 0 Duplicates: 0 Warnings: 0
2417 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
2418 ALTER TABLE t1 DROP b, ALGORITHM=INSTANT;
2419 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
2420 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
2421 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
2422 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
2423 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
2424 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
2425 ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT;
2426 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
2427 SET GLOBAL innodb_instant_alter_column_allowed = add_drop_reorder;
2428 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
2429 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
2430 ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
2431 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
2432 ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT;
2433 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
2434 ALTER TABLE t1 MODIFY a INT DEFAULT 0, ALGORITHM=INSTANT;
2435 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=add_last. Try ALGORITHM=INPLACE
2436 ALTER TABLE t1 MODIFY a INT DEFAULT 0;
2437 affected rows: 0
2438 info: Records: 0 Duplicates: 0 Warnings: 0
2439 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
2440 DROP TABLE t1;
2441 SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT;
2442 CREATE TABLE t1 (a INT, b INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2443 INSERT INTO t1 (a) VALUES (NULL), (NULL);
2444 ALTER TABLE t1 DROP a, ADD COLUMN a INT;
2445 DELETE FROM t1;
2446 BEGIN;
2447 INSERT INTO t1 SET a=NULL;
2448 ROLLBACK;
2449 DELETE FROM t1;
2450 DROP TABLE t1;
2451 CREATE TABLE t1 (a INT PRIMARY KEY, t VARCHAR(33101) NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2452 INSERT INTO t1 VALUES(347,'');
2453 ALTER TABLE t1 DROP COLUMN t, ALGORITHM=INSTANT;
2454 SELECT * FROM t1;
2455 a
2456 347
2457 DROP TABLE t1;
2458 CREATE TABLE t1 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2459 INSERT INTO t1() VALUES();
2460 ALTER TABLE t1 ADD COLUMN b INT FIRST, ADD COLUMN c INT AFTER b;
2461 SELECT * FROM t1;
2462 b c a
2463 NULL NULL NULL
2464 DROP TABLE t1;
2465 CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2466 SET @t=REPEAT('x',@@innodb_page_size / 2);
2467 INSERT INTO t1 VALUES (@t),(@t),(@t),(@t),(@t),(@t),(NULL),(@t),(@t),(@t),(@t);
2468 ALTER TABLE t1 ADD COLUMN a INT FIRST;
2469 UPDATE t1 SET a = 0;
2470 DROP TABLE t1;
2471 CREATE TABLE t1 (t TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2472 INSERT INTO t1 SET t = @x;
2473 ALTER TABLE t1 DROP COLUMN t, ADD COLUMN i INT NOT NULL DEFAULT 1;
2474 ALTER TABLE t1 ADD COLUMN t TEXT;
2475 SELECT * FROM t1;
2476 i t
2477 1 NULL
2478 DROP TABLE t1;
2479 CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2480 INSERT INTO t1 SET a=NULL;
2481 ALTER TABLE t1 DROP COLUMN b;
2482 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 42;
2483 INSERT INTO t1 SET a=NULL;
2484 UPDATE t1 SET a=a+2;
2485 SELECT * FROM t1;
2486 a c
2487 3 42
2488 4 42
2489 DROP TABLE t1;
2490 CREATE TABLE t1 (i INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2491 INSERT INTO t1 SET i=1;
2492 ALTER TABLE t1 ADD COLUMN b BIT FIRST;
2493 ALTER TABLE t1 ADD COLUMN v INT AS (i) VIRTUAL;
2494 SELECT * FROM t1;
2495 b i v
2496 NULL 1 1
2497 DROP TABLE t1;
2498 CREATE TABLE t1 (ts TIMESTAMP) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2499 ALTER TABLE t1 ADD COLUMN f VARCHAR(8), ADD COLUMN dt DATETIME;
2500 ALTER TABLE t1 ADD COLUMN b BIT, DROP COLUMN f, ADD COLUMN t TIME FIRST;
2501 ALTER TABLE t1 ADD COLUMN ts2 TIMESTAMP;
2502 DROP TABLE t1;
2503 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2504 INSERT INTO t1 VALUES (4,4,4);
2505 ALTER TABLE t1 DROP f1, DROP f2, ADD f4 INT, ADD f5 INT;
2506 DELETE FROM t1;
2507 ALTER TABLE t1 DROP COLUMN f4;
2508 DROP TABLE t1;
2509 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2510 ALTER TABLE t1 DROP f2, ADD COLUMN f4 INT;
2511 ALTER TABLE t1 DROP f4;
2512 ALTER TABLE t1 DROP f1;
2513 DROP TABLE t1;
2514 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2515 ALTER TABLE t1 DROP COLUMN id;
2516 INSERT INTO t1 () VALUES (),();
2517 SELECT * FROM t1;
2518 f
2519 NULL
2520 NULL
2521 ALTER TABLE t1 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD KEY(id);
2522 SELECT * FROM t1;
2523 id f
2524 1 NULL
2525 2 NULL
2526 DROP TABLE t1;
2527 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2528 INSERT INTO t1 SET f=NULL;
2529 ALTER TABLE t1 DROP COLUMN id;
2530 INSERT INTO t1 SET f=NULL;
2531 SELECT * FROM t1;
2532 f
2533 NULL
2534 NULL
2535 DROP TABLE t1;
2536 CREATE TABLE t1(f INT, k INT NOT NULL AUTO_INCREMENT, KEY(k)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2537 ALTER TABLE t1 DROP COLUMN f;
2538 INSERT INTO t1 VALUES (1);
2539 DROP TABLE t1;
2540 CREATE TABLE t1(pk INT PRIMARY KEY, f INT, k INT AUTO_INCREMENT, KEY(k))
2541 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2542 ALTER TABLE t1 DROP COLUMN f;
2543 INSERT INTO t1 (pk) VALUES (1);
2544 DROP TABLE t1;
2545 CREATE TABLE t1 (
2546 pk INT PRIMARY KEY,
2547 f1 INT,
2548 f2 CHAR(255),
2549 f3 BIGINT,
2550 f4 INT,
2551 f5 CHAR(255),
2552 f6 CHAR(255),
2553 f7 CHAR(255) NOT NULL,
2554 f8 INT,
2555 f9 CHAR(10)
2556 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2557 INSERT INTO t1 VALUES
2558 (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'),
2559 (2, 2, 'b', 2, 2, 'b', 'b', 'b', 2, 'b'),
2560 (3, 3, 'c', 3, 3, 'c', 'c', 'c', 3, 'c'),
2561 (4, 4, 'd', 4, 4, 'd', 'd', 'd', 4, 'd'),
2562 (5, 5, 'e', 5, 5, 'e', 'e', 'e', 5, 'e'),
2563 (6, 6, 'f', 6, 6, 'f', 'f', 'f', 6, 'f'),
2564 (7, 7, 'g', 7, 7, 'g', 'g', 'g', 7, 'g'),
2565 (8, 8, 'h', 8, 8, 'h', 'h', 'h', 8, 'h'),
2566 (9, 9, 'i', 9, 9, 'i', 'i', 'i', 9, 'i'),
2567 (10, 0, 'j', 0, 0, 'j', 'j', 'j', 0, 'j'),
2568 (11, 1, 'k', 1, 1, 'k', 'k', 'k', 1, 'k'),
2569 (12, 2, 'l', 2, 2, 'l', 'l', 'l', 2, 'l'),
2570 (13, 3, 'm', 3, 3, 'm', 'm', 'm', 3, 'm'),
2571 (14, 4, 'n', 4, 4, 'n', 'n', 'n', 4, 'n'),
2572 (15, 5, 'o', 5, 5, 'o', 'o', 'o', 5, 'o');
2573 DELETE FROM t1 WHERE pk=1;
2574 InnoDB 0 transactions not purged
2575 INSERT INTO t1 VALUES
2576 (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a');
2577 ALTER TABLE t1 DROP COLUMN f1;
2578 DROP TABLE t1;
2579 CREATE TABLE t1 (
2580 pk INT PRIMARY KEY,
2581 f1 INT, f2 CHAR(32) NOT NULL,
2582 f3 INT NOT NULL, f4 INT NOT NULL, f5 INT, f6 CHAR(32) NOT NULL,
2583 f7 CHAR(32), f8 CHAR(32)
2584 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2585 INSERT INTO t1 VALUES
2586 (1,9,'',2,88,88,'','',''),(2,48,'',8,68,92,'','',''),
2587 (3,41,'',56,84,37,'','',''),(4,NULL,'',6,6,NULL,'','',''),
2588 (5,52,'',37,44,20,'','',''),(6,44,'',53,4,NULL,'','',''),
2589 (7,24,'',54,8,54,'','',''),(8,80,'',3,52,20,'','',''),
2590 (9,71,'',34,32,NULL,'','',''),(10,14,'',6,64,88,'','',''),
2591 (11,48,'',8,25,42,'','',''),(12,16,'',8,7,NULL,'','',''),
2592 (13,NULL,'',22,0,95,'','',''),(14,4,'',72,48,NULL,'','',''),
2593 (15,4,'',5,64,2,'','',''),(16,NULL,'',9,40,30,'','',''),
2594 (17,92,'',48,2,NULL,'','',''),(18,36,'',48,51,7,'','',''),
2595 (19,NULL,'',80,96,NULL,'','',''),(20,96,'',9,80,NULL,'','',''),
2596 (21,50,'',16,40,NULL,'','',''),(22,NULL,'',7,84,8,'','',''),
2597 (23,28,'',93,80,NULL,'','',''),(24,31,'',40,38,NULL,'','',''),
2598 (25,85,'',8,5,88,'','',''),(26,66,'',8,32,4,'','',''),
2599 (51,52,'',6,92,15,'','',''),(52,77,'',24,24,28,'','',''),
2600 (53,8,'',75,31,NULL,'','',''),(54,48,'',5,8,1,'','',''),
2601 (55,90,'',56,12,5,'','',''),(56,92,'',4,9,88,'','',''),
2602 (57,83,'',23,40,72,'','',''),(58,7,'',4,40,32,'','',''),
2603 (59,28,'',2,3,32,'','',''),(60,16,'',80,4,NULL,'','',''),
2604 (61,44,'',88,24,NULL,'','',''),(62,4,'',5,25,3,'','',''),
2605 (63,NULL,'',7,24,76,'','',''),(64,0,'',13,40,73,'','',''),
2606 (101,NULL,'',1,49,75,'','',''),(102,34,'',10,17,20,'','',''),
2607 (103,8,'',2,2,NULL,'','',''),(104,12,'',44,48,52,'','',''),
2608 (105,8,'',4,19,38,'','',''),(106,20,'',6,80,9,'','',''),
2609 (107,72,'',72,16,56,'','',''),(108,76,'',98,24,21,'','',''),
2610 (109,67,'',16,91,NULL,'','',''),(110,72,'',72,3,48,'','',''),
2611 (151,8,'',3,86,NULL,'','',''),(152,NULL,'',52,72,0,'','',''),
2612 (153,NULL,'',46,30,92,'','',''),(154,80,'',1,40,48,'','',''),
2613 (155,24,'',68,68,8,'','',''),(156,85,'',85,72,60,'','',''),
2614 (157,7,'',7,12,6,'','',''),(158,NULL,'',48,48,80,'','',''),
2615 (159,12,'',0,36,0,'','',''),(160,2,'',6,52,NULL,'','',''),
2616 (201,0,'',1,3,NULL,'','',''),(202,NULL,'',3,53,14,'','',''),
2617 (203,84,'',6,20,NULL,'','',''),(204,38,'',25,13,88,'','',''),
2618 (205,1,'',2,69,5,'','',''),(206,7,'',60,22,NULL,'','',''),
2619 (207,NULL,'',5,4,NULL,'','',''),(251,7,'',0,4,40,'','',''),
2620 (252,4,'',16,8,NULL,'','',''),(253,14,'',60,12,99,'','',''),
2621 (254,84,'',68,16,5,'','',''),(255,3,'',70,36,61,'','',''),
2622 (256,7,'',18,48,NULL,'','',''),(257,NULL,'',68,53,NULL,'','',''),
2623 (258,29,'',52,16,64,'','',''),(259,NULL,'',80,92,40,'','',''),
2624 (301,68,'',1,48,48,'','',''),(302,2,'',1,1,32,'','',''),
2625 (303,44,'',60,96,16,'','',''),(304,32,'',52,64,32,'','',''),
2626 (305,88,'',37,72,NULL,'','',''),(306,5,'',35,60,20,'','',''),
2627 (307,35,'',4,48,NULL,'','',''),(308,4,'',92,44,80,'','',''),
2628 (351,48,'',60,4,40,'','',''),(352,7,'',9,61,13,'','',''),
2629 (353,0,'',5,93,53,'','',''),(354,7,'',1,20,NULL,'','',''),
2630 (355,84,'',5,48,96,'','',''),(356,NULL,'',39,92,36,'','',''),
2631 (357,88,'',9,76,44,'','',''),(358,66,'',34,67,80,'','',''),
2632 (359,8,'',8,52,NULL,'','',''),(360,3,'',53,83,NULL,'','',''),
2633 (361,23,'',44,9,48,'','',''),(362,4,'',0,54,48,'','',''),
2634 (363,75,'',66,76,52,'','','');
2635 ALTER TABLE t1 ADD COLUMN x VARCHAR(255) DEFAULT ' foobar ';
2636 UPDATE t1 SET f1 = 0;
2637 ALTER TABLE t1 DROP COLUMN x;
2638 DROP TABLE t1;
2639 CREATE TABLE t1 (f1 VARCHAR(1), f2 VARCHAR(2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2640 ALTER TABLE t1 MODIFY f2 VARCHAR (8) FIRST;
2641 DROP TABLE t1;
2642 CREATE TABLE t1 (a INT UNIQUE, b INT UNIQUE, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2643 ALTER TABLE t1 DROP PRIMARY KEY;
2644 ALTER TABLE t1 CHANGE COLUMN a a INT;
2645 DELETE FROM t1 WHERE a = NULL OR a IS NULL;
2646 DROP TABLE t1;
2647 CREATE TABLE t1 (a INT, b INT, c INT NOT NULL, d INT,
2648 e INT, f INT, g INT, h INT, j INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2649 ALTER TABLE t1 MODIFY COLUMN c INT, MODIFY COLUMN a INT AFTER b;
2650 DROP TABLE t1;
2651 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2652 INSERT INTO t1 VALUES (0,0);
2653 ALTER TABLE t1 MODIFY a INT AFTER b;
2654 ALTER TABLE t1 ADD COLUMN v INT AS (a) VIRTUAL;
2655 ALTER TABLE t1 MODIFY b INT NOT NULL AFTER a;
2656 DROP TABLE t1;
2657 CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2658 INSERT INTO t1 VALUES (1);
2659 ALTER TABLE t1 ADD COLUMN b INT;
2660 ALTER TABLE t1 MODIFY COLUMN a INT NULL;
2661 DROP TABLE t1;
2662 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2663 INSERT INTO t1 SET a=1;
2664 ALTER TABLE t1 DROP c;
2665 ALTER TABLE t1 DROP b, ADD v INT AS (a);
2666 DROP TABLE t1;
2667 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2668 INSERT INTO t1 SET a=1;
2669 ALTER TABLE t1 DROP c;
2670 ALTER TABLE t1 DROP b, ADD v INT AS (a);
2671 DROP TABLE t1;
2672 CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, b BLOB NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2673 INSERT INTO t1 VALUES (1,10,REPEAT('foobar',2000));
2674 ALTER TABLE t1 DROP COLUMN b;
2675 INSERT INTO t1 VALUES (2,20);
2676 ALTER TABLE t1 ADD COLUMN vpk INT AS (pk);
2677 ALTER TABLE t1 DROP COLUMN i;
2678 DROP TABLE t1;
2679 CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2680 INSERT INTO t1 VALUES (1,1);
2681 ALTER TABLE t1 ADD f DATE AFTER a;
2682 ALTER TABLE t1 DROP b, DROP f;
2683 DROP TABLE t1;
2684 CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2685 INSERT INTO t1 VALUES (1,1);
2686 ALTER TABLE t1 ADD COLUMN f INT AFTER a;
2687 ALTER TABLE t1 DROP b, DROP f;
2688 DROP TABLE t1;
2689 CREATE TABLE t1(t TEXT NOT NULL, FULLTEXT(t)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2690 ALTER TABLE t1 MODIFY COLUMN t TEXT;
2691 DROP TABLE t1;
2692 CREATE TABLE t1 (f TINYINT, g SMALLINT UNSIGNED) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2693 INSERT INTO t1 VALUES(127,6502),(-128,33101);
2694 ALTER TABLE t1 MODIFY f SMALLINT DEFAULT 12345,
2695 MODIFY g BIGINT UNSIGNED DEFAULT 1234567;
2696 affected rows: 2
2697 info: Records: 2 Duplicates: 0 Warnings: 0
2698 SELECT * FROM t1;
2699 f g
2700 127 6502
2701 -128 33101
2702 DROP TABLE t1;
2703 CREATE TABLE t1 (f BIT(8)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2704 INSERT INTO t1 VALUES (b'10000000'),(b'00000001');
2705 ALTER TABLE t1 MODIFY f BIT(16);
2706 affected rows: 2
2707 info: Records: 2 Duplicates: 0 Warnings: 0
2708 INSERT INTO t1 VALUES (b'1000000010101111'),(b'10000000');
2709 SELECT HEX(f) FROM t1;
2710 HEX(f)
2711 80
2712 1
2713 80AF
2714 80
2715 ALTER TABLE t1 MODIFY f SMALLINT;
2716 ERROR 22003: Out of range value for column 'f' at row 3
2717 ALTER TABLE t1 MODIFY f SMALLINT UNSIGNED;
2718 affected rows: 4
2719 info: Records: 4 Duplicates: 0 Warnings: 0
2720 SELECT * FROM t1;
2721 f
2722 128
2723 1
2724 32943
2725 128
2726 ALTER TABLE t1 MODIFY f BIT;
2727 ERROR 22001: Data too long for column 'f' at row 1
2728 ALTER TABLE t1 MODIFY f BIT(15);
2729 ERROR 22001: Data too long for column 'f' at row 3
2730 DELETE FROM t1 LIMIT 3;
2731 ALTER TABLE t1 MODIFY f BIT(15);
2732 affected rows: 1
2733 info: Records: 1 Duplicates: 0 Warnings: 0
2734 ALTER TABLE t1 MODIFY f BIT(8);
2735 affected rows: 1
2736 info: Records: 1 Duplicates: 0 Warnings: 0
2737 SELECT HEX(f) FROM t1;
2738 HEX(f)
2739 80
2740 DROP TABLE t1;
2741 CREATE TABLE t1 (b BIT NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET utf16;
2742 INSERT INTO t1 SET b=b'1';
2743 ALTER TABLE t1 CHANGE b c BIT NOT NULL;
2744 affected rows: 0
2745 info: Records: 0 Duplicates: 0 Warnings: 0
2746 SELECT HEX(c) FROM t1;
2747 HEX(c)
2748 1
2749 DROP TABLE t1;
2750 CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2751 INSERT INTO t1() VALUES();
2752 ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42;
2753 affected rows: 0
2754 info: Records: 0 Duplicates: 0 Warnings: 0
2755 ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802,
2756 MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory',
2757 ADD d DATETIME;
2758 affected rows: 1
2759 info: Records: 1 Duplicates: 0 Warnings: 0
2760 INSERT INTO t1() VALUES();
2761 INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now());
2762 SELECT * FROM t1;
2763 c f d
2764 scary -42 NULL
2765 gory 64802 NULL
2766 fury -8388608 1970-01-01 03:00:42
2767 DROP TABLE t1;
2768 CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2769 SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2770 WHERE name = 'test/t1';
2771 INSERT INTO t1 VALUES (-42, -123456);
2772 ALTER TABLE t1 CHANGE t s SMALLINT;
2773 affected rows: 1
2774 info: Records: 1 Duplicates: 0 Warnings: 0
2775 SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2776 WHERE name = 'test/t1';
2777 affected rows: 1
2778 ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT;
2779 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
2780 ALTER TABLE t1 CHANGE m i INT;
2781 affected rows: 1
2782 info: Records: 1 Duplicates: 0 Warnings: 0
2783 SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
2784 WHERE name = 'test/t1';
2785 affected rows: 1
2786 SELECT @table_id1 = @table_id2, @table_id2 = @table_id3;
2787 @table_id1 = @table_id2 @table_id2 = @table_id3
2788 0 0
2789 INSERT IGNORE INTO t1 VALUES (0, -123456);
2790 Warnings:
2791 Warning 1062 Duplicate entry '-123456' for key 'm'
2792 REPLACE INTO t1 VALUES(-42, 123456);
2793 INSERT IGNORE INTO t1 VALUES(32768, 2147483648);
2794 Warnings:
2795 Warning 1264 Out of range value for column 's' at row 1
2796 Warning 1264 Out of range value for column 'i' at row 1
2797 SELECT * FROM t1;
2798 s i
2799 -42 123456
2800 32767 2147483647
2801 DROP TABLE t1;
2802 CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2803 INSERT INTO t1 (c) VALUES(1),(2),(3);
2804 ALTER TABLE t1 MODIFY c BIGINT;
2805 affected rows: 3
2806 info: Records: 3 Duplicates: 0 Warnings: 0
2807 UPDATE t1 SET b=1 WHERE c=2;
2808 UPDATE t1 SET c=4 WHERE a=3;
2809 UPDATE t1 SET b=2 WHERE c>3;
2810 UPDATE t1 SET c=c+1;
2811 ERROR 23000: Duplicate entry '2' for key 'c'
2812 SELECT * FROM t1;
2813 a b c
2814 1 NULL 1
2815 2 1 2
2816 3 2 4
2817 DROP TABLE t1;
2818 CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2819 INSERT INTO t1 VALUES(1,'a');
2820 ALTER TABLE t1 MODIFY b VARCHAR(256), ADD COLUMN c INT;
2821 INSERT INTO t1 VALUES(2,'bah',3);
2822 SELECT * FROM t1;
2823 a b c
2824 1 a NULL
2825 2 bah 3
2826 DROP TABLE t1;
2827 CREATE TABLE t1(a CHAR(5) CHARACTER SET utf8 PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2828 INSERT INTO t1 VALUES('barf');
2829 ALTER TABLE t1 ADD b INT FIRST, ALGORITHM=INSTANT;
2830 ALTER TABLE t1 ADD vb INT AS (b);
2831 SELECT * FROM t1;
2832 b a vb
2833 NULL barf NULL
2834 DROP TABLE t1;
2835 CREATE TABLE t1 (a INT, b TEXT, PRIMARY KEY(b(9))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2836 ALTER TABLE t1 ADD COLUMN c TEXT FIRST;
2837 ALTER TABLE t1 ADD COLUMN d TEXT GENERATED ALWAYS AS (SUBSTR(b,1,499)) FIRST;
2838 DROP TABLE t1;
2839 CREATE TABLE t1(a CHAR(5), b INT, c CHAR(1), d CHAR(1), PRIMARY KEY(a,b))
2840 DEFAULT CHARACTER SET utf8 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2841 INSERT INTO t1 SET a='fubar',b=42;
2842 ALTER TABLE t1 DROP c, DROP d, ALGORITHM=INSTANT;
2843 ALTER TABLE t1 ADD vb INT AS (b);
2844 SELECT * FROM t1;
2845 a b vb
2846 fubar 42 42
2847 DROP TABLE t1;
2848 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2849 ALTER TABLE t1 MODIFY b INT FIRST;
2850 DROP TABLE t1;
2851 CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2852 INSERT INTO t1 SET pk=1;
2853 ALTER TABLE t1 ADD COLUMN b INT;
2854 BEGIN;
2855 UPDATE t1 SET pk=2;
2856 UPDATE t1 SET pk=1;
2857 connection analyze;
2858 SELECT * FROM t1;
2859 pk b
2860 1 NULL
2861 connection default;
2862 DROP TABLE t1;
2863 disconnect analyze;
2864 #
2865 # MDEV-22465: DROP COLUMN is wrongly claimed to be ALGORITHM=INSTANT
2866 #
2867 CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB;
2868 ALTER TABLE t1 DROP b, ALGORITHM=INSTANT;
2869 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: DROP INDEX. Try ALGORITHM=NOCOPY
2870 ALTER TABLE t1 DROP b, ALGORITHM=NOCOPY;
2871 DROP TABLE t1;
2872 SELECT variable_value-@old_instant instants
2873 FROM information_schema.global_status
2874 WHERE variable_name = 'innodb_instant_alter_column';
2875 instants
2876 209
2877 SET GLOBAL innodb_instant_alter_column_allowed = @saved_allowance;
2878 #
2879 # MDEV-18266: Changing an index comment unnecessarily rebuilds index
2880 #
2881 CREATE TABLE t1(a INT, b INT) ENGINE=INNODB;
2882 CREATE INDEX i1 ON t1(a) COMMENT 'comment1';
2883 ALTER TABLE t1 DROP INDEX i1, ADD INDEX i1(a) COMMENT 'comment2', ALGORITHM=INSTANT;
2884 SHOW CREATE TABLE t1;
2885 Table Create Table
2886 t1 CREATE TABLE `t1` (
2887 `a` int(11) DEFAULT NULL,
2888 `b` int(11) DEFAULT NULL,
2889 KEY `i1` (`a`) COMMENT 'comment2'
2890 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
2891 DROP TABLE t1;
2892 #
2893 # MDEV-25057 Assertion `n_fields < dtuple_get_n_fields(entry)'
2894 # failed in dtuple_convert_big_rec
2895 #
2896 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT,
2897 a CHAR(255) NOT NULL,
2898 b CHAR(255) NOT NULL, c INT) ENGINE=InnoDB CHARSET utf32;
2899 ALTER TABLE t1 DROP c;
2900 INSERT INTO t1(a, b) SELECT '', '' FROM seq_1_to_16;
2901 SELECT COUNT(*) FROM t1;
2902 COUNT(*)
2903 16
2904 DROP TABLE t1;
2905 #
2906 # MDEV-25630 Rollback of instant operation adds wrong
2907 # column to secondary index
2908 #
2909 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, f4 INT,
2910 PRIMARY KEY(f1, f4),
2911 KEY(f2))ENGINE=InnoDB;
2912 CREATE TABLE t2 (f1 INT, f2 INT, PRIMARY KEY(f1),
2913 FOREIGN KEY fk (f2) REFERENCES t2(f1)
2914 )ENGINE=InnoDB;
2915 ALTER TABLE t1 ADD f5 INT;
2916 SET FOREIGN_KEY_CHECKS=0;
2917 ALTER TABLE t1 DROP COLUMN f3, ADD FOREIGN KEY fk (f1)
2918 REFERENCES x(x);
2919 ERROR HY000: Failed to add the foreign key constraint 'test/fk' to system tables
2920 ALTER TABLE t1 DROP COLUMN f5;
2921 SHOW CREATE TABLE t1;
2922 Table Create Table
2923 t1 CREATE TABLE `t1` (
2924 `f1` int(11) NOT NULL,
2925 `f2` int(11) DEFAULT NULL,
2926 `f3` int(11) DEFAULT NULL,
2927 `f4` int(11) NOT NULL,
2928 PRIMARY KEY (`f1`,`f4`),
2929 KEY `f2` (`f2`)
2930 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
2931 DROP TABLE t1, t2;
2932 #
2933 # MDEV-28822 Table from older version requires table rebuild when adding column to table with multi-column index
2934 #
2935 CREATE TABLE mdev28822_100427_innodb (
2936 id int not null primary key,
2937 msg varchar(10),
2938 index(id, msg)
2939 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
2940 FLUSH TABLES;
2941 ALTER TABLE mdev28822_100427_innodb ADD i1 INTEGER, ALGORITHM=INSTANT;
2942 DROP TABLE mdev28822_100427_innodb;
2943 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
2944 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;