MySQL Bugs: #77398: row size too large in mysql 5.7 query

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

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.

Thank you for the bug report. Duplicate of http://bugs.mysql.com/bug.php?id=69336.

Try set internal_tmp_disk_storage_engine=MyISAM  in my.cnf and check if it works?

This solution: internal_tmp_disk_storage_engine=MyISAM solved the problem.

verified with attached testcase.

testcase

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

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

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.

Having the same problem with a create table.

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

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?

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?

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.