The database is missing some indexes. run "occ db:add-missing-indices" - cpanel

ℹ️ Support
1

Hi,

Similar to issues 160647 & 160871 .

I have the following error message in overview -

  • The database is missing some indexes. Due to the fact that adding indexes on big tables could take some time they were not added automatically. By running “occ db:add-missing-indices” those missing indexes could be added manually while the instance keeps running. Once the indexes are added queries to those tables are usually much faster.
    • Missing index “fs_parent” in table “oc_filecache”.

Problem is i’m unable to run the command via bash as I’m on cpanel and there’s no sudo access.

Also tried via cron using command ‘www-data php --define apc.enable_cli=1 /var/www/nextcloud/occ db:add-missing-indices’ to no avail .

I suspect I could run some sql to fix this via phpmyadmin ,adapted this from a fix for some other issue

ALTER TABLE oc_filecache ADD INDEX fs_parent (userid, propertypath) USING BTREE;

The response is that #1072 - Key column ‘userid’ doesn’t exist in table , I suspect this SQL would likely need to be quite different anyway, bit of a stab in the dark really .

How can I best resolve this ? The instance is extremely slow currently, probably also impacting this is the following message I see -

  • No memory cache has been configured. To enhance performance, please configure a memcache, if available. Further information can be found in the documentation :arrow_upper_right:.

In config I have -

'memcache.local' => '\OC\Memcache\APCu',
'memcache.distributed' => '\OC\Memcache\Redis',

Also acpu, redix and memcache php extensions are enabled (using php 8.1)

Not sure what to do to resolve this , performance is currently extremely poor …

Appreciate any guidance you can provide…

Thanks

2

Did you try this app: occweb?
Maybe this is a solution for you (however, it’s only for NC<=23).

3

Thanks @philharmonie , unfortunately not an option as I am on 26.01 , thank you though, I did try to install it …

4

You don’t need sudo access. If you can log into the shell as the web server user (www-data or whatever it is in your case), that would be good enough to run occ.

2 Likes
6

Thanks, sorted this now, I had the command wrong …

Still stuck on this bit -

  • No memory cache has been configured. To enhance performance, please configure a memcache, if available. Further information can be found in the documentation :arrow_upper_right:.
7

Do you actually have redis running?

Here is the documentation on the subject. https://docs.nextcloud.com/server/stable/admin_manual/configuration_server/caching_configuration.html#memory-caching

8

Hmm maybe it isn’t, the response i get for ps ax | grep redis is -

3679292 pts/7 S+ 0:00 grep --color=auto redis

It’s supposed to show IP address isn’t it ?

9

Added this to config … as it seems the most appropriate version for my setup -

‘memcache.local’ => ‘\OC\Memcache\APCu’,
‘memcache.distributed’ => ‘\OC\Memcache\Redis’,
‘memcache.locking’ => ‘\OC\Memcache\Redis’,
‘redis’ => [
‘host’ => ‘localhost’,
‘port’ => 6379,
],

10

What you’re seeing here is not redis, but the very grep you ran to look for it.

I suspect your system is not actually running redis, and so any attempt to connect to the redis daemon will naturally fail.

If you aren’t able to install redis, then you may have to remove all of the above configuration except for

That may satisfy the memcache error, but performance would be notably better with redis I think.

11

Your cron command should look like this

php --define apc.enable_cli=1 -f /full path to your Nextcloud installation/occ db:add-missing-indices

in my case it is

php --define apc.enable_cli=1 -f /home/xxx/domains/xxx.com/public_html/xxx/occ db:add-missing-indices

12

I have the same Problem but i am on a VServer with Plesk and root access. Execute the Command with shell tells me i have the wrong php version. The server has 7.4.3 but in Plesk it is 8.x After updating from Nextcloud 26.0 to 26.1

13

Just use /opt/plesk/php/version/bin/php for your path to php.

14

Thanks, this is really helpful, I also raised this with my host who have now informed me that they do not allow nextcloud installs on their servers, might need to change my hosting plan …

15

Thanks, yep figured this out in the end and it ran fine in terminal !

16

Thank you for your Post. Because i am New at Terminal i dont know how to combine it with database command. Sorry for my bad english

17

Depending if you have a separate user for your webserver, you have to use it similar like this

cd /path/to/your/nextcloud
sudo -u username /opt/plesk/php/version/bin/php ./occ

Hope this helps.

18

Probably going to opt to use Oracle free tier instead for this now, should probably have considered that in the first place.

Thank you all for the help though, much appreciated.

19

Perfect Thank You