Concerning the index-update just a question to SQL

Nextcloud version: 15.0.4
Shared hosting at manitu

The issue you are facing: alternative to occ db-comands

Hi there,
I recently updated my installation to 15.0.4 I got the expected warnings about missing indexes and the big int.

missing indexes, big int
  • 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 “share_with_index” in table “nc_share”.
  • Missing index “parent_index” in table “nc_share”.
  • Missing index “owner_index” in table “nc_share”.
  • Missing index “initiator_index” in table “nc_share”.
  • Missing index “fs_mtime” in table “nc_filecache”.
  • 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.
  • activity.activity_id
  • activity.object_id
  • activity_mq.mail_id
  • filecache.fileid
  • filecache.storage
  • filecache.parent
  • filecache.mimetype
  • filecache.mimepart
  • filecache.mtime
  • filecache.storage_mtime
  • mimetypes.id
  • storages.numeric_id

So now, I searched the web and found this post on how to solve these actualization on shared hosting.

Just to understand right (as I don’t know nearby nothing about SQL), for every of the named tables I have to add the index as below, and for every of the named tables at the big intsection too as in the format table.key, right?

So, in my case it would be the following:

ALTER TABLE `nc_share` ADD KEY `share_with_index` (`uid_owner`) USING BTREE;
ALTER TABLE `nc_share` ADD KEY `parent_index` (`uid_initiator`) USING BTREE;
ALTER TABLE `nc_share` ADD KEY `owner_index` (`uid_owner`) USING BTREE;
ALTER TABLE `nc_share` ADD KEY `initiator_index` (`uid_initiator`) USING BTREE;
ALTER TABLE `nc_filecache` ADD KEY `fs_mtime` (`uid_initiator`) USING BTREE;


ALTER TABLE `nc_activity` MODIFY `activity_id` bigint NOT NULL DEFAULT '0';
ALTER TABLE `nc_activity` MODIFY `object_id` bigint NOT NULL DEFAULT '0';
ALTER TABLE `nc_activity` MODIFY `mq.mail_id` bigint NOT NULL DEFAULT '0';

ALTER TABLE `nc_filecache` MODIFY `fileid` bigint NOT NULL DEFAULT '0';
ALTER TABLE `nc_filecache` MODIFY `storage` bigint NOT NULL DEFAULT '0';
ALTER TABLE `nc_filecache` MODIFY `parent` bigint NOT NULL DEFAULT '0';
ALTER TABLE `nc_filecache` MODIFY `mimetype` bigint NOT NULL DEFAULT '0';
ALTER TABLE `nc_filecache` MODIFY `mimepart` bigint NOT NULL DEFAULT '0';
ALTER TABLE `nc_filecache` MODIFY `mtime` bigint NOT NULL DEFAULT '0';
ALTER TABLE `nc_filecache` MODIFY `storage_mtime` bigint NOT NULL DEFAULT '0';

ALTER TABLE `nc_mimetypes` MODIFY `id` bigint NOT NULL DEFAULT '0';
ALTER TABLE `nc_storages` MODIFY `numeric_id` bigint NOT NULL DEFAULT '0';

Just an ‘okay‘, that I understood the given information would be great :smiley:
Thanks

Have you seen this?

It is much easier than fiddling around with sql-statements…

Nope. Thanks. Works easy!

Thanks. :slight_smile:

1 Like