Bug #77398 row size too large in mysql 5.7 query
Submitted: 18 Jun 2015 8:01 Modified: 30 Nov 2023 6:16
Reporter: Andrea Manenti Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.7.7 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: 5.7, innodb, query

[18 Jun 2015 8:01] Andrea Manenti
Description:
With Mysql 5.7 I can't execute some heavy queries because i get the error:

ERROR 1118 (42000) at line 2226: 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.

In the log file i have something like that:

2015-06-17T13:55:40.754201Z 2 [ERROR] InnoDB: Cannot add field `FVR_cms_codice_338` in table `temp`.`#sql6f0_2_8` because after adding it, the row size is 8138 which is greater than maximum allowed size (8126) for a record on index leaf page.

- The same queries work fine since mysql 5.1
- i've tried using barracuda and compression on tables involved but nothing has changed.
- i've changed the size of the ibtmp1 but nothing has changed.

How to repeat:
make a very big select in a query, with a lot of varchar and text fields. 
use 'order by' with a lot of fields.
[18 Jun 2015 10:59] MySQL Verification Team
Thank you for the bug report. Duplicate of http://bugs.mysql.com/bug.php?id=69336.
[15 Jun 2016 12:22] MySQL Verification Team
Try set internal_tmp_disk_storage_engine=MyISAM  in my.cnf and check if it works?
[15 Jun 2016 12:35] Vitalijs Gaicuks
This solution: internal_tmp_disk_storage_engine=MyISAM solved the problem.
[15 Jun 2016 15:49] MySQL Verification Team
verified with attached testcase.
[15 Jun 2016 15:49] MySQL Verification Team
testcase

Attachment: bug77398.sql (application/octet-stream, text), 2.13 KiB.

[15 Jun 2016 16:12] MySQL Verification Team
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_internal_tmp_di...

"Under internal_tmp_disk_storage_engine=INNODB, queries that generate temporary tables that exceed InnoDB row or column limits will return Row size too large or Too many columns errors. The workaround is to set internal_tmp_disk_storage_engine to MYISAM."
[20 Aug 2016 20:50] Rick James
Since Oracle's goal is to get rid of MyISAM, the "workaround" for this problem is not a good long term answer.  Please rethink how the bug can be solved within InnoDB.
[4 Jan 2018 16:24] Daniel Silva
Having the same problem with a create table.

Attachment: innbodb_error.sql (application/octet-stream, text), 9.14 KiB.

[26 Jul 2018 18:11] Murray Williams
We have hit this issue. Please can you advise whether there is any plan to resolve it and what the migration path is for MySQL 8?
[29 Apr 2019 14:01] Mohit Mhetre
We also facing similar issue with MYSQL version 5.7.26.

Can any one please let me know, What is the plan to resolve this issue and Will there be fix of this issue available in future release?
[25 Jan 10:44] Nimita Joshi
The following work around can be used to resolve the issue:

The error in this scenario is due to the InnoDB engine row size limitation.
So instead of this we can use memory mapped files.

When using TempTable, we can set it to overflow to memory mapped files
to have virtually unlimited storage for internal temporary tables.
According to chapter “Internal Temporary Table Storage Engine”
(https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html)
it is possible to use the variable temptable_max_mmap, to control how much
space can be used by such tables. temptable_max_mmap defines the
maximum amount of memory the TempTable storage engine is permitted
to allocate from memory-mapped files before MySQL starts using Innodb
on-disk internal temporary tables.
The default values of temptable_max_mmap,temptable_max_ram are 1GB and
for tmp_table_size is 16MB.

Below is the order in which the engines are used:
1. It starts with HEAP memory, on overflow
2. Moves to memory mapped files if configuration permits, on overflow
3. Moves to InnoDB engine [This has the row size design limit]
Hence if the temp tables does not overflow to step 3, it will never encounter
the design limit and the query will work fine.

The following three variables can be used:
tmp_table_size : Controls the total size of temp tables.
temptable_max_ram : Sets the max size of temp table before moving out of Heap.
temptable_max_mmap : Max size of memory mapped files for temp table use
before moving to InnoDB engine.

So if a table consists of a large number of rows you can further increase
the values of tmp_table_size and temptable_max_mmap and the queries will
work as expected.