Description:
As reported on StackOverflow here: https://stackoverflow.com/questions/67242726/mysql-8-order-by-created-at-out-of-sort-memor...
I've just recently upgraded to MySQL 8 and suddenly I'm getting this "Out of Sort Memory" error when sorting by a non-JSON field in a table with 2 JSON fields.
The error: SQL Error (1038): Out of sort memory, consider increasing server sort buffer size
My sort_buffer_size is set to 1M, which was always fine before upgrading to version 8.
The column I tried sorting by ("created_at") is a non-indexed timestamp.
After reading up a bit I found this release note on a change to the optimizer:
"One effect of this enhancement is that it is now possible for Out of memory errors to occur when trying to sort rows containing very large (multi-megabtye) JSON or GEOMETRY column values if the sort buffers are of insufficient size; this can be compensated for in the usual fashion by increasing the value of the sort_buffer_size system variable."
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html
But in my case the largest JSON value is far less than 1MB, so I understand that my case should not fall in this situation.
If I add an index to the `created_at` field, then I'm able to sort by it. But I don't think I should have to add indexes to every field in the table (except the JSON fields) in order to be able to sort by any of them whenever I wish. I generally only add indexes to fields that are frequently searched/sorted.
How to repeat:
I'm happy to send a dump of the table that is having the problems (I don't see a way to attach it to this report).
From there you would just import it and run a query such as:
SELECT * FROM mytable order by created_at;
Using the latest version of MySQL at the time of this writing: 8.0.24
Suggested fix:
Rick James recommends the following:
This error seems to be caused by an "improvement" in the Optimizer. The "right" way to deal with the error is to do one of these:
* Use statistics on the table to conservatively estimate that the JSON (etc) could be too big to avoid the error, then use the old sorting method. Or
* Catch the "out of memory" and revert to the old way.
One reverts unnecessarily in some cases (where the stats are not precise enough); the other has to undo some work (making it slower).
He mentions this with more detail here:
https://bugs.mysql.com/bug.php?id=103225
https://stackoverflow.com/questions/67242726/mysql-8-order-by-created-at-out-of-sort-memor...
