I have a table which has 300 columns in it. Each column is up to 255 bytes (there is a business justification for this).
When I try it create it using VARCHAR(255)
, I go past the limit for the maximum number of bytes. So I create is using 300 TEXT
fields. When I then try and insert data, I get the error:
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.
After reading up on this, I tried to change the table to use Barracuda format by specifying ROW_FORMAT=COMPRESSED
. The issue now seems to be that when I try and create the table using that format, I get the same error.
CREATE TABLE T_ObjLarge__c (Id VARCHAR(18), Name VARCHAR(80),
ObjLarge_Field_1__c TEXT,
ObjLarge_Field_2__c TEXT,
...
ObjLarge_Field_300__c TEXT
) ROW_FORMAT=COMPRESSED ;
The error I get is:
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 am using MySQL 5.5.31 on Linux mint. No indexes. I have tried DYNAMIC
format; it behaves the same way.
Output of SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
:
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+