Bug #103318 Out of sort memory, consider increasing server sort buffer size for JSON Columns
Submitted: 14 Apr 13:46 Modified: 17 May 11:00
Reporter: Snehal Bhavsar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version: 8.0 OS:Any
Assigned to: CPU Architecture:Any

[14 Apr 13:46] Snehal Bhavsar
Description:
While using JSON datatype in MySQL 8.0.22 with sort buffer size of 256M, mysql is giving the following error.

Error: Out of sort memory, consider increasing server sort buffer size

mysql> show variables like 'sort_buffer_size';
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| sort_buffer_size | 256000000 |
+------------------+-----------+
1 row in set (0.00 sec)

Please suggest the fix for the issue.

How to repeat:
1. Create table having JSON columns (In my case I am having many TEXT and JSON columns in the same table)
2. Execute select statement with order by clause with any column other than one in the primary key.
3. Select Query will not be executed and will end up with this Error.

Error: Out of sort memory, consider increasing server sort buffer size

Suggested fix:
Once we have changed the JSON columns to TEXT the queries executed successfully, but this may be a temporary workaround. In our case we want to have columns with JSON datatype. 

However increasing sort_buffer_size is also not the recommended practice.
[14 Apr 14:02] Snehal Bhavsar
This is the case specifically happening with JSON columns.
[14 Apr 15:13] MySQL Verification Team
Hi Mr. Bhavsar,

Thank you for your bug report.

First of all, sort_buffer_size of 256 Mb is way too high, since this is allocated for each thread that requires it..

Second, we need to repeat the behaviour.

Hence, we need all the tables involved in that query, plus the query itself.

Only when we repeat it, can we further process your report.

We are waiting on your feedback.
[15 Apr 8:27] Snehal Bhavsar
This is the table structure:

CREATE TABLE `VIEW_INFO_TEST` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `CREATOR_FK` int DEFAULT NULL,
  `CREATOR_NAME` varchar(100) DEFAULT NULL ,
  `CREATED_TIME` datetime DEFAULT CURRENT_TIMESTAMP ,
  `MODIFIER_FK` int DEFAULT NULL COMMENT ,
  `MODIFIER_NAME` varchar(100) DEFAULT NULL ,
  `MODIFIED_TIME` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  `NAME` varchar(200) NOT NULL ,
  `DISPLAY_NAME` varchar(100) DEFAULT NULL ,
  `VIEW_JSON` json DEFAULT NULL ,
  `TABLET_JSON` json DEFAULT NULL ,
  `RESOURCE_LIST` varchar(1000) DEFAULT NULL ,
  `CUSTOM_MESSAGE` varchar(100) DEFAULT NULL ,
  `VIEW_TYPE` varchar(50) NOT NULL ,
  `PLATFORM` varchar(50) NOT NULL ,
  `FORM_RESOURCE` varchar(1000) DEFAULT NULL ,
  `CHECKLIST_ID` varchar(500) DEFAULT NULL ,
  `CHECKLIST_NAMES` varchar(2000) DEFAULT NULL ,
  `STATUS` varchar(20) NOT NULL ,
  `APPLICATION_FK` int NOT NULL ,
  `REFERENCE_ID` varchar(50) NOT NULL ,
  `DELETED` bit(1) DEFAULT b'0' ,
  `FORM_PROPERTY` json ,
  `TOTAL_FIELD` int DEFAULT NULL,
  `DESCRIPTION` text ,
  `OPEN_DIALOG` bit(1) DEFAULT b'0' ,
  `WORKGRP_NAME` varchar(2000) DEFAULT NULL ,
  `VIEW_ADDITIONAL_INFO_FK` int DEFAULT NULL ,
  `CUSTOM_VIEW_FK` int DEFAULT NULL ,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `view_builder_ibuk_3` (`REFERENCE_ID`),
  UNIQUE KEY `view_builder_ibuk_1` (`NAME`),
  KEY `view_builder_ibfk_1` (`RCP_APPLICATION_FK`),
  KEY `Idx_MODIFIED_TIME` (`MODIFIED_TIME`),
  KEY `VIEW_INFO_VIEW_ADDITIONAL_INFO_FK` (`VIEW_ADDITIONAL_INFO_FK`),
  KEY `VIEW_INFO_CUSTOM_VIEW_FK` (`CUSTOM_VIEW_FK`)
) ENGINE=InnoDB AUTO_INCREMENT=3011 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

=================================================
-- With Default size of sort_buffer_size:

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM VIEW_INFO_TEST order by MODIFIED_TIME DESC;
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

=================================================
-- With 1MB of sort_buffer_size:

mysql> show variables like 'sort_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 1621442 |
+------------------+---------+

mysql> SELECT * FROM VIEW_INFO_TEST order by MODIFIED_TIME DESC;
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

===============================================================
-- You can check the max length of data on each of the columns:

*************************** 1. row ***************************
           max(length(CHECKLIST_ID)): 177
        max(length(CHECKLIST_NAMES)): 549
           max(length(CREATED_TIME)): 19
             max(length(CREATOR_FK)): 5
           max(length(CREATOR_NAME)): 36
         max(length(CUSTOM_MESSAGE)): NULL
         max(length(CUSTOM_VIEW_FK)): 1
                max(length(DELETED)): 1
            max(length(DESCRIPTION)): 6142
           max(length(DISPLAY_NAME)): 168
          max(length(FORM_PROPERTY)): 11223
          max(length(FORM_RESOURCE)): 295
                     max(length(ID)): 4
          max(length(MODIFIED_TIME)): 19
            max(length(MODIFIER_FK)): 5
          max(length(MODIFIER_NAME)): 36
                   max(length(NAME)): 133
            max(length(OPEN_DIALOG)): 1
               max(length(PLATFORM)): 4
     max(length(RCP_APPLICATION_FK)): 1
           max(length(REFERENCE_ID)): 9
          max(length(RESOURCE_LIST)): 270
                 max(length(STATUS)): 8
            max(length(TABLET_JSON)): 4630
            max(length(TOTAL_FIELD)): 3
max(length(VIEW_ADDITIONAL_INFO_FK)): 3
              max(length(VIEW_JSON)): 1356813
              max(length(VIEW_TYPE)): 7
         max(length(WORKGRP_NAME))  : 33

So, in this case even 1 MB of sort_buffer_size is insufficient, so we tried to increase the sort_buffer_size to 256MB, then it worked properly,but this values is too high and not the good practice. Also by converting these json columns to text solved our problem, but again we do not want to do this and want to keep the column in json datatype only.

Kindly suggest what is the other solution for this issue.
[15 Apr 12:26] MySQL Verification Team
Hi Mr. Bhavsar,

We need a fully reproducible test case.

That implies all tables involved. Each table should come with its CREATE TABLE statement and all of its rows. Last, we need the query that produces that error.

We do not need all rows from each table, but we need as many as it is necessary for the error message to be reproduced.

Let us know when you have the proper test case for us.

We do hope that we were more clear now.
[19 Apr 10:15] Snehal Bhavsar
Hello! 

I have already shared all the details. We are only having single table and we are executing select query on the same single table. There is no other tables involved neither the Query is having join with another table. This is the exact case which I have shared through which we are able to reproduce this ERROR.
[19 Apr 12:48] MySQL Verification Team
Hi Mr. Bhavsar,

I will repeat what we wrote once already ....... This time a bit more explicit ......

What we need is the entire table, which means  its CREATE TABLE statement and all of its rows !!!!!!

We do not need all rows from each table, but we need as many as it is necessary for the error message to be reproduced.

So far, we have not received contents of that table ......

Since we are waiting on data from you, you can also send us the detailed EXPLAIN of that query. Also, let us know whether you have changed defaults for memory and permanent temporary tables.

We are waiting on your feedback.
[24 Apr 18:21] Rick James
Dup of https://bugs.mysql.com/bug.php?id=103225 ?
[26 Apr 13:32] MySQL Verification Team
Hi Mr. Bhavsar,

We need a full name of your table dump file.
[27 Apr 12:50] MySQL Verification Team
Hi Mr. Bhavsar,

We managed to repeat your behaviour.

Verified as reported.
[29 Apr 8:39] Steinar Gunderson
A correction here: 256 MB sort buffer is not only completely allowed, but
actively encouraged if you have the RAM (assuming you are on 8.0). Please
disregard earlier messages to the contrary.
[17 May 11:00] Snehal Bhavsar
Hello MySQL Team,

Any update on this?
[17 May 14:22] MySQL Verification Team
No, not yet ......