OCC commands in shared hosting! "occ db: add-missing-indices"

[ddi98]

Steps to reproduce

Hi, I use nextcloud on a shared hosting and since I upgraded to version 14.0.1 the indexes are no longer updated in the database and to update them I should launch this command occ db: add-missing-indices, but I can not do it since I use a shared hosting! Could you work on this problem that affects a large slice of users? thank you so much

Server configuration

Operating system: CentOS

Web server: Hosting Linux Aruba

Database: MySql

PHP version: 7.2.8

Nextcloud version: 14.0.1

[realies]

Can second that. Happened on all instances I have updated.

[nextcloud-bot]

GitMate.io thinks possibly related issues are #11167 (occ db:add-missing-indices - Exact steps to resolve?), #11463 (Missing-index), #10926 (Possible DB indexes), #10656 (Configuration check - Minor typo: occ db:add-missing-indices), and #866 (Calendar Share Functionality missing).

[MorrisJobke]

Hi, I use nextcloud on a shared hosting and since I upgraded to version 14.0.1 the indexes are no longer updated in the database and to update them I should launch this command occ db: add-missing-indices, but I can not do it since I use a shared hosting! Could you work on this problem that affects a large slice of users? thank you so much

There is a reason why this is a CLI command: it could take a bigger amount of time to complete and getting this command killed by the Webserver would result in a broken DB. Thus we added it to the CLI interface. Also if you run on a shared hosting instance there is not so much performance impact because usually those instances aren't that huge and only serve a handful of users. This optimization is basically for large instances that take advantage when sharing pages have hundreds of thousands of entries.

But we can check on showing the SQL statements that are needed to add those indexes then.

cc @nickvergessen @rullzer

[ddi98]

Mine is not a large instance but still hosts 500 users and 200 GB of files. This operation could be completed without problems during web browsing, to avoid the timeout you could separate this operation from the web update and you could allow to run it optionally later "it could be a good solution"

PS: Would there be a way to directly perform this operation from phpMyAdmin?

[MorrisJobke]

to avoid the timeout you could separate this operation from the web update and you could allow to run it optionally later "it could be a good solution"

There is no way to separate this from web requests if there are only web requests possible. The CLI would be the timeout-free approach ;)

PS: Would there be a way to directly perform this operation from phpMyAdmin?

I will link some resources where SQL statements could be build from so the message can be improved.

[nickvergessen]

Current queries to run are:

ALTER TABLE `oc_share` ADD KEY `share_with_index` (`share_with`) USING BTREE;
ALTER TABLE `oc_share` ADD KEY `parent_index` (`parent`) USING BTREE;
ALTER TABLE `oc_filecache` ADD KEY `fs_mtime` (`mtime`) USING BTREE;

[ddi98]

Thanks a lot to everyone for the availability!
You could provide more details on how to perform queries (for those who are not a web expert like me)

[nickvergessen]

Well you better google that or ask for help in the forums. We can't really give you support on administration side of things in our bugtracker.

[ddi98]

you're right, however it was simpler than I thought :)!
PS: when I perform this operation, do I have to put the instance into maintenance? thank you

[MorrisJobke]

PS: when I perform this operation, do I have to put the instance into maintenance? thank you

Not needed. The DB handles this properly.

[MorrisJobke]

Let's close this ticket here for now as there are the SQL statements.

[ddi98]

I launched the query on the database the result was the following:

ALTER TABLE oc_share ADD KEY share_with_index (share_with) USING BTREE; # MySQL returned an empty set (i.e. zero lines).

ALTER TABLE oc_share ADD KEY parent_index (parent) USING BTREE; # MySQL returned an empty set (i.e. zero lines).

ALTER TABLE oc_filecache ADD KEY fs_mtime (mtime) USING BTREE; # MySQL returned an empty set (i.e. zero lines).

Was this the result to be obtained?

[realies]

Assuming there could be code that can estimate if the query could be successful given the maximum script execution time configuration. If that is within the estimated limit, the query could be executed in the browser. In several of the instances, I have updated it took between 2-10 seconds to complete.

[ddi98]

Thank you have a nice day !

[realies]

@ddi98, is the warning gone from Nextcloud after you ran the query? If not, are you running it against the Nextcloud database?

[MorrisJobke]

Assuming there could be code that can estimate if the query could be successful given the maximum script execution time configuration. If that is within the estimated limit, the query could be executed in the browser. In several of the instances, I have updated it took between 2-10 seconds to complete.

I'm not aware of any estimations like this. :/

[ddi98]

the warning was returned by phpmyadmin, so the operation was not successful?

[MorrisJobke]

Was this the result to be obtained?

Looks good, because adding an index does not return anything.

[realies]

@MorrisJobke, assuming the amount of time required for a query of that type to complete is based on the number of records in the given table. Thinking about it, that doesn't say much about the speed of database procedures (disk IOPS, ram, available CPU time, etc)...

[ddi98]

In any case, the request turns out to be much more powerful!
It would be nice to add these queries in the official documentation

[FireEmerald]

The queries are working as expected, adding the indexes and the warning is gone after execution. (14.0.1)

[Tom-2019]

The three queries mentioned above solved three issue. The following remain:

Fehlender Index "owner_index" in der Tabelle "oc_share".
Fehlender Index "initiator_index" in der Tabelle "oc_share".

[ddi98]

Current queries to run are:

ALTER TABLE `oc_share` ADD KEY `share_with_index` (`share_with`) USING BTREE;
ALTER TABLE `oc_share` ADD KEY `parent_index` (`parent`) USING BTREE;
ALTER TABLE `oc_filecache` ADD KEY `fs_mtime` (`mtime`) USING BTREE;

Hello, I reopened the discussion to which you gave me solution some time ago :), after updating to nextcloud 15.0.2 a similar problem has resurfaced, would you be so kind to help me? :)

these are the errors:

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 "owner_index" in table "oc_share".
Missing index "initiator_index" in table "oc_share".

---

Some columns in the database are missing a conversion to big int. Due to the fact that changing column types on big tables could take some time they were not changed automatically. By running 'occ db:convert-filecache-bigint' those pending changes could be applied manually. This operation needs to be made while the instance is offline. For further details read the documentation page about this.
filecache.fileid
filecache.storage
filecache.parent
filecache.mimetype
filecache.mimepart
filecache.mtime
filecache.storage_mtime
mimetypes.id
storages.numeric_id

[Tom-2019]

Please check here:
#13578
I summarized what helped for me.

[FireEmerald]

But we can check on showing the SQL statements that are needed to add those indexes then.

@MorrisJobke Is there any open issue which tracks your idea? Because i - and many others, which are unable to use the occ component, would really appreciate if you would provide the required SQL commands right with the error inside the settings page of nextcloud (or at least a link).

[MorrisJobke]

@MorrisJobke Is there any open issue which tracks your idea? Because i - any many others, which are unable to use the occ component, would really appreciate if you would provide the required SQL commands right with the error inside the settings page of nextcloud (or at least a link).

Not really - let's reopen this for now.

[FireEmerald]

It's really frustrating to google for the queries after each update. Is there any document available which is always up-to date and lists the queries which must be executed after each update?

Missing Index "twofactor_providers_uid" in table "oc_twofactor_providers".
Missing Index "version" in table "oc_whats_new".
Missing Index "cards_abid" in table "oc_cards".
Missing Index "cards_prop_abid" in table "oc_cards_properties".

-- MySQL
ALTER TABLE `oc_whats_new` ADD KEY `version` (`version`) USING BTREE;
ALTER TABLE `oc_twofactor_providers` ADD KEY `twofactor_providers_uid` (`uid`) USING BTREE;
ALTER TABLE `oc_cards` ADD KEY `cards_abid` (`addressbookid`) USING BTREE;
ALTER TABLE `oc_cards_properties` ADD KEY `cards_prop_abid` (`addressbookid`) USING BTREE;

ref https://help.nextcloud.com/t/missing-indices-on-nc16/52284

15.0.7 > 16.0.1.

As it seems that this is not going to be implemented in the near feature, isn't it?

The OCC Web app (https://apps.nextcloud.com/apps/occweb) is a good approach, but unfortunately it seems like it isn't really well maintained as there's no official version for Nextcloud 16 available.

[MichaSei]

ich habe folgende Fehlermeldung (leider keine Lösung ergoogled) wenn ich in meinem phpmyadmin dies hier eingebe:

ALTER TABLE oc_share ADD KEY share_with_index (share_with) USING BTREE;
ALTER TABLE oc_share ADD KEY parent_index (parent) USING BTREE;
ALTER TABLE oc_filecache ADD KEY fs_mtime (mtime) USING BTREE;

Fehler
SQL-Befehl:

ALTER TABLE oc_share ADD KEY share_with_index (share_with) USING BTREE
MySQL meldet: Dokumentation

#1061 - Doppelter Name für Schlüssel vorhanden: 'share_with_index'

Was kann ich noch machen um mein Problem zu lösen?

english:

I have the following error message (unfortunately no solution ergoogled) if I enter this in my phpmyadmin this:

ALTER TABLE oc_share ADD KEY share_with_index (share_with) USING BTREE;
ALTER TABLE oc_share ADD KEY parent_index (parent) USING BTREE;
ALTER TABLE oc_filecache ADD KEY fs_mtime (mtime) USING BTREE;

error
SQL command:

ALTER TABLE oc_share ADD KEY share_with_index (share_with) USING BTREE
MySQL reports: Documentation

# 1061 - Duplicate key name exists: 'share_with_index'

What else can I do to solve my problem?

[kesselb]

Duplicate key name exists: 'share_with_index'

The index with the name share_with_index is already present. Skip this one and execute the next alter table statement.

[MichaSei]

thanxs for tis fast support.
But in the backend of my NC15 i have follow message:#
In der Datenbank fehlen einige Indizes. Auf Grund der Tatsache, dass das Hinzufügen von Indizes in großen Tabellen einige Zeit in Anspruch nehmen kann, wurden diese nicht automatisch erzeugt. Durch das Ausführen von "occ db:add-missing-indices" können die fehlenden Indizes manuell hinzugefügt werden, während die Instanz weiter läuft. Nachdem die Indizes hinzugefügt wurden, sind Anfragen auf die Tabellen normalerweise schneller.

Fehlender Index "parent_index" in der Tabelle "oc_share".
Fehlender Index "owner_index" in der Tabelle "oc_share".
Fehlender Index "initiator_index" in der Tabelle "oc_share".
Fehlender Index "fs_mtime" in der Tabelle "oc_filecache".

How i solve this?

[kesselb]

Please check here:
#13578
I summarized what helped for me.

@MichaSei

[skjnldsv]

What about the occ shell app for shared environment?
Does it work?

[Tom-2019]

occ shell does work on our Strato hosted website. NC 16.0.1

[DiagonalArg]

I see occ is supposed to be use "as your HTTP user", and with the http server's version of PHP. I'm using shared hosting but I do have shell access (webfaction - platform as a service) . All files in my nextcloud install are owned by my shell user.

1. Can I use occ?
2. How do I confirm which PHP version nextcloud is using, as the system has many.

Also, I have to second @FireEmerald 's frustration.

[nickvergessen]

In your shell run:

php --version

if that works, try the following in your nextcloud directory:

php occ

[sushidave]

FYI: If using Plesk the scheduled task feature can be used to run occ commands:

1. Task type: Run a command
2. Command: php /path/to/nextcloud/directory/occ db:add-missing-indices
   (replace “/path/to/nextcloud/directory/” by the actual path)
3. Leave the other settings as they are.
4. Click “Run now”.

https://help.nextcloud.com/t/how-to-execute-an-occ-command-using-scheduled-tasks-on-plesk/134098/2

[Flix6x]

You can use FTP access to add your own php file that executes custom occ commands. I added fix-config.php to my nextcloud directory and browsed to https://<my nextcloud url>/fix-config.php to run its contents. I had accumulated 4 different warning over the years, each of which recommended running a separate occ command, so my contents are:

<?php 
   exec("/usr/bin/php74 -d memory_limit=1024M occ -n db:add-missing-indices > occ-fixed-config.txt 2>&1");
   exec("echo \"Congratulations: Indices fixed!\" >> occ-fixed-config.txt");
   exec("/usr/bin/php74 -d memory_limit=1024M occ -n db:add-missing-primary-keys > occ-fixed-config.txt 2>&1");
   exec("echo \"Congratulations: Primary keys fixed!\" >> occ-fixed-config.txt");
   exec("/usr/bin/php74 -d memory_limit=1024M occ -n db:add-missing-columns > occ-fixed-config.txt 2>&1");
   exec("echo \"Congratulations: Columns fixed!\" >> occ-fixed-config.txt");
   exec("/usr/bin/php74 -d memory_limit=1024M occ -n db:convert-filecache-bigint > occ-fixed-config.txt 2>&1");
   exec("echo \"Congratulations: Filecache fixed!\" >> occ-fixed-config.txt");
   header('Location: occ-fixed-config.txt');
?>

This creates the file occ-fixed-config.txt containing whatever occ returned, among some congratulations. You can also set up a webcron job to trigger this endpoint, if you can't set up cron jobs at your host. You may need to adjust the PHP version to the one that Nextcloud shows under Administration/System (index.php/settings/admin/serverinfo). If your database isn't big, this should complete within the lifespan of a http request.

Is there any reason why such a file shouldn't be part of nextcloud server, to support users that want to fix problems after upgrading, but cannot run occ in shared hosting?

[AyoungDukie]

It may be worth converting some of these entries to a wiki entry or documentation file, as most existing documentation points to the occ command/script.

In a shared-hosting situation, where only the phpMyAdmin access is viable, these queries would be useful.

For the 26.0.X -> 27.0.0 upgrade, I am encountering this issue now:

Missing index "mounts_user_root_path_index" in table "oc_mounts".

And digging into the script for occ db:addissing-indices, the new step for this adds index mounts_user_root_path_index with columns user_id, root_id, and mount_point to the index, so I tried:

ALTER TABLE `oc_mounts` ADD KEY `mounts_user_root_path_index` (`user_id`, `root_id`, `mount_point`) USING BTREE;

however attempting define these runs into a length issue #1071 - Specified key was too long; max key length is 3072 bytes.

I see that the php file seems to define lengths of null, null, and 128, respectively, but I cannot seem to translate this to a query syntax well. Using

ALTER TABLE `oc_mounts` ADD KEY `mounts_user_root_path_index` (`user_id`(NULL), `root_id`(NULL), `mount_point`(128)) USING BTREE;

Gives the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL), `root_id`(NULL), `mount_point`(128)) USING BTREE' at line 1

Since this is a syntax error, Is there a different way should be applied? Based on my reading, it seems like the appropriate method would have either been to use parenthetical value of length (NULL in this case), however this is the limit of my knowledge here...