MYSQL - How to workaround the row size limit of 66 KBytes

Question

I have a mysql db. I use innodb. 0ne of my tables contains a little more than 10 columns. The last column has a type of LONGTEXT and it is supposed to contain html code. The problem is, for each record, that field does not conatin the full code and it stops always after the same amount of characters. The weight of the html files I try to insert is around 60KO. So I guess each of my record exceeds the row size limit of mysql (66KO). What I would like to know is if there are some ways to extand that limit. Any workaround would be much appreciated. Thanks in advance for the inputs. Cheers. Marc

Answer 1

The accepted answer is wrong (or at least pretty opinionated) - I don't personally want data stored outside of my database, as it creates complications in terms of backup procedures and transactional queries.

As others have pointed out, the manual repeatedly states that BLOB and TEXT columns do not count towards the total row-size, but unfortunately, with the default configuration settings, that's not true, and you end up getting this error-message. (The error-message doesn't make any sense, because it's telling you to use TEXT instead of VARCHAR to address the issue - which you already are.)

The reason for this limitation is the default storage mechanism, Antelope, which stores the first 768 bytes of variable-length columns in the row - and a possible solution is to use INNODB and switch your storage mechanism to the alternative Barracuda storage mechanism:

SET GLOBAL innodb_file_format=Barracuda;

This will have no immediate effect, because this setting is a default for new database files - so you will need to drop and recreate your entire database.

Alternatively, switch to Barracuda (as above) and then (in addition) switch to the file-per-table strategy:

SET GLOBAL innodb_file_per_table=ON;

Again, this will have no immediate effect, because both settings are defaults for new tables - so again, you will need to drop and recreate the table.

If you look in the MySQL data folder after doing this, you can confirm that separate files were created, e.g. for a database named "data", and a table named "test", you should see a file named "data/test/bigtable.ibd".

If you dislike changing the global settings in MySQL, try SET SESSION instead of SET GLOBAL, e.g. immediately before running your CREATE TABLE statements.