MySQL - Barracuda file format & row compression to avoid ERROR 1118 Row Size Too Large

1.1k views Asked by At

I am attempting to get row compression working with mysql 5.5.31 (community edition) to avoid getting the following error when denormalizing in a warehousing-type context.

ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. You have to change some columns
to TEXT or BLOBs

I have enabled the various system variables as follows:

mysql> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

But when I attempt to do the following as a test, I still get the same row size too large errors.

create table foo (  first varchar(10000),
    second varchar(10000),
    third varchar(10000),
    fourth varchar(10000),
    fifth varchar(10000),
    sixth varchar(10000),
    seventh varchar(10000)
 ) ENGINE=InnoDB ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8;

Does anyone know what I might be missing here? There are several sources that indicate this should work.

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

Change limit for "Mysql Row size too large"

Django-MySQL enable Row_Format=Compress with syncdb

1

There are 1 answers

0
snowdragon On

https://dba.stackexchange.com/a/22463 - this suggests the combined row length still has a limit even under Barracuda.

Try changing varhcar(x) to text or blob instead, it worked fine in my tests:

create table foo_text (  
    first longtext,
    second longtext,
    third longtext,
    fourth longtext,
    fifth longtext,
    sixth longtext,
    seventh longtext
) ENGINE=InnoDB ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8;