MySQL 8 order by created_at out of sort memory error 1038

Question

I just recently upgrade from MySQL 5.7 to MySQL 8.0.3. The update process seems to have gone well. No significant problems noted in mysqld.log. I was able to connect fine to my database.

But when I try to perform a simple query like this:

SELECT * FROM mytable ORDER BY `created_at` DESC LIMIT 1000;

I get the following error:

SQL Error (1038): Out of sort memory, consider increasing server sort buffer size

I've checked in my.ini and I have sort_buffer_size=1M, the same as I had in MySQL 5.7.

The created_at column is a nullable timestamp with no index. The table is small, <90 rows. I never had this problem with MySQL 5.7, not even with tables with hundreds of thousands of rows.

What could be wrong?

The EXPLAIN of the query:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	mytable	\N	ALL	\N	\N	\N	\N	31	100.00	Using filesort

I noticed the row count is way below the actual number of rows in the table. I tried OPTIMIZE TABLE and now the EXPLAIN shows 43 rows, still about half of the real number.

I did a dump of the database before upgrading MySQL so I tried dropping and restoring that table from the dump in case somehow it was corrupted. No change.

What else should I do in this case?

Here's my CREATE TABLE:

CREATE TABLE `mytable` (
    `id` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `relation_id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `request_data` JSON NOT NULL,
    `response_data` JSON NULL DEFAULT NULL,
    `filename` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `remote_key` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `relation_id` (`relation_id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

As I mentioned in the comments, I don't think this is an issue of adding an index to created_at as I have tables with hundreds of thousands of rows that sort just fine without that index.

Could it have something to do with the 2 JSON columns? Some of those responses I save have lots of data.

More info:

The size of the largest JSON field is 430 kb.

SHOW TABLE STATUS LIKE 'mytable':

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
mytable	InnoDB	10	Dynamic	44	131444	5783552	0	0	4194304	\N	2021-04-24 15:21:00	2021-04-24 14:33:36	\N	utf8mb4_unicode_ci	\N

EXPLAIN FORMAT=JSON SELECT * FROM mytable ORDER BY created_at DESC LIMIT 1000;:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "360.21"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "43.00"
      },
      "table": {
        "table_name": "mytable",
        "access_type": "ALL",
        "rows_examined_per_scan": 43,
        "rows_produced_per_join": 43,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "312.91",
          "eval_cost": "4.30",
          "prefix_cost": "317.21",
          "data_read_per_join": "173K"
        },
        "used_columns": [
          "id",
          "relation_id",
          "request_data",
          "response_data",
          "filename",
          "remote_key",
          "created_at",
          "updated_at"
        ]
      }
    }
  }
}

MySQL 8 order by created_at out of sort memory error 1038

0 Answers