CreateTableConstraintStep ddlList =
(CreateTableConstraintStep) dslContext
.createTableIfNotExists(getDSLTableName(List))
.columns(fields)
.constraints(primaryKey(ID_COLUMN))
.storage("ROW_FORMAT=COMPRESSED");
I'm trying to create a table with 250 columns and ROW_FORMAT=COMPRESSED
on an Aurora mysql database. I am getting the following error.
Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
I still seem to be getting this Row size too large exception despite the fact that the table's storage is configured to use ROW_FORMAT=COMPRESSED
I checked the DBs parameters to make sure innodb_file_format = Barracuda
and innodb_file_per_table = ON
and this is the case.
innodb_file_format : Barracuda
innodb_file_format_check : ON
innodb_file_format_max : Barracuda
innodb_file_per_table : ON
version : 5.7.12-log
What's interesting is the threshold for number of columns is 190 columns before getting this error and this is when using the defualt row format which is COMPACT
. When changing the row format to COMPRESSED
, it doesn't seem to increase that threshold at all meaning it makes zero difference, so i'm leaning towards the row format still being COMPACT
even though I set it to COMPRESSED
?
This is my query.
CREATE TABLE IF NOT EXISTS `table1`.`list1`(id varchar(100) NOT NULL, modified_at timestamp(3) NOT
NULL, time_zones_last_updated timestamp(3) NULL, sequence_id bigint unsigned NOT
NULL, contactable boolean NOT NULL DEFAULT TRUE, contactable_voice boolean NULL,
contactable_sms boolean NULL, contactable_email boolean NOT NULL DEFAULT TRUE,
zipcode_timezone_id tinyint NOT NULL DEFAULT -1, data_0 blob NULL, data_1 blob NULL,
data_2 blob NULL, data_3 blob NULL, data_4 blob NULL, data_5 blob NULL, data_6 blob NULL,
data_7 blob NULL, data_8 blob NULL, data_9 blob NULL, data_10 blob NULL, data_11 blob NULL, data_12 blob NULL, data_13 blob NULL, data_14 blob NULL, data_15 blob NULL, data_16 blob NULL, data_17 blob NULL, data_18 blob NULL, data_19 blob NULL, data_20 blob NULL, data_21 blob NULL, data_22 blob NULL, data_23 blob NULL, data_24 blob NULL, data_25 blob NULL, data_26 blob NULL, data_27 blob NULL, data_28 blob NULL, data_29 blob NULL, data_30 blob NULL, data_31 blob NULL, data_32 blob NULL, data_33 blob NULL, data_34 blob NULL, data_35 blob NULL, data_36 blob NULL, data_37 blob NULL, data_38 blob NULL, data_39 blob NULL, data_40 blob NULL, data_41 blob NULL, data_42 blob NULL, data_43 blob NULL, data_44 blob NULL, data_45 blob NULL, data_46 blob NULL, data_47 blob NULL, data_48 blob NULL, data_49 blob NULL, data_50 blob NULL, data_51 blob NULL, data_52 blob NULL, data_53 blob NULL, data_54 blob NULL, data_55 blob NULL, data_56 blob NULL, data_57 blob NULL, data_58 blob NULL, data_59 blob NULL, data_60 blob NULL, data_61 blob NULL, data_62 blob NULL, data_63 blob NULL, data_64 blob NULL, data_65 blob NULL, data_66 blob NULL, data_67 blob NULL, data_68 blob NULL, data_69 blob NULL, data_70 blob NULL, data_71 blob NULL, data_72 blob NULL, data_73 blob NULL, data_74 blob NULL, data_75 blob NULL, data_76 blob NULL, data_77 blob NULL, data_78 blob NULL, data_79 blob NULL, data_80 blob NULL, data_81 blob NULL, data_82 blob NULL, data_83 blob NULL, data_84 blob NULL, data_85 blob NULL, data_86 blob NULL, data_87 blob NULL, data_88 blob NULL, data_89 blob NULL, data_90 blob NULL, data_91 blob NULL, data_92 blob NULL, data_93 blob NULL, data_94 blob NULL, data_95 blob NULL, data_96 blob NULL, data_97 blob NULL, data_98 blob NULL, data_99 blob NULL, data_100 blob NULL, data_101 blob NULL, data_102 blob NULL, data_103 blob NULL, data_104 blob NULL, data_105 blob NULL, data_106 blob NULL, data_107 blob NULL, data_108 blob NULL, data_109 blob NULL, data_110 blob NULL, data_111 blob NULL, data_112 blob NULL, data_113 blob NULL, data_114 blob NULL, data_115 blob NULL, data_116 blob NULL, data_117 blob NULL, data_118 blob NULL, data_119 blob NULL, data_120 blob NULL, data_121 blob NULL, data_122 blob NULL, data_123 blob NULL, data_124 blob NULL, data_125 blob NULL, data_126 blob NULL, data_127 blob NULL, data_128 blob NULL, data_129 blob NULL, data_130 blob NULL, data_131 blob NULL, data_132 blob NULL, data_133 blob NULL, data_134 blob NULL, data_135 blob NULL, data_136 blob NULL, data_137 blob NULL, data_138 blob NULL, data_139 blob NULL, data_140 blob NULL, data_141 blob NULL, data_142 blob NULL, data_143 blob NULL, data_144 blob NULL, data_145 blob NULL, data_146 blob NULL, data_147 blob NULL, data_148 blob NULL, data_149 blob NULL, data_150 blob NULL, data_151 blob NULL, data_152 blob NULL, data_153 blob NULL, data_154 blob NULL, data_155 blob NULL, data_156 blob NULL, data_157 blob NULL, data_158 blob NULL, data_159 blob NULL, data_160 blob NULL, data_161 blob NULL, data_162 blob NULL, data_163 blob NULL, data_164 blob NULL, data_165 blob NULL, data_166 blob NULL, data_167 blob NULL, data_168 blob NULL, data_169 blob NULL, data_170 blob NULL, data_171 blob NULL, data_172 blob NULL, data_173 blob NULL, data_174 blob NULL, data_175 blob NULL, data_176 blob NULL, data_177 blob NULL, data_178 blob NULL, data_179 blob NULL, data_180 blob NULL, data_181 blob NULL, data_182 blob NULL, data_183 blob NULL, data_184 blob NULL, data_185 blob NULL, data_186 blob NULL, data_187 blob NULL, data_188 blob NULL, data_189 blob NULL, data_190 blob NULL, data_191 blob NULL, data_192 blob NULL, data_193 blob NULL, data_194 blob NULL, data_195 blob NULL, PRIMARY KEY (`id`)) ROW_FORMAT=COMPRESSED];
I'm wondering is there anything else I can do to get ROW_FORMAT=COMPRESSED
to work for my DB without redesigning the DB?