MySQL Row size too large (> 8126)

Question

After I upgraded MySQL from 5.6 to 5.7 on Ubuntu 14.04, I started to have this problem on certain action. As I understand, the problem is that I am trying to get row that is too big from the table. So, I managed to solve this problem by limiting columns on select part which was originally using select *.

However, I want to solve this problem by changing the setting. (Because I might be able to face a situation where many columns for select are required)

I have tried to change

innodb_strict_mode = 0 max_allowed_packet = 512M innodb_log_file_size = 128M innodb_log_buffer_size = 256M innodb_file_per_table = ON

all these values but without any luck.

Any advice or suggestion would be appreciated. Thank you

Answer 1

Plan A: (see answer by @hmikael)

Plan B: Change innodb_page_size to 32K. However, this is a non-trivial task since it applies to all InnoDB tables. Doing so would change the limit to ~16KB.

(Note: going to page size of 64KB won't get past 16KB limit per row.)

Plan C: Switch to MyISAM. This is not advised for various reasons.

Plan D: build a "parallel table" or "vertically partition". That is, split off some of the columns into another table. Suggest moving rarely-used and/or bulky columns into the new table. Both tables would have the same PRIMARY KEY (though at most one would say AUTO_INCREMENT).

Plan E: Shrink datatypes where practical. That is, don't blindly use VARCHAR(255). (This will also help with performance in a few situations.) Don't use a 4-byte INT for a true/false flag; use a 1-byte TINYINT. Etc.

Plan F: Normalize common strings. (A 3-byte MEDIUMINT UNSIGNED is usually a lot smaller than a string.)

Plan G: Don't splay an array across columns.

(Some "Plans" can be used together.)

Answer 2

Solution 1: Add the following to the my.cnf file under [mysqld] section.

innodb_file_per_table=1
innodb_file_format = Barracuda

ALTER the table to use ROW_FORMAT=COMPRESSED.

ALTER TABLE nombre_tabla
    ENGINE=InnoDB
    ROW_FORMAT=COMPRESSED 
    KEY_BLOCK_SIZE=8;

solution 2 : changed the table storage engine to MyISAM

more details here : https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large