MySQL Bugs: #115848: out of sort memory

Bug #115848	out of sort memory
Submitted:	16 Aug 3:17	Modified:	16 Aug 10:00
Reporter:	wei sun	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.31	OS:	Red Hat
Assigned to:		CPU Architecture:	Any

Description:
I am using mysql-5.7.31 Community Edition, and I have created an empty table for testing purposes. The table creation statement is:
“CREATE TABLE `t5` (
`id` int(11) DEFAULT NULL,
`name` varchar(510) NOT NULL,
`age` int(11) DEFAULT NULL,
`name2` varchar(510) NOT NULL,
UNIQUE KEY `name1` (`name`,`name2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;”
Then when I execute the query statement 'select * from t5 order by id;', an error will be reported: 'ERROR 1038 (HY001): Out of sort memory', consider increasing server sort buffer size”
The value of 'sort_fuffersize' is as follows:
show variables like 'sort_buffer_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sort_buffer_size | 32768 |
+------------------+-------+
May I ask, in sorting operations, if the 'sort_fuffer' is not large enough, a temporary file should be used instead? What causes an error? And I also conducted another test. If the index in the table is not a unique index, but a regular index, there will be no error. If there is no index, there will be no error. Only when it is a unique index will there be an error. Why is this?

How to repeat:
CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(510) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name2` varchar(510) NOT NULL,
  UNIQUE KEY `name1` (`name`,`name2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

show variables like 'sort_buffer_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sort_buffer_size | 32768 |
+------------------+-------+

select * from t5 order by id;
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

Hi Mr. sun,

Thank you for your bug report.

However , version 5.7 is not supported any more.

We could not repeat your test case on our official 8.0.39 binary:

CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(510) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name2` varchar(510) NOT NULL,
  UNIQUE KEY `name1` (`name`,`name2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select * from t5 order by id;
Empty set (0.00 sec)

Also, do not set sort buffer size to such a low value as all your queries will fail. Use defaults.

Unsupported.

Thanks

You are truly welcome.