0

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

  • Sounds like one table was on the verge of hitting the ~8KB limit?? – Rick James Nov 9 '17 at 18:59
  • TIMESTAMP is the only column that I can think of that grew in size (4 bytes to 5). – Rick James Nov 9 '17 at 19:18
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.)

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

  • @RickJames i mean MyISAM instead of InnoDB i just edited my answer thank you for pointed out – Mikael HOUNDEGNON Nov 9 '17 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. – smchae Nov 10 '17 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.