SQL Error [1038] [HY001]: Out of sort memory，考虑增加服务器排序缓冲区大小.

Question

我在mysql中创建了一个用于搜索文章的存储过程。当我想调用它时，会得到这个错误

SQL Error [1038] [HY001]: Out of sort memory，考虑增加服务器排序缓冲区大小

我的代码目的是首先根据标题相对排序，然后根据描述相对排序。

也增加缓冲区大小后(并重新启动mysql服务，然后再次调用sp)，我得到这个错误:SQL错误[S1000]: Could not retrieve transaction read-only status from server

create PROCEDURE art_usp_Article_Search(in SearchTerm varchar(200),in SortColumn varchar(100),in SortOrder varchar(5),in PageNumber int,in PageSize int)
BEGIN


DECLARE StartRow INT;
DECLARE EndRow INT;
declare NSortColumn varchar(100);
declare NSortOrder varchar(100);
-- calculate the starting and ending of records
SET NSortColumn = LOWER(IFNULL(SortColumn, ''));
SET NSortOrder = LOWER(IfNULL(SortOrder, ''));
SET StartRow = (PageNumber - 1) * PageSize  ;    
SET EndRow = (PageNumber * PageSize)+1      ;   

WITH CTETitleResult AS (
SELECT ROW_NUMBER() OVER (ORDER BY 
CASE 
WHEN (@SortColumn = 'title' AND @SortOrder='asc') THEN title
WHEN (@SortColumn = 'date' AND @SortOrder='asc') THEN date
end
) AS RowNumber
, COUNT(*) OVER () AS TotalCount
,id,code,categoryId ,title,shortDescription ,description ,tags ,content,date,time,typeId,isActive,isSpecial,visited,author,imageId,statusId,created_by,updated_by,created_at,updated_at
FROM art_article aa 
WHERE isActive = 1
AND (
(IfNULL(SearchTerm, '') = '' OR title LIKE CONCAT('%', SearchTerm, '%'))
)
),CTEDescriptionResult AS (
SELECT ROW_NUMBER() OVER (ORDER BY 
CASE 
WHEN (@SortColumn = 'title' AND @SortOrder='asc') THEN title
WHEN (@SortColumn = 'date' AND @SortOrder='asc') THEN date
end
) AS RowNumber
, COUNT(*) OVER () AS TotalCount
,id,code,categoryId ,title,shortDescription ,description ,tags ,content,date,time,typeId,isActive,isSpecial,visited,author,imageId,statusId,created_by,updated_by,created_at,updated_at
FROM art_article aa 
WHERE isActive = 1
AND (
(IfNULL(SearchTerm, '') = '' OR shortDescription LIKE CONCAT('%', SearchTerm, '%'))
)
)
SELECT *
FROM CTETitleResult
WHERE RowNumber > StartRow AND RowNumber < EndRow
union       
SELECT *
FROM CTEDescriptionResult
WHERE RowNumber > StartRow AND RowNumber < EndRow
ORDER BY RowNumber;

END
call art_usp_Article_Search('','title','asc',1,10)

Answer 1

临时解决方案

运行mysql query

SET GLOBAL sort_buffer_size = 256000000//服务器重启后复位

设置永久

编辑下面的文件并添加

sudo nano/etc/mysql/mysql.conf.d/mysql .cnf

sort_buffer_size = 256000000

mysql restart