Just to add to this... I've been going quietly blue in the face trying to solve the same problem on my system. I was getting the same
SQL: Missing result set!/
GetMoviesByWhere failed errors for both
Recently Added movies and
Sets.
It seems that mysqld is somewhat... flexible about which config files it uses: from one post I read, it seems that there are over 40 different potential locations. Different systems will, therefore, reference different config files - edit the wrong one, and you'll be there all week waiting for the new variables to make any difference. So, YMMV when it comes to whether or not
mysqld.cnf
will work.
To check what configs are being used
$ mysqld --verbose --help | more
This will return pages of output, hence the
more
(you could also use
head
, of course). The files being read, and their order, are listed under "
Default options are read from the following files in the given order". Cunning title, that.
Code:
mysqld Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
Copyright © 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starts the MySQL database server.
Usage: mysqld [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
Then edit one of these. Make sure you're editing a global file (e.g.
/etc/my.cnf
) or one that applies to the relevant user as whom the daemon is running - there's no point in editing your personal config if you're running the daemon as root, after all.
Add the sort_buffer_size declaration to the
[mysqld]
section and restart, as above. I've gone for 1MB (
sort_buffer_size=1M
) and put that into the existing
[mysqld]
section of
/etc/my.cnf
.
To check if the parameter will take effect
$ mysqld --verbose --help | grep sort-buffer-size
This is effectively the same command as above, we're just looking for a different bit of the output. In essence, this simply reads the defined list of config files, and returns all the configuration values that will be used when mysqld runs. We're only interested in one, hence the
grep
.
Code:
--innodb-sort-buffer-size=#
--myisam-sort-buffer-size=#
--sort-buffer-size=#
innodb-sort-buffer-size 1048576
myisam-sort-buffer-size 8388608
sort-buffer-size 1048576
... you can see the 1048576 bytes I set (1MB = 1024 * 1024 bytes), versus the default of 262144 bytes (256k).
If it looks good, restart the daemon/service.
To check if the parameter has taken effect
$ mysql -u root -p
Code:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 190
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Copyright © 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW VARIABLES where Variable_name = "sort_buffer_size";
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| sort_buffer_size | 1048576 |
+------------------+---------+
1 row in set (0.00 sec)
Now restart Kodi. All is good.