article_revisions & sort_buffer_size issue

[AidasK]

Description

Article edit page is crashing due to unoptimal db query.

select * from `article_revisions` where `article_revisions`.`article_id` = 444 and `article_revisions`.`article_id` is not null order by created_at desc;

Error: Out of sort memory, consider increasing server sort buffer size

This can be easily solved by ordering by an indexed field instead, so order by id desc works.

Also edit page can perform a lot better and would not crash if revision payload would not be selected.

So ideal query would be:

select id, created_at from `article_revisions` where `article_revisions`.`article_id` = 444 and `article_revisions`.`article_id` is not null order by id desc;

Steps to reproduce

1. Create and article with lots of languages and lots of block with a lot of revisions. 40 languages, 20 blocks & 20 revisions ± (nothing mind blowing)
2. Article edit page is not loading due to mysql sort_buffer_size too low error

Expected result

Edit page should always load.

Actual result

500 error page is displayed instead.

Versions

Twill version: 3.4.1
Laravel: 11.x.x
PHP: 8.3
DB: Mysql 8.0.31.

[Tofandel]

Are you sure you are on mysql 8.4 and not 8.0?
https://bugs.mysql.com/bug.php?id=103225

[AidasK]

Sorry, our Database version is MySQL 8.0.31. , by the comments in the thread "This particular bug was patched in 8.0.28 .". So maybe it should be fixed?