String 4000 (MEDIUMTEXT) fields occupying a lot of space in a SQL table row

[anko]

I have created a table with 15 string fields of length 4000 (SQL type has become MEDIUMTEXT, as expected). As long as no more than 10 of the string fields contain large amounts of data, all goes well, to a certain extend even regardless of how much data is in each of the fields. As soon as 11 or more fields contain large amounts of data (> 750 chars each) , I get below error message:

java.sql.BatchUpdateException: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

From what I've read so far, it seems that, next to a pointer, the first (up to) 768 bytes of each MEDIUMTEXT are stored in the record itself, causing the record to reach its max size of 8126 bytes total fairly quickly.

How can I either prevent the first 768 bytes of each MEDIUMTEXT field from being stored in the record or increase to max row size?

Suggestions are highly appreciated. Regards,

Anko

[chrishenson]

Hello Anko,

It looks like you'd be better off creating two tables, one with a text field in and one parent which the text field records reference.

That way you'd be able to have as many 4k+ text fields as you like as part of the container record without hitting the record size limit.

Regards,

Chris Henson

[anko]

Thanks Chris. That would be something to consider, but the problem is that my table is an import / staging table. Do you think I would be able to import data into such a structure?

[chrishenson]

So the issue is actually getting the data into Service Now in the first place before performing any transform.

As far as I'm aware we wouldn't modify the max db row size so it may be a case of modifying the structure of your import data or looking at other ways to import the data.

[anko]

Although I did not achieve what I initially wanted to achieve (prevent the first 768 bytes of each MEDIUMTEXT field from being stored in the record or increase to max row size), I found out it is not really an issue for me, because, in real live, I will not encounter records where so many of the MEDIUMTEXT fields are populated that I will exceed the max row size.