Missing indices after an update

Hello,

I had a similar problem after many updates. Most of the time I could find a solution, but this time I am stuck.

I updated from 22.2.3 to 23.0.2 and got the following warnings:

Fehlender Index "fs_id_storage_size" in der Tabelle "oc_filecache".
Fehlender Index "fs_storage_path_prefix" in der Tabelle "oc_filecache".
Fehlender Index "properties_pathonly_index" in der Tabelle "oc_properties".
Fehlender Index "job_lastcheck_reserved" in der Tabelle "oc_jobs".

I don’t have root access, so I can’t use “occ db:add-missing-indices”.

In the past I often used the glorious addin “OCC Web”. But it no longer works, although it is still listed in the store.

But I have full access to the database. Unfortunately I cannot figure out the correct command from the error message (warning) above. I think it has to be something like this:

ALTER TABLE oc_filecache ADD INDEX fs_id_storage_size (fileid, storage, size) USING BTREE;

etc

It would be very kind if someone could post the SQL command.

I would be happy if there was a general solution for this that would also apply to future updates. I.e. if in future updates the command could be derived from the error message.

Many thanks in advance.

Cheers
Guss

1 Like

You don’t need root access. The command needs to be run as the user of your webserver e.g. www-data. If you do not have access to a command line at all, you could maybe put the command to a crontab and run it just once at a fixed time and then delete the cronjob again.

2 Likes

Thank you very much. I was sure that you have to be root :slightly_smiling_face:

php ./occ db:add-missing-indices
did the trick.

1 Like

Good evening,
forgive me if I get involved in the discussion: I too had the same problem, going from 22.2.3 to 22.2.5.
I tried using sudo -u www-data php occ db: add-missing-indices
but it gives me this error:
This version of Nextcloud is not compatible with> PHP 8.0.
You are currently running 8.1.2.
Can you help me please?
Thank you!

Short answer: install and/or activate PHP8.0 on your system.

In order to help with that we would need more information about your setup. But I think you better create a new thread for your specific issue.

1 Like

I had the same issue from 22.2.3 to 22.2.5, and
after:
sudo -u www-data php occ db: add-missing-indices
came out:
An unhandled exception has been thrown:
OC\HintException: [0]: Memcache \OC\Memcache\APCu not available for local cache (Is the matching PHP module installed and enabled?)

I think error does not belong to your command.

Install php-apcu
(sudo) apt-get install php-apcu

php -m
[PHP Modules]
apc
apcu
bcmath
bz2
calendar
Core…

Read this issue.

Enabling apc.enable_cli = 1 fixes the issue.

(perhaps in `/etc/php/7.4/cli/conf.d/20-apcu.ini`)

or

sudo -u www-data php --define apc.enable_cli=1  occ db: add-missing-indices
3 Likes

Perfect!!!
Thank you so much.
it is working like a charm. :slight_smile:

Lovely! Worked for me too!

Hi I had the same problem and got it not to run with above instructions.

Within an other forum I found the hint for the setting of alternative config files NETCUP FORUM.

Within folder config I created the file datadirectory.config.php with content

<?php
$CONFIG = array (

  • ‘datadirectory’ => ((php_sapi_name() != ‘cli’) ? ‘/var/www/vhosts/hostingxxxxxxx.netcup.net’ : ‘’) . ‘/path_to your_Data_directory’,*
    );

Forgive me for the late reply! But between work and Covid-19, they have been intense days …
I posted here thinking it was ineterent to the update.
I managed to solve it by starting the instance with php7.4, then I’ll try to better understand how to activate php8.

Good to hear that you managed to get up and running again. And while 8.0 ist the recommended version and will eventually become the minimum requirement with a future version of Nextcloud, running it on PHP 7.4 is perfectly fine for now and fuilly supported in Nextcloud 23.

1 Like

Hi,

same problem here (all-inkl. shared hosting).

Can you please give me further instructions how to setup this?

I can execute a php file via cron. But what is the excact code to put in the php file?

Thanks!

The webserver must execute this command (i think this is your command):
php occ db:add-missing-indices
From root you can use
sudo -u www-data php occ db:add-missing-indices
“occ” is in path /path/to/nextcloud. Maybe you must use the full path.

In your case you can e.g. use cron or use OCC Web and only execute there.
db:add-missing-indices
Problem: Officially OCC Web for Nextcloud 24 is not yet supported.

It’s a pity that this doesn’t seem to be executed directly during the update or at least asked for. Would perhaps be an improvement in a future release if possible and useful.

Hi,

I am on all-inkl shared hosting. So I have no root(SSH) access. Usual cronjobs are not supported, just webcron.

So in the end I had to execute a script via cronjob which triggers the command.

The solution that worked for me was:

<?php
exec("/usr/bin/php80 ./occ db:add-missing-indices  2>&1", $out, $result);
echo "Returncode: " .$result ."<br>";
echo "Ausgabe des Scripts: " ."<br>";
echo "<pre>"; print_r($out);
?>

This script (fixes.php) could be executed via cronjob.

1 Like

Hello,

I’m also on shared hosting (uberspace.de) and don’t have root access, but can connect with my normal user via ssh.
In my case my instance of apache runs also with my user. I don’t need root and can execute the command in the ssh terminal:

php ./occ db:add-missing-indices

Hi, @NCUser68 , thank you very much for this code, it helped me to run the OCC command after upgrading to nc25 &26. I also run a shared hosted installation . After OCCweb was obsolete I used a 2nd config file to run OCC. But since NC25 this did not work any more. Only your provided code was the solution!

Best regards, Martin

Just interjecting what I have found to be successful. Here’s how I define the php version used:

sudo -u www-data php8.0 /var/www/nextcloud/occ db:add-missing-indices