select * from strapi_database_schema order by time DESC limit 1 - Out of sort memory, consider increasing server sort buffer size

[claudiograssia]

Bug report

Hi, I found this error after the schema column in the strapi_database_schema table reached 262kb.

Required System information

Strapi Version: You can reproduce this bug using Strapi versions from 4.5.0 to 4.24.2.
Database: Mysql 8
Node Versione: 16,18,20
NPM: 6.0
OS: Ubuntu 20.04
Project: Javascript

Describe the bug

I encountered this error when I added many collections, resulting in the schema column in the strapi_database_schema table reaching a size of 262kb. When I attempted to start the server using the command:

yarn develop

I receive the error:

Error: select * from `strapi_database_schema` order by `time` DESC limit 1 - Out of sort memory, consider increasing server sort buffer size

I attempted to resolve the issue by adding a simple index key to the schema column, but it did not work in any case.

Steps to reproduce the behavior

1. Create a Strapi instance with a MySQL database.
2. Create many collections until the schema column in the strapi_database_schema table reaches 262kb.
3. Attempt to start Strapi using the command yarn develop.
4. Observe the error in the console.

Expected behavior

The error:

Error: select * from `strapi_database_schema` order by `time` DESC limit 1 - Out of sort memory, consider increasing server sort.

should not appear; otherwise, the Strapi instance will not start.

[claudiograssia]

"I also tried this query directly in the database; if I try to insert 'id' instead of 'time' in the sorting, the error does not appear."

[Lau08]

Is there any update of this issue? Or a way to partial fix?

[abaksha-sc]

Faced the same issue on MySQL 8.0.35 and Strapi 4.11.5.

Hot fixed by increasing sort_buffer_size from default 262144 to 500000 (execute SQL SET GLOBAL sort_buffer_size = 500000;), but documentation says

On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload

So maybe better fix is necessary.
I also read that INDEX by sorting column can help, but didn't try it.

This happens when you have many collections and strapi stores all DB schema in a single field. In my case the field schema of table strapi_database_schema has size 257112 characters what is almost a limit for default database buffer size 262144. And when strapi reads it with additional fields then the limit exceeds.

It's mostly MySQL issue: https://bugs.mysql.com/bug.php?id=103225
But I believe it's possible to fix on strapi side (for example by splitting the database schema to few parts).
Or maybe it's possible to handle this error and retry the query after SET SESSION sort_buffer_size = 500000;

[claudiograssia]

Hy guys,
My suggestion is:
In order to recover schema column without error we can do

select id from strapi_database_schema order by time desc limit 1

and than

select * from strapi_database_schema where id=id

or we can have this query

select * from strapi_database_schema where id = (
    select id from strapi_database_schema order by time desc limit 1
);