MySQL Bugs: #112071: Incorrect row size checking when writing an ondisk temptable during cursor fetch

Bug #112071	Incorrect row size checking when writing an ondisk temptable during cursor fetch
Submitted:	15 Aug 2023 8:56	Modified:	16 Aug 2023 15:11
Reporter:	Wen He (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cursor, storage engine, temp table

Description:
When executing a select statement, error 1118 occurs. The detailed error meassage is as follows. Actually, the row size is only about 2000 bytes.

ERROR: 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. (code 1118)

How to repeat:
# Download the attached test file into mysql-test/suite/x/t and execute the following command.
./mtr x.cursor_fetch_bug.test --record

Test for a bug of cursor fetch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: cursor_fetch_bug.test (application/octet-stream, text), 2.63 KiB.

Hi Mr. He,

Thank you very much for your bug report.

However, it is not a bug.

The total size of your row is exactly :

8832 bytes .....

Hence, the error is correct.

Consider converting your CHARs to VARCHARs.

Not a bug.

If the total size of your row is exactly 8832 bytes, Why does the insertion succeed?

Hi,

The answers are very simple and actually quite obvious.

Your INSERT succeeds since you have not filled up the CHAR column to the brim.

Your SELECT fails due to the incredibly small value of the tmp_table_size.

Hence, this is your own bug and not MySQL bug.

HI,

Needless to say, if that system variable is not set to the incredibly low value, everything works just fine.

An incredibly small value of the tmp_table_size is to make the example simple. When the memory usage reaches the tmp_table_size limit, MySQL automatically converts the in-memory temporary table to an on-disk table. The newly cerated on-disk temporary table uses the InnoDB storage engine. When writing rows into this table, the row size checking compares the maximum possible record length with 8126. However, when inserting rows into a normal InnoDB table, the row size checking compares the record length of the record that is been inserting with 8126. The two cases both use InnoDB storage engine but have different row size checking logic. Thus, a same record will have different storage formats requiring  different amount of disk space in the two cases. The MySQL 8.0 Reference Manual doesn't tell much about it. https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html#:~:text=Internal%20...

Hi,

We would like to remind you that we are writing about the Reference Manual and not the Users Manual.

We do not have nor do we  maintain Users Manual. However, there is a number of MySQL Users Manuals on the market.