3

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.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
thedevd
  • 683
  • 11
  • 26

2 Answers2

2

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.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
  • almost correct, there are two "positions" in the topicstarters table structure a `row` and `column` position. You didn't maintain the "row" position data.. – Raymond Nijland Dec 17 '18 at 12:15
  • @RaymondNijland I don't follow. Each piece of data in the OP's table has: an ID (verbatim), a column number (inherent in the field name `colX`) and a value (value stored in field `colX`). I'm suggesting having three fields: the ID, the column number, and the value. What's missing? – Lightness Races in Orbit Dec 17 '18 at 12:24
  • Oh there's `status` too which should go in another table (`id, status`). – Lightness Races in Orbit Dec 17 '18 at 12:26
  • Think off all columns in the topicstarter table structure belong to a row/record..How do you tell in your table stucture the record with id 200 is the actually topicstarter record with id 1? – Raymond Nijland Dec 17 '18 at 12:28
  • @RaymondNijland I don't understand what you're saying. You still have rows. Rows are inherent to SQL databases. What is this "record 1" you speak of? The entry with id=200 is the entry with id=200. The ID is the identifying piece of information, and I haven't got rid of it. – Lightness Races in Orbit Dec 17 '18 at 12:30
  • @RaymondNijland Could you try explaining it in different words? Perhaps give a concrete example, with some sample data, which shows what information my proposed solution loses. – Lightness Races in Orbit Dec 17 '18 at 12:33
  • it's simple trust me the topicstarter record id = 1 contains 255 columns that's one record.. You define `(id (AUTO_INCREMENT), 1 (column_postion), val)` but how do you know in your structure which records are together and match the topicstarter record with id = 1.. iam not seeing it in your structure thats why i have `col_row_position` .. . – Raymond Nijland Dec 17 '18 at 13:38
  • @RaymondNijland Because that's what `id` does. The `id` represents that relation. So now you have [up to] 255 rows all with `id`=1. That's the purpose of that part of the composite key. See my example above. You've renamed `id` to `col_row_position` and added a new auto-increment `id` but I don't see any value in that. I recommend reading more about _database normalisation_ and common techniques to achieve it. [The Wikipedia article](https://en.wikipedia.org/wiki/Database_normalization) has an example almost exactly the same as this - search for _"One of Codd's important insights"_ in the text – Lightness Races in Orbit Dec 17 '18 at 13:45
  • i know how normalisation works trust me mine only problem i have is a leak of caffeine, worst night sleep, terrible headache that's why i missed with you meant with your structure from the start of the first comment and trying to discusse of problem we both want to have right and not listen to the other answer/comments and trying to "attack" one and other. i use [FCO-IM](https://en.wikipedia.org/wiki/FCO-IM) (Fully Communication Oriented Information Modeling) by the way to normalise.. And like i said only if your structure makes more sense rationally it (might) not scale with large result sets – Raymond Nijland Dec 17 '18 at 14:47
  • @RaymondNijland I have not attacked you Raymond, I politely asked you to clarify several times and explained a few things. I can assure you that this solution will scale - RDBMS are literally designed for that purpose in this scenario. We can probably move on from this now – Lightness Races in Orbit Dec 17 '18 at 14:58
  • Ok "attack" was probably the wrong word to have used here.. Besides i did explain why i did go for mine structure and even explained MySQL internals/query execution "I can assure you that this solution will scale - RDBMS are literally .. purpose in this scenario" true but not when MySQL needs to generate the resultset based on a (disk) based temporary table which "Using temporary" can indicate which can hog up the server with alot of (unnecessary) random disk i/o access which is slow. "We can probably move on from this now" Agree untill we meet again on this website in better conditions i hope – Raymond Nijland Dec 17 '18 at 15:12
0

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.

Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
  • Nujland, This seems a good idea. But at this point, I am not in a position to change the backend code as this deals with a lot of code in the application for me. But definitely I am going to try this later. – thedevd Dec 17 '18 at 12:18
  • Ok @thedevd that would more sense yes. Let me think there might be a other way. – Raymond Nijland Dec 17 '18 at 12:22
  • What do `col_row_position` and `col_position` represent? Can you give an example of how the OP's table will map to this one? – Lightness Races in Orbit Dec 17 '18 at 12:25
  • see a example @LightnessRacesinOrbit https://www.db-fiddle.com/f/6Ue6KFK1qe33836cdZJpgj/0 – Raymond Nijland Dec 17 '18 at 12:39
  • @RaymondNijland `col_row_position` is pointless there. It contains the values that should be in `id`. And now `id` is something else that just auto-increments and contains no useful information previously encoded in the OP's table. What do you intend for your `id` row to be used for? Is it possible you've misunderstood the purpose of the primary key? It's not for just blindly counting rows! It's for identifying a relation (a relation which now spans multiple rows). – Lightness Races in Orbit Dec 17 '18 at 12:44
  • See my answer for a schema that properly describes the normalised data, along with the meaningful primary key(s). This encodes exactly the same information as the OP's would if it worked, whereas yours is missing some keys and adds a new `id` field which doesn't appear to serve any purpose. – Lightness Races in Orbit Dec 17 '18 at 12:48
  • "What do you intend for your id row to be used for?" @LightnessRacesinOrbit hard to explain (let me try to find a older post on dba.stackexchange.com which is posted by me).. But the col_row_position should have a `INDEX` or be included in the `PRIMARY KEY` that is where you are right. – Raymond Nijland Dec 17 '18 at 12:51
  • "and adds a new id field which doesn't appear to serve any purpose. " Found the post [this](https://dba.stackexchange.com/questions/48072/why-does-mysql-ignore-the-index-even-on-force-for-this-order-by/48184#48184) is basicly why @LightnessRacesinOrbit – Raymond Nijland Dec 17 '18 at 12:58
  • @RaymondNijland Yes, of course you want a primary key, but you already have that in `id`. You don't need that information _and_ another, arbitrary value that doesn't provide any information. You've invented a new numeric value that does not represent any useful information nor have any relationship to the useful data, and that's an anti-pattern! – Lightness Races in Orbit Dec 17 '18 at 13:29
  • We are not going to solve this "discussion" @LightnessRacesinOrbit i figured out this structure i know it's correct and keeps the **total** topicstarter data structure.. And trust me i know how MySQL works and how indexes works. – Raymond Nijland Dec 17 '18 at 13:35
  • @RaymondNijland Okay then we will agree to disagree. Unfortunately as a result I feel I must downvote your solution though :( (for the reasons laid out above) Sorry about that. – Lightness Races in Orbit Dec 17 '18 at 13:35
  • I now see what you mean with your structure .. @LightnessRacesinOrbit But still i will defend mine answer with facts.. i would choose mine (you called it a anti-pattern) [structure and execution](https://www.db-fiddle.com/f/8CSqVsQkYQv7maXBrS9KAe/0) over [your structure and execution](https://www.db-fiddle.com/f/oY46ph5wJS8PmAidpJ7hCG/1) anny time of the week or year. – Raymond Nijland Dec 17 '18 at 14:34
  • @LightnessRacesinOrbit ... even when your approach is rational more correct but it will not scale correctly on large tables/result sets because off the "Using temporary" in your explain which can make a (disk) based temporary table when executing and can hog up en slow down the complete server.. rational correct data structures are not always a silver bullet.. – Raymond Nijland Dec 17 '18 at 14:35
  • @RaymondNijland Only because you did not add the suggested foreign key, and wrote a sub-par query. Look at [this fixed version](https://www.db-fiddle.com/f/pX7FcYNidyZPsGuAhEWTZH/0). On the other hand, yours has no ref, no index ... at scale that lookup will fail horribly. Relational databases _are designed to work well_ when given normalised data. Yours is just a flat key/value store and that's not how relational database are supposed to be used. That's not just a philosophy - their performance is tied to it. – Lightness Races in Orbit Dec 17 '18 at 14:40
  • indeed the second query does not have no ref/index @LightnessRacesinOrbit because the optimizer knows a direct table access is the fastest way because Optimizer is cost based. "Relational databases .. designed normalised data." true but not when MySQL needs to generate the resultset based on a (disk) based temporary table. "Yours is just a flat key/value store and that's not how relational database are supposed to be used" Like i said your structure makes relational more sense besides your fixed version also does not scale with [IN(1, 2)](https://www.db-fiddle.com/f/pX7FcYNidyZPsGuAhEWTZH/1) – Raymond Nijland Dec 17 '18 at 14:59