Level 1
Please provide enough code so others can better understand or reproduce the problem
Feb 27, 2022
9
Level 10
Memory allocation error: 1038
I am getting below error.
SQLSTATE[HY001]: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size
SQL: select * from program_versions where program_versions.program_id = 93 and program_versions.program_id is not null and program_versions.deleted_at is null order by created_at desc limit 1
How would we solve this issue?
Level 56
consider increasing server sort buffer size
First three results in DuckDuckGo for search string: SQLSTATE[HY001]: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size
- https://bobcares.com/blog/mysql-error-1038/
- https://stackoverflow.com/questions/66075626/sql-error-1038-hy001-out-of-sort-memory-consider-increasing-server-sort-bu
- https://stackoverflow.com/questions/29575835/error-1038-out-of-sort-memory-consider-increasing-sort-buffer-size
Next time inform which database server and version you are using
Level 10
@rodrigo.pedra Is there any way to make code level changes?
Level 56
@swapnilnandgave By code level you mean from PHP?
Because changing config to a program is kind of code level, isn't it?
From PHP I don't think so, this is a config needed to be changed in MySQL and MySQL needs to be restarted to make it work.
I see you run it on Digital Ocean droplet, it should be possible to have ssh shell and change this.
Level 10
@rodrigo.pedra Thanks for that. Will do ssh and make changes
Level 10
I am using mysql 8.0 version and it is digital ocean deployment.
Level 102
@swapnilnandgave Ok a few questions that might give us some insights.
- Why do you need to check if the program_id is not null? You are already checking if its a specific ID.
- Are there multiple items with there same
program_id? You are sorting but only getting 1 record? - How many records are in that table? Based on the error, I would assume you have many millions
Level 10
- It is important for starting point. It is like parent and child relation
- Yes. One program has multiple program versions
- Program Table has only 99 Records and 287 in program versions.
Program version has one json column
Level 1
I had the same issue with MySQL 8.0.27. Upgrading to newer version fixed the issue for me.
Please or to participate in this conversation.