I have two different mysql servers one is 8.0.16 community version and the other is 8.0.27 commercial version. Both has mysql default configuration running on Linux.
I have table called jsonstore which contain 34 column and two of which are jsons and total count of rows in table is 9. I have copied the same table in both servers.
Select json1 from jsonstore where reference = 7 order by generated_date desc limit 1;
Where reference is an int(11) indexed column ,generated_date is datetime column and json1 is json column and jsonstore is innodb table
Above query return 1 json record approx size of json is 1.5MB
Same query i have executed in both servers in MySQL( 8.0.16 ) server query is executed and returned data. Where as in MySQL(8.0.27) server it failed and returns out of sort memory please consider increasing server sort buffer size.
Both servers has sort_buffer_size of 1048576 bytes(1MB) and sort_merge_passes less than 20.
select json1 from jsonstore order by generated_on desc limit 1;
Above query also working fine in 8.0.16 but failed in 8.0.27.
So, i tired with increasing sort_buffer_size to 2MB in mysql (8.0.27) then it worked for 1st query and failed for 2nd query. Again i have updated it to 5MB then both query's executed successfully.
But same queries in other mysql server (8.0.16) are working fine with sort_buffer_size of 256Kb.
My fear is that if this is the case as table grows i need to keep on increase the sort_buffer_size. not sure weather it is an bug in mysql new verison or there is a change in mechanism of sort_buffer_size.
Please help me with understanding of how sort_buffer_size will work internally.
Note:- above problem is not limited to those two queries there are some other queries using order by and group by has json columns also throwing same error but working fine in 8.0.16 version.
Finally found that it is an issue in 8.0.27 version of mysql and with the latest release of mysql 8.0.28 this issue is fixed.
Sorts of some column types, including JSON and TEXT, sometimes exhausted the sort buffer if its size was not at least 15 times that of the largest row in the sort. Now the sort buffer need only be only 15 times as large as the largest sort key. (Bug #103325, Bug #105532, Bug #32738705, Bug #33501541)
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html#mysqld-8-0-28-data-types