1

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

Improve this question
2
  • Sounds like one table was on the verge of hitting the ~8KB limit?? Commented Nov 9, 2017 at 18:59
  • TIMESTAMP is the only column that I can think of that grew in size (4 bytes to 5). Commented Nov 9, 2017 at 19:18

2 Answers 2

Reset to default
2

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.)

Improve this answer
0

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

Improve this answer
2
  • @RickJames i mean MyISAM instead of InnoDB i just edited my answer thank you for pointed out Commented Nov 9, 2017 at 19:19
  • I have already tried solution1, but it does not help. And strange thing is that even though I add innodb_file_format = Barracuda on ect/mysql/mysql.conf.d/mysqld.cnf`` on ubuntu, when I run SELECT * FROM information_schema.INNODB_SYS_TABLES;, the result still shows some tables still have Antelope` format. So I had to change involving tables individually. Commented Nov 10, 2017 at 0:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.