NextCloud Internal Server Error: The server was unable to complete your request.

Recently on a fresh installation of NextCloud, the following error appeared after logging into the dashboard:

Or in plain text:

Internal Server Error
The server was unable to complete your request.

If this happens again, please send the technical details below to the server administrator.

More details can be found in the server log.

Technical details
Remote Address: XX.XX.XXX.XX
Request ID: YGwdJ99rJht4RtWzYxQ2

When checking the nextcloud.log file, the following errors stood out:

{"reqId":"zs37dGr4l9Kb6N3LGAWn","level":3,"time":"2022-01-04T16:09:00+00:00","remoteAddr":"XX.X.XXX.XXX","user":"jatak","app":"index","method":"GET","url":"/index.php","message":"An exception occurred while executing a query: SQLSTATE[HY001]: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36","version":"23.0.0.10","exception"

So the script is going over the sort buffer size limit configured in MySQL.

This appears to be an outstanding issue with MySQL which causes this error when trying to sort on a table with a JSON column. It looks like it impacts MySQL >= 8.0.1 and MariaDB >= 10.3

Option 1. Disable Circles app

To solve this, if you do not have access to the server configuration files (you are on a shared hosting server), simply disable the Circles app in NextCloud by renaming the following folder:

/apps/circles

This will disable the Circles app which needs a higher sort buffer size limit.

Option 2. Edit my.cnf file

If you have root access to the server (VPOS or DEDICATED server) you can edit the MySQL configuration file and increase the sort_buffer_size value.

sudo nano /etc/mysql/my.cnf

and inside increase the sort_buffer_size:

[mysqld]
sort_buffer_size=5M

then restart MySQL

sudo service mysql restart