MySQL 8K row limitation

kklosson

I have a BO that may occasionally exceed the 8k row limit in MySQL. After a discussion with ChatGPT, it seems there is no way around this (i.e., using different data types). I previously thought that the db engine stored larger text data types in a different space in the engine, but apparently not. I have a plan to alleviate this but just wondering if I understand everything correctly.

Also, the BO has an attribute to hold up to 23k characters. When I fill it with over 8,000 characters (as counted by MS Word) is saves without error. Please tell me what it is that I do not understand. In the DB, it's mediumtext data type.

johntalbott

Looks like the off-page storage data types will work for you.

On-Page Storage:
For columns of type TINYTEXT, TEXT, VARCHAR, and BLOB, if the data is small enough to fit within the row's maximum size limit (approximately 8 KB for InnoDB), it is stored directly in the row.

Off-Page Storage:
For MEDIUMTEXT and LONGTEXT columns, the actual data is stored outside the main row data, and only a pointer to the data is stored in the row. This means that the content of these columns is not part of the row's in-memory data structure but is instead stored in the tablespace.

kklosson

Very interesting. I suspect I may be experiencing a false error due to erroneous hidden characters in some text. Thank you for the clarification. You are officially smarter than ChatGPT.

aware_support

We have come across this problem too. This is what worked for us:

Add this line to mysql.ini (which is located in c:\Progarm Data\MySQL
Program Data is a hidden folder)

innodb_strict_mode=0