mysql 8.0.13 Row size too large ( 8126)

Question

I have read all the other same questions regarding this error. But I could not found the right solution to fix the error with MySQL server 8.0.13. Please help.

I have the following table with arround 255 column of type MEDIUMTEXT

 CREATE TABLE `guidatatable` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `col01` MEDIUMTEXT NULL,
   `col02` MEDIUMTEXT NULL,
   `col03` MEDIUMTEXT NULL,
   `col04` MEDIUMTEXT NULL,
   `col05` MEDIUMTEXT NULL,
   `col06` MEDIUMTEXT NULL,
   `col07` MEDIUMTEXT NULL,
   `col08` MEDIUMTEXT NULL,
   `col09` MEDIUMTEXT NULL,
   `col10` MEDIUMTEXT NULL,
   `col11` MEDIUMTEXT NULL,
   `col12` MEDIUMTEXT NULL,
   `col13` MEDIUMTEXT NULL,
   `col14` MEDIUMTEXT NULL,
   `col15` MEDIUMTEXT NULL,
   `col16` MEDIUMTEXT NULL,
   `col17` MEDIUMTEXT NULL,
   `col18` MEDIUMTEXT NULL,
   `col19` MEDIUMTEXT NULL,
   `col20` MEDIUMTEXT NULL,
   `col21` MEDIUMTEXT NULL,
   `col22` MEDIUMTEXT NULL,
   `col23` MEDIUMTEXT NULL,
   `col24` MEDIUMTEXT NULL,
   `col25` MEDIUMTEXT NULL,
   `col26` MEDIUMTEXT NULL,
   `col27` MEDIUMTEXT NULL,
   `col28` MEDIUMTEXT NULL,
   `col29` MEDIUMTEXT NULL,
   `col30` MEDIUMTEXT NULL,
   `col31` MEDIUMTEXT NULL,
   `col32` MEDIUMTEXT NULL,
   `col33` MEDIUMTEXT NULL,
   `col34` MEDIUMTEXT NULL,
   `col35` MEDIUMTEXT NULL,
   `col36` MEDIUMTEXT NULL,
   `col37` MEDIUMTEXT NULL,
   `col38` MEDIUMTEXT NULL,
   `col39` MEDIUMTEXT NULL,
   `col40` MEDIUMTEXT NULL,
   `col41` MEDIUMTEXT NULL,
   `col42` MEDIUMTEXT NULL,
   `col43` MEDIUMTEXT NULL,
   `col44` MEDIUMTEXT NULL,
   `col45` MEDIUMTEXT NULL,
   `col46` MEDIUMTEXT NULL,
   `col47` MEDIUMTEXT NULL,
   `col48` MEDIUMTEXT NULL,
   `col49` MEDIUMTEXT NULL,
   `col50` MEDIUMTEXT NULL,
   `col51` MEDIUMTEXT NULL,
   `col52` MEDIUMTEXT NULL,
   `col53` MEDIUMTEXT NULL,
   `col54` MEDIUMTEXT NULL,
   `col55` MEDIUMTEXT NULL,
   `col56` MEDIUMTEXT NULL,
   `col57` MEDIUMTEXT NULL,
   `col58` MEDIUMTEXT NULL,
   `col59` MEDIUMTEXT NULL,
   `col60` MEDIUMTEXT NULL,
   `col61` MEDIUMTEXT NULL,
   `col62` MEDIUMTEXT NULL,
   `col63` MEDIUMTEXT NULL,
   `col64` MEDIUMTEXT NULL,
   `col65` MEDIUMTEXT NULL,
   `col66` MEDIUMTEXT NULL,
   `col67` MEDIUMTEXT NULL,
   `col68` MEDIUMTEXT NULL,
   `col69` MEDIUMTEXT NULL,
   `col70` MEDIUMTEXT NULL,
   `col71` MEDIUMTEXT NULL,
   `col72` MEDIUMTEXT NULL,
   `col73` MEDIUMTEXT NULL,
   `col74` MEDIUMTEXT NULL,
   `col75` MEDIUMTEXT NULL,
   `col76` MEDIUMTEXT NULL,
   `col77` MEDIUMTEXT NULL,
   `col78` MEDIUMTEXT NULL,
   `col79` MEDIUMTEXT NULL,
   `col80` MEDIUMTEXT NULL,
   `col81` MEDIUMTEXT NULL,
   `col82` MEDIUMTEXT NULL,
   `col83` MEDIUMTEXT NULL,
   `col84` MEDIUMTEXT NULL,
   `col85` MEDIUMTEXT NULL,
   `col86` MEDIUMTEXT NULL,
   `col87` MEDIUMTEXT NULL,
   `col88` MEDIUMTEXT NULL,
   `col89` MEDIUMTEXT NULL,
   `col90` MEDIUMTEXT NULL,
   `col91` MEDIUMTEXT NULL,
   `col92` MEDIUMTEXT NULL,
   `col93` MEDIUMTEXT NULL,
   `col94` MEDIUMTEXT NULL,
   `col95` MEDIUMTEXT NULL,
   `col96` MEDIUMTEXT NULL,
   `col97` MEDIUMTEXT NULL,
   `col98` MEDIUMTEXT NULL,
   `col99` MEDIUMTEXT NULL,
   `col100` MEDIUMTEXT NULL,
   `col101` MEDIUMTEXT NULL,
   `col102` MEDIUMTEXT NULL,
   `col103` MEDIUMTEXT NULL,
   `col104` MEDIUMTEXT NULL,
   `col105` MEDIUMTEXT NULL,
   `col106` MEDIUMTEXT NULL,
   `col107` MEDIUMTEXT NULL,
   `col108` MEDIUMTEXT NULL,
   `col109` MEDIUMTEXT NULL,
   `col110` MEDIUMTEXT NULL,
   `col111` MEDIUMTEXT NULL,
   `col112` MEDIUMTEXT NULL,
   `col113` MEDIUMTEXT NULL,
   `col114` MEDIUMTEXT NULL,
   `col115` MEDIUMTEXT NULL,
   `col116` MEDIUMTEXT NULL,
   `col117` MEDIUMTEXT NULL,
   `col118` MEDIUMTEXT NULL,
   `col119` MEDIUMTEXT NULL,
   `col120` MEDIUMTEXT NULL,
   `col121` MEDIUMTEXT NULL,
   `col122` MEDIUMTEXT NULL,
   `col123` MEDIUMTEXT NULL,
   `col124` MEDIUMTEXT NULL,
   `col125` MEDIUMTEXT NULL,
   `col126` MEDIUMTEXT NULL,
   `col127` MEDIUMTEXT NULL,
   `col128` MEDIUMTEXT NULL,
   `col129` MEDIUMTEXT NULL,
   `col130` MEDIUMTEXT NULL,
   `col131` MEDIUMTEXT NULL,
   `col132` MEDIUMTEXT NULL,
   `col133` MEDIUMTEXT NULL,
   `col134` MEDIUMTEXT NULL,
   `col135` MEDIUMTEXT NULL,
   `col136` MEDIUMTEXT NULL,
   `col137` MEDIUMTEXT NULL,
   `col138` MEDIUMTEXT NULL,
   `col139` MEDIUMTEXT NULL,
   `col140` MEDIUMTEXT NULL,
   `col141` MEDIUMTEXT NULL,
   `col142` MEDIUMTEXT NULL,
   `col143` MEDIUMTEXT NULL,
   `col144` MEDIUMTEXT NULL,
   `col145` MEDIUMTEXT NULL,
   `col146` MEDIUMTEXT NULL,
   `col147` MEDIUMTEXT NULL,
   `col148` MEDIUMTEXT NULL,
   `col149` MEDIUMTEXT NULL,
   `col150` MEDIUMTEXT NULL,
 `col151` MEDIUMTEXT NULL,
 `col152` MEDIUMTEXT NULL,
 `col153` MEDIUMTEXT NULL,
 `col154` MEDIUMTEXT NULL,
 `col155` MEDIUMTEXT NULL,
 `col156` MEDIUMTEXT NULL,
 `col157` MEDIUMTEXT NULL,
 `col158` MEDIUMTEXT NULL,
 `col159` MEDIUMTEXT NULL,
 `col160` MEDIUMTEXT NULL,
 `col161` MEDIUMTEXT NULL,
 `col162` MEDIUMTEXT NULL,
 `col163` MEDIUMTEXT NULL,
 `col164` MEDIUMTEXT NULL,
 `col165` MEDIUMTEXT NULL,
 `col166` MEDIUMTEXT NULL,
 `col167` MEDIUMTEXT NULL,
 `col168` MEDIUMTEXT NULL,
 `col169` MEDIUMTEXT NULL,
 `col170` MEDIUMTEXT NULL,
 `col171` MEDIUMTEXT NULL,
 `col172` MEDIUMTEXT NULL,
 `col173` MEDIUMTEXT NULL,
 `col174` MEDIUMTEXT NULL,
 `col175` MEDIUMTEXT NULL,
 `col176` MEDIUMTEXT NULL,
 `col177` MEDIUMTEXT NULL,
 `col178` MEDIUMTEXT NULL,
 `col179` MEDIUMTEXT NULL,
 `col180` MEDIUMTEXT NULL,
 `col181` MEDIUMTEXT NULL,
 `col182` MEDIUMTEXT NULL,
 `col183` MEDIUMTEXT NULL,
 `col184` MEDIUMTEXT NULL,
 `col185` MEDIUMTEXT NULL,
 `col186` MEDIUMTEXT NULL,
 `col187` MEDIUMTEXT NULL,
 `col188` MEDIUMTEXT NULL,
 `col189` MEDIUMTEXT NULL,
 `col190` MEDIUMTEXT NULL,
 `col191` MEDIUMTEXT NULL,
 `col192` MEDIUMTEXT NULL,
 `col193` MEDIUMTEXT NULL,
 `col194` MEDIUMTEXT NULL,
 `col195` MEDIUMTEXT NULL,
 `col196` MEDIUMTEXT NULL,
 `col197` MEDIUMTEXT NULL,
 `col198` MEDIUMTEXT NULL,
 `col199` MEDIUMTEXT NULL,
 `col200` MEDIUMTEXT NULL,
 `col201` MEDIUMTEXT NULL,
 `col202` MEDIUMTEXT NULL,
 `col203` MEDIUMTEXT NULL,
 `col204` MEDIUMTEXT NULL,
 `col205` MEDIUMTEXT NULL,
 `col206` MEDIUMTEXT NULL,
 `col207` MEDIUMTEXT NULL,
 `col208` MEDIUMTEXT NULL,
 `col209` MEDIUMTEXT NULL,
 `col210` MEDIUMTEXT NULL,
 `col211` MEDIUMTEXT NULL,
 `col212` MEDIUMTEXT NULL,
 `col213` MEDIUMTEXT NULL,
 `col214` MEDIUMTEXT NULL,
 `col215` MEDIUMTEXT NULL,
 `col216` MEDIUMTEXT NULL,
 `col217` MEDIUMTEXT NULL,
 `col218` MEDIUMTEXT NULL,
 `col219` MEDIUMTEXT NULL,
 `col220` MEDIUMTEXT NULL,
 `col221` MEDIUMTEXT NULL,
 `col222` MEDIUMTEXT NULL,
 `col223` MEDIUMTEXT NULL,
 `col224` MEDIUMTEXT NULL,
 `col225` MEDIUMTEXT NULL,
 `col226` MEDIUMTEXT NULL,
 `col227` MEDIUMTEXT NULL,
 `col228` MEDIUMTEXT NULL,
 `col229` MEDIUMTEXT NULL,
 `col230` MEDIUMTEXT NULL,
 `col231` MEDIUMTEXT NULL,
 `col232` MEDIUMTEXT NULL,
 `col233` MEDIUMTEXT NULL,
 `col234` MEDIUMTEXT NULL,
 `col235` MEDIUMTEXT NULL,
 `col236` MEDIUMTEXT NULL,
 `col237` MEDIUMTEXT NULL,
 `col238` MEDIUMTEXT NULL,
 `col239` MEDIUMTEXT NULL,
 `col240` MEDIUMTEXT NULL,
 `col241` MEDIUMTEXT NULL,
 `col242` MEDIUMTEXT NULL,
 `col243` MEDIUMTEXT NULL,
 `col244` MEDIUMTEXT NULL,
 `col245` MEDIUMTEXT NULL,
 `col246` MEDIUMTEXT NULL,
 `col247` MEDIUMTEXT NULL,
 `col248` MEDIUMTEXT NULL,
 `col249` MEDIUMTEXT NULL,
 `col250` MEDIUMTEXT NULL,
 `col251` MEDIUMTEXT NULL,
 `col252` MEDIUMTEXT NULL,
 `col253` MEDIUMTEXT NULL,
 `col254` MEDIUMTEXT NULL,
 `col255` MEDIUMTEXT NULL,
 `status` INT NULL,
 PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

But this throws me the error -

Error Code: 1118. 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 have tried other alternative solution like tweaking the my.ini file such as increasing innodb_log_file_size to any extent like 2G, 5G, but no luck with these tweaks.

Also, I don't want to just turn off the innodb_strict_mode, as I don't think that turning off validation is the only solution. Any other solution anyone has with this type of table for MYSQL server version 8.0.13.

user_560648 · Answer

You've reached the size limit by designing a kind of silly table, sorry! When you have an "array" scheme like this, you are supposed to normalize — SQL is designed for you to do this.

You need to flatten the table that you've got. Instead of (id, val1, val2, val3, val4, ...), you want (id, 1, val), (id, 2, val), (id, 3, val), .... With this scheme, you are not going to hit the size limit, and you have a much more idiomatic schema.

You also need to allow for the status field, so we end up with something like this:

CREATE TABLE `guidatatable` (
   `id`     INT NOT NULL AUTO_INCREMENT,
   `status` INT NULL,

   PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `guidatatable_values` (
   `id`       INT NOT NULL,
   `colNum`   INT NOT NULL,
   `colValue` MEDIUMTEXT NOT NULL,

   PRIMARY KEY (`id`, `colNum`)
) ENGINE=InnoDB;

And be sure to make guidatatable_values.id a foreign key referring to guidatatable.id (I forget the best syntax for this off-hand).

Concrete example of the data's transformation; this:

id  col01   col02   col03   status
 1  Hello   World   !         42
 2  How     Are     You       99
 3  I       Am      Fine      168

Becomes this:

 id   status
  1   42
  2   99
  3   168

 id   colNum   colValue
  1   1        Hello
  1   2        World
  1   3        !
  2   1        How
  2   2        Are
  2   3        You
  3   1        I
  3   2        Am
  3   3        Fine

As you can see, all semantic and relational information stored in your table remains intact; it's just been reorganised.

In general, once you hit the maximum table size, you know that your table is too big and can probably be better designed. This can particularly happen in large applications where legacy requirements have resulted in a table growing and growing and growing … again, this is usually an example of failing to adequately design new features.

user_2548147 · Answer

MySQL has a max row size.
So probably your best option is to normalize the table data into the following.

 CREATE TABLE guidatatable (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
  , col_row_position INT UNSIGNED NOT NULL 
  , col_text MEDIUMTEXT NULL
  , col_position INT UNSIGNED NOT NULL 
  , guidatatable_status VARCHAR(255)
  , KEY(col_row_position, col_position)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This also handles the max packet size which MySQL has better.

mysql 8.0.13 Row size too large (> 8126)

2 Answers