MySQL Bugs: #103318: Out of sort memory, consider increasing server sort buffer size for JSON Columns

Bug #103318	Out of sort memory, consider increasing server sort buffer size for JSON Columns
Submitted:	14 Apr 2021 13:46	Modified:	17 May 2021 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

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.

This is the case specifically happening with JSON columns.

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.

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.

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.

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.

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.

Dup of https://bugs.mysql.com/bug.php?id=103225 ?

Hi Mr. Bhavsar,

We need a full name of your table dump file.

Hi Mr. Bhavsar,

We managed to repeat your behaviour.

Verified as reported.

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.

Hello MySQL Team,

Any update on this?

No, not yet ......

I had the same issue, I've founded that using MySQL 8.0.19 works fine, after upgrade to any 8.0.20+ (even 8.0.40) version have the problem.
If I remove the json columns from the selected fields it works, but I can't order for any other than PK

Thank you for the info, Mr. Spadea.