2
# Bug#36169 create innodb compressed table with too large row size crashed
3
# http://bugs.mysql.com/36169
7
let $file_format=`select @@innodb_file_format`;
8
let $file_per_table=`select @@innodb_file_per_table`;
9
SET GLOBAL innodb_file_format='Barracuda';
10
SET GLOBAL innodb_file_per_table=ON;
13
# The following is copied from http://bugs.mysql.com/36169
14
# (http://bugs.mysql.com/file.php?id=9121)
15
# Probably it can be simplified but that is not obvious.
18
# we care only that the following SQL commands do produce errors
19
# as expected and do not crash the server
21
# Generating 10 tables
22
# Creating a table with 94 columns and 24 indexes
23
DROP TABLE IF EXISTS `table0`;
24
set innodb_strict_mode=on;
25
--error ER_TOO_BIG_ROWSIZE
26
CREATE TABLE IF NOT EXISTS `table0`
32
`col5` SET ('test1','test2','test3'),
36
`col9` SET ('test1','test2','test3'),
38
`col11` DOUBLE PRECISION,
39
`col12` ENUM ('test1','test2','test3'),
42
`col15` SET ('test1','test2','test3'),
47
`col20` DOUBLE PRECISION,
56
`col29` ENUM ('test1','test2','test3'),
84
`col57` ENUM ('test1','test2','test3'),
87
`col60` VARCHAR (232),
96
`col69` ENUM ('test1','test2','test3'),
101
`col74` VARCHAR (170),
102
`col75` SET ('test1','test2','test3'),
115
`col88` VARCHAR (145),
121
KEY `idx0` (`col69`,`col90`,`col8`),
122
KEY `idx1` (`col60`),
123
KEY `idx2` (`col60`,`col70`,`col74`),
124
KEY `idx3` (`col22`,`col32`,`col72`,`col30`),
125
KEY `idx4` (`col29`),
126
KEY `idx5` (`col19`,`col45`(143)),
127
KEY `idx6` (`col46`,`col48`,`col5`,`col39`(118)),
128
KEY `idx7` (`col48`,`col61`),
129
KEY `idx8` (`col93`),
130
KEY `idx9` (`col31`),
131
KEY `idx10` (`col30`,`col21`),
132
KEY `idx11` (`col67`),
133
KEY `idx12` (`col44`,`col6`,`col8`,`col38`(226)),
134
KEY `idx13` (`col71`,`col41`,`col15`,`col49`(88)),
135
KEY `idx14` (`col78`),
136
KEY `idx15` (`col63`,`col67`,`col64`),
137
KEY `idx16` (`col17`,`col86`),
138
KEY `idx17` (`col77`,`col56`,`col10`,`col55`(24)),
139
KEY `idx18` (`col62`),
140
KEY `idx19` (`col31`,`col57`,`col56`,`col53`),
141
KEY `idx20` (`col46`),
142
KEY `idx21` (`col83`(54)),
143
KEY `idx22` (`col51`,`col7`(120)),
144
KEY `idx23` (`col7`(163),`col31`,`col71`,`col14`)
145
)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
147
# Creating a table with 10 columns and 32 indexes
148
DROP TABLE IF EXISTS `table1`;
149
--error ER_TOO_BIG_ROWSIZE
150
CREATE TABLE IF NOT EXISTS `table1`
157
`col6` SET ('test1','test2','test3'),
161
KEY `idx0` (`col5`(101),`col7`,`col8`),
163
KEY `idx2` (`col4`(177),`col9`(126),`col6`,`col3`),
164
KEY `idx3` (`col5`(160)),
165
KEY `idx4` (`col9`(242)),
166
KEY `idx5` (`col4`(139),`col2`,`col3`),
168
KEY `idx7` (`col6`,`col2`,`col0`,`col3`),
169
KEY `idx8` (`col9`(66)),
170
KEY `idx9` (`col5`(253)),
171
KEY `idx10` (`col1`,`col7`,`col2`),
172
KEY `idx11` (`col9`(242),`col0`,`col8`,`col5`(163)),
173
KEY `idx12` (`col8`),
174
KEY `idx13` (`col0`,`col9`(37)),
175
KEY `idx14` (`col0`),
176
KEY `idx15` (`col5`(111)),
177
KEY `idx16` (`col8`,`col0`,`col5`(13)),
178
KEY `idx17` (`col4`(139)),
179
KEY `idx18` (`col5`(189),`col2`,`col3`,`col9`(136)),
180
KEY `idx19` (`col0`,`col3`,`col1`,`col8`),
181
KEY `idx20` (`col8`),
182
KEY `idx21` (`col0`,`col7`,`col9`(227),`col3`),
183
KEY `idx22` (`col0`),
184
KEY `idx23` (`col2`),
185
KEY `idx24` (`col3`),
186
KEY `idx25` (`col2`,`col3`),
187
KEY `idx26` (`col0`),
188
KEY `idx27` (`col5`(254)),
189
KEY `idx28` (`col3`),
190
KEY `idx29` (`col3`),
191
KEY `idx30` (`col7`,`col3`,`col0`,`col4`(220)),
192
KEY `idx31` (`col4`(1),`col0`)
193
)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
195
# Creating a table with 141 columns and 18 indexes
196
DROP TABLE IF EXISTS `table2`;
197
--error ER_TOO_BIG_ROWSIZE
198
CREATE TABLE IF NOT EXISTS `table2`
201
`col2` VARCHAR (209),
220
`col21` SET ('test1','test2','test3'),
224
`col25` ENUM ('test1','test2','test3'),
226
`col27` VARCHAR (116),
230
`col31` DOUBLE PRECISION,
237
`col38` VARCHAR (253),
246
`col47` DOUBLE PRECISION,
247
`col48` SET ('test1','test2','test3'),
248
`col49` DOUBLE PRECISION,
249
`col50` VARCHAR (97),
252
`col53` ENUM ('test1','test2','test3'),
265
`col66` VARCHAR (212),
272
`col73` ENUM ('test1','test2','test3'),
281
`col82` SET ('test1','test2','test3'),
282
`col83` DOUBLE PRECISION,
284
`col85` VARCHAR (184),
285
`col86` DOUBLE PRECISION,
291
`col92` ENUM ('test1','test2','test3'),
300
`col101` DOUBLE PRECISION,
309
`col110` SET ('test1','test2','test3'),
318
`col119` SET ('test1','test2','test3'),
325
`col126` ENUM ('test1','test2','test3'),
327
`col128` DOUBLE PRECISION,
329
`col130` VARCHAR (159),
333
`col134` SET ('test1','test2','test3'),
335
`col136` SET ('test1','test2','test3'),
340
KEY `idx0` (`col14`(139),`col24`(208),`col38`,`col35`),
341
KEY `idx1` (`col48`,`col118`,`col29`(131),`col100`),
342
KEY `idx2` (`col86`,`col67`,`col43`(175)),
343
KEY `idx3` (`col19`),
344
KEY `idx4` (`col40`(220),`col67`),
345
KEY `idx5` (`col99`,`col56`),
346
KEY `idx6` (`col68`,`col28`,`col137`(157)),
347
KEY `idx7` (`col51`(160),`col99`,`col45`,`col39`(9)),
348
KEY `idx8` (`col15`,`col52`,`col90`,`col94`),
349
KEY `idx9` (`col24`(3),`col139`(248),`col108`(118),`col41`),
350
KEY `idx10` (`col36`,`col92`,`col114`),
351
KEY `idx11` (`col115`,`col9`(116)),
352
KEY `idx12` (`col130`,`col93`,`col134`),
353
KEY `idx13` (`col123`(65)),
354
KEY `idx14` (`col44`(90),`col86`,`col119`),
355
KEY `idx15` (`col69`),
356
KEY `idx16` (`col132`,`col81`(118),`col18`),
357
KEY `idx17` (`col24`(250),`col7`,`col92`,`col45`)
358
)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
360
# Creating a table with 199 columns and 1 indexes
361
DROP TABLE IF EXISTS `table3`;
362
--error ER_TOO_BIG_ROWSIZE
363
CREATE TABLE IF NOT EXISTS `table3`
365
`col1` SET ('test1','test2','test3'),
392
`col28` VARCHAR (106),
410
`col46` DOUBLE PRECISION,
413
`col49` DOUBLE PRECISION,
421
`col57` ENUM ('test1','test2','test3'),
430
`col66` VARCHAR (88),
462
`col98` ENUM ('test1','test2','test3'),
467
`col103` SET ('test1','test2','test3'),
474
`col110` VARCHAR (131),
480
`col116` SET ('test1','test2','test3'),
481
`col117` ENUM ('test1','test2','test3'),
484
`col120` SET ('test1','test2','test3'),
487
`col123` VARCHAR (242),
498
`col134` DOUBLE PRECISION,
500
`col136` SET ('test1','test2','test3'),
502
`col138` DOUBLE PRECISION,
505
`col141` SET ('test1','test2','test3'),
507
`col143` VARCHAR (26),
510
`col146` SET ('test1','test2','test3'),
527
`col163` VARCHAR (90),
530
`col166` ENUM ('test1','test2','test3'),
531
`col167` DOUBLE PRECISION,
532
`col168` DOUBLE PRECISION,
539
`col175` VARCHAR (14),
540
`col176` VARCHAR (90),
547
`col183` DOUBLE PRECISION,
563
KEY `idx0` (`col39`,`col23`)
564
)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
566
# Creating a table with 133 columns and 16 indexes
567
DROP TABLE IF EXISTS `table4`;
568
--error ER_TOO_BIG_ROWSIZE
569
CREATE TABLE IF NOT EXISTS `table4`
570
(`col0` VARCHAR (60),
583
`col13` VARCHAR (213),
590
`col20` SET ('test1','test2','test3'),
594
`col24` ENUM ('test1','test2','test3'),
601
`col31` SET ('test1','test2','test3'),
605
`col35` VARCHAR (146),
609
`col39` SET ('test1','test2','test3'),
623
`col53` SET ('test1','test2','test3'),
635
`col65` ENUM ('test1','test2','test3'),
636
`col66` DOUBLE PRECISION,
646
`col76` VARCHAR (122),
653
`col83` DOUBLE PRECISION,
658
`col88` DOUBLE PRECISION,
664
`col94` SET ('test1','test2','test3'),
666
`col96` SET ('test1','test2','test3'),
672
`col102` VARCHAR (225),
683
`col113` ENUM ('test1','test2','test3'),
689
`col119` DOUBLE PRECISION,
703
KEY `idx0` (`col130`),
704
KEY `idx1` (`col30`,`col55`,`col19`(31)),
705
KEY `idx2` (`col104`(186)),
706
KEY `idx3` (`col131`),
707
KEY `idx4` (`col64`,`col93`,`col2`(11)),
708
KEY `idx5` (`col34`,`col121`,`col22`),
709
KEY `idx6` (`col33`,`col55`,`col83`),
710
KEY `idx7` (`col17`,`col87`(245),`col99`(17)),
711
KEY `idx8` (`col65`,`col120`),
712
KEY `idx9` (`col82`),
713
KEY `idx10` (`col9`(72)),
714
KEY `idx11` (`col88`),
715
KEY `idx12` (`col128`,`col9`(200),`col71`,`col66`),
716
KEY `idx13` (`col77`(126)),
717
KEY `idx14` (`col105`(26),`col13`,`col117`),
718
KEY `idx15` (`col4`(246),`col130`,`col115`,`col3`(141))
719
)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
721
# Creating a table with 176 columns and 13 indexes
722
DROP TABLE IF EXISTS `table5`;
723
--error ER_TOO_BIG_ROWSIZE
724
CREATE TABLE IF NOT EXISTS `table5`
736
`col11` SET ('test1','test2','test3'),
743
`col18` SET ('test1','test2','test3'),
750
`col25` DOUBLE PRECISION,
765
`col40` SET ('test1','test2','test3'),
770
`col45` VARCHAR (126),
772
`col47` DOUBLE PRECISION,
778
`col53` VARCHAR (208),
779
`col54` VARCHAR (207),
782
`col57` ENUM ('test1','test2','test3'),
785
`col60` VARCHAR (73),
794
`col69` VARCHAR (58),
799
`col74` DOUBLE PRECISION,
808
`col83` ENUM ('test1','test2','test3'),
810
`col85` SET ('test1','test2','test3'),
814
`col89` ENUM ('test1','test2','test3'),
823
`col98` VARCHAR (198),
840
`col115` VARCHAR (7),
858
`col133` VARCHAR (123),
860
`col135` VARCHAR (225),
869
`col144` VARCHAR (236),
877
`col152` VARCHAR (72),
879
`col154` VARCHAR (165),
882
`col157` DOUBLE PRECISION,
887
`col162` DOUBLE PRECISION,
889
`col164` ENUM ('test1','test2','test3'),
897
`col172` VARCHAR (30),
901
KEY `idx0` (`col16`,`col156`(139),`col97`,`col120`),
902
KEY `idx1` (`col24`,`col0`(108)),
903
KEY `idx2` (`col117`,`col173`(34),`col132`,`col82`),
904
KEY `idx3` (`col2`(86)),
905
KEY `idx4` (`col2`(43)),
906
KEY `idx5` (`col83`,`col35`(87),`col111`),
907
KEY `idx6` (`col6`,`col134`,`col92`),
908
KEY `idx7` (`col56`),
909
KEY `idx8` (`col30`,`col53`,`col129`(66)),
910
KEY `idx9` (`col53`,`col113`(211),`col32`,`col15`(75)),
911
KEY `idx10` (`col34`),
912
KEY `idx11` (`col126`),
913
KEY `idx12` (`col24`)
914
)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
916
# Creating a table with 179 columns and 46 indexes
917
DROP TABLE IF EXISTS `table6`;
918
--error ER_TOO_BIG_ROWSIZE
919
CREATE TABLE IF NOT EXISTS `table6`
920
(`col0` ENUM ('test1','test2','test3'),
944
`col24` SET ('test1','test2','test3'),
968
`col48` ENUM ('test1','test2','test3'),
972
`col52` DOUBLE PRECISION,
973
`col53` ENUM ('test1','test2','test3'),
975
`col55` DOUBLE PRECISION,
981
`col61` ENUM ('test1','test2','test3'),
1001
`col81` ENUM ('test1','test2','test3'),
1002
`col82` VARCHAR (221),
1020
`col100` SET ('test1','test2','test3'),
1023
`col103` MEDIUMBLOB,
1024
`col104` MEDIUMTEXT,
1027
`col107` VARCHAR (26),
1037
`col117` SET ('test1','test2','test3'),
1048
`col128` MEDIUMBLOB,
1051
`col131` MEDIUMBLOB,
1060
`col140` SET ('test1','test2','test3'),
1061
`col141` ENUM ('test1','test2','test3'),
1062
`col142` ENUM ('test1','test2','test3'),
1066
`col146` DOUBLE PRECISION,
1068
`col148` MEDIUMTEXT,
1070
`col150` SET ('test1','test2','test3'),
1071
`col151` MEDIUMTEXT,
1072
`col152` CHAR (126),
1074
`col154` CHAR (243),
1075
`col155` SET ('test1','test2','test3'),
1076
`col156` SET ('test1','test2','test3'),
1087
`col167` VARCHAR (63),
1091
`col171` ENUM ('test1','test2','test3'),
1096
`col176` MEDIUMBLOB,
1099
KEY `idx0` (`col131`(219)),
1100
KEY `idx1` (`col67`,`col122`,`col59`,`col87`(33)),
1101
KEY `idx2` (`col83`,`col42`,`col57`(152)),
1102
KEY `idx3` (`col106`(124)),
1103
KEY `idx4` (`col173`,`col80`,`col165`,`col89`(78)),
1104
KEY `idx5` (`col174`,`col145`(108),`col23`(228),`col141`),
1105
KEY `idx6` (`col157`,`col140`),
1106
KEY `idx7` (`col130`(188),`col15`),
1107
KEY `idx8` (`col52`),
1108
KEY `idx9` (`col144`),
1109
KEY `idx10` (`col155`),
1110
KEY `idx11` (`col62`(230),`col1`(109)),
1111
KEY `idx12` (`col151`(24),`col95`(85)),
1112
KEY `idx13` (`col114`),
1113
KEY `idx14` (`col42`,`col98`(56),`col146`),
1114
KEY `idx15` (`col147`,`col39`(254),`col35`),
1115
KEY `idx16` (`col79`),
1116
KEY `idx17` (`col65`),
1117
KEY `idx18` (`col149`(165),`col168`(119),`col32`,`col117`),
1118
KEY `idx19` (`col64`),
1119
KEY `idx20` (`col93`),
1120
KEY `idx21` (`col64`,`col113`,`col104`(182)),
1121
KEY `idx22` (`col52`,`col111`(189)),
1122
KEY `idx23` (`col45`),
1123
KEY `idx24` (`col154`,`col107`,`col110`(159)),
1124
KEY `idx25` (`col149`(1),`col87`(131)),
1125
KEY `idx26` (`col58`,`col115`,`col63`),
1126
KEY `idx27` (`col95`(9),`col0`,`col87`(113)),
1127
KEY `idx28` (`col92`,`col130`(1)),
1128
KEY `idx29` (`col151`(129),`col137`(254),`col13`),
1129
KEY `idx30` (`col49`),
1130
KEY `idx31` (`col28`),
1131
KEY `idx32` (`col83`,`col146`),
1132
KEY `idx33` (`col155`,`col90`,`col17`(245)),
1133
KEY `idx34` (`col174`,`col169`(44),`col107`),
1134
KEY `idx35` (`col113`),
1135
KEY `idx36` (`col52`),
1136
KEY `idx37` (`col16`,`col120`(190)),
1137
KEY `idx38` (`col28`),
1138
KEY `idx39` (`col131`(165)),
1139
KEY `idx40` (`col135`,`col26`(86)),
1140
KEY `idx41` (`col69`,`col94`),
1141
KEY `idx42` (`col105`,`col151`(38),`col97`),
1142
KEY `idx43` (`col88`),
1143
KEY `idx44` (`col176`(100),`col42`,`col73`(189),`col94`),
1144
KEY `idx45` (`col2`(27),`col27`(116))
1145
)engine=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
1147
DROP TABLE IF EXISTS table0;
1148
DROP TABLE IF EXISTS table1;
1149
DROP TABLE IF EXISTS table2;
1150
DROP TABLE IF EXISTS table3;
1151
DROP TABLE IF EXISTS table4;
1152
DROP TABLE IF EXISTS table5;
1153
DROP TABLE IF EXISTS table6;
1155
EVAL SET GLOBAL innodb_file_format=$file_format;
1156
EVAL SET GLOBAL innodb_file_per_table=$file_per_table;