Infinite SQL query keeps mysql at 100% indefinitely

ℹ️ Support
1
Support intro

Sorry to hear you’re facing problems :slightly_frowning_face:

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

example

Or for longer, use three backticks above and below the code snippet:

longer
example
here

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can :heart:

Nextcloud version (eg, 20.0.5): 25.0.5
Operating system and version (eg, Ubuntu 20.04): Debian Buster, inside docker container (Docker)
Apache or nginx version (eg, Apache 2.4.25): Nginx
PHP version (eg, 7.4): 7.4.26

The issue you are facing:

From time to time (around once a day at least), I notice that the mysql service is at 100% of the cpu. Checking which processes are causing this, I find this query that never ends:

SELECT `path` FROM `oc_filecache` WHERE (`storage` = 18) AND (`size` < 0) ORDER BY `fileid` DESC LIMIT 1

It seems to be triggered by a nextcloud cron job, since it’s running while this is still running:
php7 -f /config/www/nextcloud/cron.php

I usually kill these processes and it goes back to normal, but only for a few hours, as the issue tends to reappear.

I have the app Memories together with Recognize installed and active, so I don’t know if they could be related somehow. A few weeks ago there was a similar problem that was caused by recognize, but they query was different and was already fixed in an update.

Is this the first time you’ve seen this error? (Y/N): N. It has been happening for several days/weeks now.

Steps to replicate it:

  1. Wait.
  2. Notice the increased CPU usage.

I didn’t see anything relevant in the logs, but I’d be happy to provide them if necessary.

2

I don’t really thik this is a reason or you might have filled the form wrong but Docker NC 25 runs PHP Version: 8.1.16 I would really wonder you still have 7.4 in place. and if this is the case I strongly recommend to move to more modern and supported PHP version.

3

I am aware, it’s on my to-do list. I’ve been updating nextcloud without updating the docker container. Because the last time I did, so many things broke that I spent days fixing it up. Someday I’ll be ready.

4

If you are using the official Nextcloud Docker image, the right way to update your Nextcloud is to run a newer version of the image. Not using the web UI updater or manually doing it some other way.

2 Likes
5

Is anyone else having this issue? It reappears from time to time and the only way is to manually kill the cron.php process.