Debian Codegraph @

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

raw
1 ##############################################
2 # Test instant ADD/DROP COLUMN for REDUNDANT format
3 ##############################################
4 SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode;
5 # ------------------------------------------------------------
6 # Create a table with 3 columns. [id, c2, c3]
7 # ------------------------------------------------------------
8 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000)) ROW_FORMAT=REDUNDANT;;
9 # Metadata from INFORMATION_SCHEMA.TABLES
10 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
11 test/t1 6 0 0
12 # Metadata from INFORMATION_SCHEMA.COLUMNS
13 NAME POS MTYPE PRTYPE HAS_DEFAULT
14 id 0 6 1283 0
15 c1 1 12 16715791 0
16 c2 2 12 16715791 0
17 SET SESSION innodb_strict_mode=OFF;
18 # ------------------------------------------------------------
19 # Add a new column. It causes max_row_size > permissible size
20 # ------------------------------------------------------------
21 ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT;
22 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
23 ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';
24 Warnings:
25 Warning 139 Row size too large (> 1979). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
26 Warning 139 Row size too large (> 1979). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
27 # Metadata from INFORMATION_SCHEMA.TABLES
28 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
29 test/t1 7 0 0
30 # Metadata from INFORMATION_SCHEMA.COLUMNS
31 NAME POS MTYPE PRTYPE HAS_DEFAULT
32 id 0 6 1283 0
33 c1 1 12 16715791 0
34 c2 2 12 16715791 0
35 c3 3 12 16716047 0
36 INSERT INTO t1 (id, c1, c2) VALUES(1, repeat('a', 4000), repeat('b', 4000));
37 ERROR 42000: Row size too large (> 1979). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
38 INSERT INTO t1 (id, c1, c2) VALUES(1, repeat('a', 4), repeat('b', 4));
39 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1 ORDER BY id;
40 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
41 1 aaaa bbbb dddddddddd
42 ALTER TABLE t1 ADD COLUMN c4 INT, ALGORITHM=INSTANT;
43 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
44 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
45 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
46 ###########
47 # CLEANUP #
48 ###########
49 DROP TABLE t1;
50 SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;
51 # ------------------------------------------------------------
52 # Create a partition table with 3 columns. [c1, c2, c3, c4]
53 # ------------------------------------------------------------
54 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=REDUNDANT PARTITION BY RANGE(c1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);;
55 # Metadata from INFORMATION_SCHEMA.TABLES
56 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
57 test/t1#p#p0 7 0 0
58 # Metadata from INFORMATION_SCHEMA.COLUMNS
59 NAME POS MTYPE PRTYPE HAS_DEFAULT
60 c1 0 6 1027 0
61 c2 1 6 1027 0
62 c3 2 6 1027 0
63 c4 3 5 16711932 0
64 # Metadata from INFORMATION_SCHEMA.TABLES
65 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
66 test/t1#p#p1 7 0 0
67 # Metadata from INFORMATION_SCHEMA.COLUMNS
68 NAME POS MTYPE PRTYPE HAS_DEFAULT
69 c1 0 6 1027 0
70 c2 1 6 1027 0
71 c3 2 6 1027 0
72 c4 3 5 16711932 0
73 # Metadata from INFORMATION_SCHEMA.TABLES
74 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
75 test/t1#p#p2 7 0 0
76 # Metadata from INFORMATION_SCHEMA.COLUMNS
77 NAME POS MTYPE PRTYPE HAS_DEFAULT
78 c1 0 6 1027 0
79 c2 1 6 1027 0
80 c3 2 6 1027 0
81 c4 3 5 16711932 0
82 # Metadata from INFORMATION_SCHEMA.TABLES
83 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
84 test/t1#p#p3 7 0 0
85 # Metadata from INFORMATION_SCHEMA.COLUMNS
86 NAME POS MTYPE PRTYPE HAS_DEFAULT
87 c1 0 6 1027 0
88 c2 1 6 1027 0
89 c3 2 6 1027 0
90 c4 3 5 16711932 0
91 SET SESSION innodb_strict_mode=OFF;
92 # ------------------------------------------------------------
93 # Add new columns. It causes max_row_size > permissible size
94 # ------------------------------------------------------------
95 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ADD COLUMN c6 TEXT, ALGORITHM=INSTANT;
96 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
97 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ADD COLUMN c6 TEXT;
98 Warnings:
99 Warning 139 Row size too large (> 1979). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
100 Warning 139 Row size too large (> 1979). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
101 Warning 139 Row size too large (> 1979). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
102 Warning 139 Row size too large (> 1979). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
103 # Metadata from INFORMATION_SCHEMA.TABLES
104 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
105 test/t1#p#p0 9 0 0
106 # Metadata from INFORMATION_SCHEMA.COLUMNS
107 NAME POS MTYPE PRTYPE HAS_DEFAULT
108 c1 0 6 1027 0
109 c2 1 6 1027 0
110 c3 2 6 1027 0
111 c4 3 5 16711932 0
112 c5 4 12 16716047 0
113 c6 5 5 16711932 0
114 # Metadata from INFORMATION_SCHEMA.TABLES
115 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
116 test/t1#p#p1 9 0 0
117 # Metadata from INFORMATION_SCHEMA.COLUMNS
118 NAME POS MTYPE PRTYPE HAS_DEFAULT
119 c1 0 6 1027 0
120 c2 1 6 1027 0
121 c3 2 6 1027 0
122 c4 3 5 16711932 0
123 c5 4 12 16716047 0
124 c6 5 5 16711932 0
125 # Metadata from INFORMATION_SCHEMA.TABLES
126 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
127 test/t1#p#p2 9 0 0
128 # Metadata from INFORMATION_SCHEMA.COLUMNS
129 NAME POS MTYPE PRTYPE HAS_DEFAULT
130 c1 0 6 1027 0
131 c2 1 6 1027 0
132 c3 2 6 1027 0
133 c4 3 5 16711932 0
134 c5 4 12 16716047 0
135 c6 5 5 16711932 0
136 # Metadata from INFORMATION_SCHEMA.TABLES
137 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
138 test/t1#p#p3 9 0 0
139 # Metadata from INFORMATION_SCHEMA.COLUMNS
140 NAME POS MTYPE PRTYPE HAS_DEFAULT
141 c1 0 6 1027 0
142 c2 1 6 1027 0
143 c3 2 6 1027 0
144 c4 3 5 16711932 0
145 c5 4 12 16716047 0
146 c6 5 5 16711932 0
147 INSERT INTO t1 (c1, c2, c3, c4, c6) VALUES(1, 2, 3, repeat('a', 1000), repeat('c', 1000));
148 ERROR 42000: Row size too large (> 1979). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
149 INSERT INTO t1 (c1, c2, c3, c4, c6) VALUES(1, 2, 3, 'abcdefg', 'hijklmnop');
150 SELECT c1, c2, c3, c4, LEFT(c5 , 10), c6 from t1;
151 c1 c2 c3 c4 LEFT(c5 , 10) c6
152 1 2 3 abcdefg dddddddddd hijklmnop
153 ALTER TABLE t1 ADD COLUMN c7 INT, ALGORITHM=INSTANT;
154 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
155 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
156 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
157 ###########
158 # CLEANUP #
159 ###########
160 DROP TABLE t1;
161 SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;
162 ############################################
163 # No need to run for DYNAMIC
164 ############################################
165 ############################################
166 # Test instant ADD/DROP COLUMN for COMPACT format
167 ############################################
168 SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode;
169 # ------------------------------------------------------------
170 # Create a table with 3 columns. [id, c2, c3]
171 # ------------------------------------------------------------
172 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000)) ROW_FORMAT=COMPACT;;
173 # Metadata from INFORMATION_SCHEMA.TABLES
174 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
175 test/t1 6 0 0
176 # Metadata from INFORMATION_SCHEMA.COLUMNS
177 NAME POS MTYPE PRTYPE HAS_DEFAULT
178 id 0 6 1283 0
179 c1 1 12 16715791 0
180 c2 2 12 16715791 0
181 SET SESSION innodb_strict_mode=OFF;
182 # ------------------------------------------------------------
183 # Add a new column. It causes max_row_size > permissible size
184 # ------------------------------------------------------------
185 ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT;
186 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
187 ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';
188 Warnings:
189 Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
190 Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
191 # Metadata from INFORMATION_SCHEMA.TABLES
192 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
193 test/t1 7 0 0
194 # Metadata from INFORMATION_SCHEMA.COLUMNS
195 NAME POS MTYPE PRTYPE HAS_DEFAULT
196 id 0 6 1283 0
197 c1 1 12 16715791 0
198 c2 2 12 16715791 0
199 c3 3 12 16716047 0
200 INSERT INTO t1 (id, c1, c2) VALUES(1, repeat('a', 4000), repeat('b', 4000));
201 ERROR 42000: Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
202 INSERT INTO t1 (id, c1, c2) VALUES(1, repeat('a', 4), repeat('b', 4));
203 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1 ORDER BY id;
204 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
205 1 aaaa bbbb dddddddddd
206 ALTER TABLE t1 ADD COLUMN c4 INT, ALGORITHM=INSTANT;
207 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
208 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
209 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
210 ###########
211 # CLEANUP #
212 ###########
213 DROP TABLE t1;
214 SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;
215 # ------------------------------------------------------------
216 # Create a partition table with 3 columns. [c1, c2, c3, c4]
217 # ------------------------------------------------------------
218 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=COMPACT PARTITION BY RANGE(c1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);;
219 # Metadata from INFORMATION_SCHEMA.TABLES
220 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
221 test/t1#p#p0 7 0 0
222 # Metadata from INFORMATION_SCHEMA.COLUMNS
223 NAME POS MTYPE PRTYPE HAS_DEFAULT
224 c1 0 6 1027 0
225 c2 1 6 1027 0
226 c3 2 6 1027 0
227 c4 3 5 16711932 0
228 # Metadata from INFORMATION_SCHEMA.TABLES
229 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
230 test/t1#p#p1 7 0 0
231 # Metadata from INFORMATION_SCHEMA.COLUMNS
232 NAME POS MTYPE PRTYPE HAS_DEFAULT
233 c1 0 6 1027 0
234 c2 1 6 1027 0
235 c3 2 6 1027 0
236 c4 3 5 16711932 0
237 # Metadata from INFORMATION_SCHEMA.TABLES
238 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
239 test/t1#p#p2 7 0 0
240 # Metadata from INFORMATION_SCHEMA.COLUMNS
241 NAME POS MTYPE PRTYPE HAS_DEFAULT
242 c1 0 6 1027 0
243 c2 1 6 1027 0
244 c3 2 6 1027 0
245 c4 3 5 16711932 0
246 # Metadata from INFORMATION_SCHEMA.TABLES
247 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
248 test/t1#p#p3 7 0 0
249 # Metadata from INFORMATION_SCHEMA.COLUMNS
250 NAME POS MTYPE PRTYPE HAS_DEFAULT
251 c1 0 6 1027 0
252 c2 1 6 1027 0
253 c3 2 6 1027 0
254 c4 3 5 16711932 0
255 SET SESSION innodb_strict_mode=OFF;
256 # ------------------------------------------------------------
257 # Add new columns. It causes max_row_size > permissible size
258 # ------------------------------------------------------------
259 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ADD COLUMN c6 TEXT, ALGORITHM=INSTANT;
260 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
261 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ADD COLUMN c6 TEXT;
262 Warnings:
263 Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
264 Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
265 Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
266 Warning 139 Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
267 # Metadata from INFORMATION_SCHEMA.TABLES
268 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
269 test/t1#p#p0 9 0 0
270 # Metadata from INFORMATION_SCHEMA.COLUMNS
271 NAME POS MTYPE PRTYPE HAS_DEFAULT
272 c1 0 6 1027 0
273 c2 1 6 1027 0
274 c3 2 6 1027 0
275 c4 3 5 16711932 0
276 c5 4 12 16716047 0
277 c6 5 5 16711932 0
278 # Metadata from INFORMATION_SCHEMA.TABLES
279 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
280 test/t1#p#p1 9 0 0
281 # Metadata from INFORMATION_SCHEMA.COLUMNS
282 NAME POS MTYPE PRTYPE HAS_DEFAULT
283 c1 0 6 1027 0
284 c2 1 6 1027 0
285 c3 2 6 1027 0
286 c4 3 5 16711932 0
287 c5 4 12 16716047 0
288 c6 5 5 16711932 0
289 # Metadata from INFORMATION_SCHEMA.TABLES
290 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
291 test/t1#p#p2 9 0 0
292 # Metadata from INFORMATION_SCHEMA.COLUMNS
293 NAME POS MTYPE PRTYPE HAS_DEFAULT
294 c1 0 6 1027 0
295 c2 1 6 1027 0
296 c3 2 6 1027 0
297 c4 3 5 16711932 0
298 c5 4 12 16716047 0
299 c6 5 5 16711932 0
300 # Metadata from INFORMATION_SCHEMA.TABLES
301 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
302 test/t1#p#p3 9 0 0
303 # Metadata from INFORMATION_SCHEMA.COLUMNS
304 NAME POS MTYPE PRTYPE HAS_DEFAULT
305 c1 0 6 1027 0
306 c2 1 6 1027 0
307 c3 2 6 1027 0
308 c4 3 5 16711932 0
309 c5 4 12 16716047 0
310 c6 5 5 16711932 0
311 INSERT INTO t1 (c1, c2, c3, c4, c6) VALUES(1, 2, 3, repeat('a', 1000), repeat('c', 1000));
312 ERROR 42000: Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
313 INSERT INTO t1 (c1, c2, c3, c4, c6) VALUES(1, 2, 3, 'abcdefg', 'hijklmnop');
314 SELECT c1, c2, c3, c4, LEFT(c5 , 10), c6 from t1;
315 c1 c2 c3 c4 LEFT(c5 , 10) c6
316 1 2 3 abcdefg dddddddddd hijklmnop
317 ALTER TABLE t1 ADD COLUMN c7 INT, ALGORITHM=INSTANT;
318 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
319 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
320 ERROR HY000: Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY.
321 ###########
322 # CLEANUP #
323 ###########
324 DROP TABLE t1;
325 SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;