Missing Indices on NC15

[Alishara]

After updating to nextcloud 15.0.0 I get the following error:

Some indices are missing in the database. Because adding indexes to large tables may take some time, they have not been added automatically. By executing “occ db: add-missing-indices”, missing indexes can be added manually while the instance is running. Once the indexes have been added, queries to these tables are usually faster.

• Fehlender Index “owner_index” in der Tabelle “oc_share”.
• Fehlender Index “initiator_index” in der Tabelle “oc_share”.

Using a shared hosting I can not launch the occ command so I need the SQL-Command für PHPMyAdmin.

There is also a problem with a lot conversion to big int. Is there also a SQL-Command for this?

How can I add these values manually?

Thank you Alishara

[Schmu]

Indices:

"The database is missing some indexes." and "Some columns in the database are missing a conversion to big int." with phpmyadmin and-or CRON job (can't use occ command in shared hosting)

Hello, queries to run for fixing the issues: ALTER TABLE `oc_share` ADD KEY `owner_index` (`uid_owner`) USING BTREE; ALTER TABLE `oc_share` ADD KEY `initiator_index` (`uid_initiator`) USING BTREE; I updated my system to NC15 and still trying to fix some performance issues. I will write down my findings since the system is running.

Big Int:

"The database is missing some indexes." and "Some columns in the database are missing a conversion to big int." with phpmyadmin and-or CRON job (can't use occ command in shared hosting)

Hello, with the following queries you can modify the database field type from int(11) to bigint(20): ALTER TABLE `oc_filecache` MODIFY `mtime` bigint NOT NULL DEFAULT '0'; ALTER TABLE `oc_filecache` MODIFY `storage_mtime` bigint NOT NULL DEFAULT '0'; This will solve the “occ db: convert-filecache-bigint” part.

[Alishara]

Thanks, that helped a lot. Everything worked perfectly. Now there are only a few modifications to bigint to do for:

• activity.activity_id
• activity.object_id
• activity_mq.mail_id
• filecache.fileid
• filecache.storage
• filecache.parent
• filecache.mimetype
• filecache.mimepart
• mimetypes.id
• storages.numeric_id
  When I see this right this is alway like this?
  ALTER TABLE oc_activity MODIFY activity_id bigint NOT NULL DEFAULT ‘0’;

[Schmu]

@Alishara

When I see this right this is alway like this?
ALTER TABLE oc_activity MODIFY activity_id bigint NOT NULL DEFAULT ‘0’;

Well, not exactly. For
activity.activity_id
activity_mq.mail_id
filecache.fileid
mimetypes.id
storages.numeric_id
as example the columns actually have “NULL” as default and are “auto_increment”.

So the query is rather like this:

ALTER TABLE oc_filecache MODIFY fileid bigint AUTO_INCREMENT PRIMARY KEY;

For the rest it should correct.

But: I’m not DB expert and not 100% sure about that. Definitely create a DB backup before you try anything.

[Maurice]

Hello,

with the following queries you can modify the database field type from int(11) to bigint(20):

ALTER TABLE `oc_filecache` MODIFY `fileid` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `oc_filecache` MODIFY `storage` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `parent` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `mimetype` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `mimepart` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `mtime` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `storage_mtime` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_activity` MODIFY `activity_id` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `oc_activity` MODIFY `object_id` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_activity_mq` MODIFY `mail_id` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `oc_storages` MODIFY `numeric_id` bigint NOT NULL AUTO_INCREMENT;

This will solve the “occ db: convert-filecache-bigint” part.

[Schmu]

@Maurice what about the columns which are actually primary keys with default NULL and auto_increment? Shouldn’t they be treated differently? Does the alter table query you wrote leave the primary key and auto_increment definition leave untouched?

[Maurice]

@Schmu to be on the save site you are absolutly right:

ALTER TABLE `oc_filecache` MODIFY `fileid` bigint NOT NULL AUTO_INCREMENT; 

DB structure looks like this:

[Schmu]

Okay

Interestingly the columns are default NULL on my system:

+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| fileid           | bigint(20)    | NO   | PRI | NULL    | auto_increment |

| activity_id      | bigint(20)    | NO   | PRI | NULL    | auto_increment |

| mail_id          | bigint(20)    | NO   | PRI | NULL    | auto_increment |

Not sure where the difference to your system is coming from.

[Maurice]

Ok - just checked the query to be sure if it leaves primary key as it is. The following query won’t change the primary key settings:

ALTER TABLE `oc_filecache` MODIFY `fileid` bigint NOT NULL AUTO_INCREMENT; 

[Maurice]

+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| fileid           | bigint(20)    | NO   | PRI | NULL    | auto_increment |

| activity_id      | bigint(20)    | NO   | PRI | NULL    | auto_increment |

| mail_id          | bigint(20)    | NO   | PRI | NULL    | auto_increment |

@Schmu interesting. By using AUTO_INCREMENT the default value NULL does not make sense

[Alishara]

Hallo, hat bestes funktioniert. Vielen Dank, ihr wart echt schnell!
Eines hatte allerdings noch gefehlt:

ALTER TABLE oc_mimetypes MODIFY id bigint NOT NULL AUTO_INCREMENT;