Row size too large (> 8126) - 5.7.mysql_aurora.2.10.0

Question

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?

Answer 1

The computation of when the column will overflow is complex. 250 TINYINTs (1 byte + overhead) would easily fit, but a TEXT, BLOB, and certain other datatypes will take a minimum of 20 bytes; some cases 40 bytes. Hence, the unexpected overflow.

I recommend doing one or more of the following:

• Shrink the datatypes. (This is not applicable to your "table1".) Some programmers blindly use BIGINT and DOUBLE for numeric values. These take 8 bytes each; there are smaller datatypes that my suffice.
• Do not have an "array" spread across columns.
• "Vertically" split the table, moving some columns to another table. Then use the same PRIMARY KEY for both tables. (If that is AUTO_INCREMENT, then it applies only to the main table.)
• As above, but move sets of columns that are often NULL. Then you can simply leave out the row in the new table. (Use LEFT JOIN when putting them back together.)
• Combine some columns together in some way, perhaps via JSON. This will decrease the number of columns, hence the per-column overhead. And the JSON will use a minimum of 20 (or whatever), which is less than 20 * the number of columns it replaces.

If you provide the current SHOW CREATE TABLE with the actual column names, I may have more tips.

re Blobs

If those 195 Blobs are an array of things, change to have a table with up to 195 rows for each id.

If those blobs are not an "array", you still need to break some of them out into a separate table.

If some of those blobs can be really VARBINARY(...) with some small limit, that will help some.

Even if you were to get past the 8K limit, you would probably hit some other limit.