Missing index "systag_by_tagid" in table "oc_systemtag_object_mapping"

ℹ️ Support
, ,
1

Nextcloud version (eg, 20.0.5): 26.0.3
Operating system and version (eg, Ubuntu 20.04): ubuntu 20.04
Apache or nginx version (eg, Apache 2.4.25): apache(???)
PHP version (eg, 7.4): ???

The issue you are facing:

Security and setup warnings says

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 "systag_by_tagid" in table "oc_systemtag_object_mapping".

I can confirm it is VERY slow,
and if I do

nextcloud.occ db:add-missing-indices

It will not fix the problem

Is this the first time you’ve seen this error? (Y/N): N
I once solved it by doing snap revert but that is not working now regardless.

Steps to replicate it:

[I do not know what caused it although it might have been an auto update.]

The output of your Nextcloud log in Admin > Logging:

[Was too slow]

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'apps_paths' => 
  array (
    0 => 
    array (
      'path' => '/snap/nextcloud/current/htdocs/apps',
      'url' => '/apps',
      'writable' => false,
    ),
    1 => 
    array (
      'path' => '/var/snap/nextcloud/current/nextcloud/extra-apps',
      'url' => '/extra-apps',
      'writable' => true,
    ),
  ),
  'supportedDatabases' => 
  array (
    0 => 'mysql',
  ),
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'memcache.local' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => '/tmp/sockets/redis.sock',
    'port' => 0,
  ),
  'log_type' => 'file',
  'default_phone_region' => 'US',
  'logfile' => '/var/snap/nextcloud/current/logs/nextcloud.log',
  'logfilemode' => 416,
  'instanceid' => '***',
  'passwordsalt' => '***',
  'secret' => '***',
  'trusted_domains' => 
  array (
    0 => '***',
  ),
  'datadirectory' => '/var/snap/nextcloud/common/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '26.0.3.2',
  'overwrite.cli.url' => '***',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost:/tmp/sockets/mysql.sock',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'nextcloud',
  'dbpassword' => '***',
  'updater.release.channel' => 'daily',
  'installed' => true,
  'maintenance' => false,
  'app_install_overwrite' => 
  array (
    0 => 'documentserver_community',
    1 => 'music',
    2 => 'riotchat',
    3 => 'richdocumentscode',
  ),
  'mail_from_address' => 'nextcloud',
  'mail_smtpmode' => 'smtp',
  'mail_sendmailmode' => 'smtp',
  'mail_domain' => '***',
  'mail_smtphost' => '***',
  'mail_smtpport' => '587',
  'mail_smtpauth' => 1,
  'mail_smtpname' => '***',
  'mail_smtppassword' => '***',
  'theme' => '',
  'loglevel' => 0,
  'ldapProviderFactory' => 'OCA\\User_LDAP\\LDAPProviderFactory',
);
1 Like
2

UPDATE: I went into mysql and got this

mysql> SELECT * FROM oc_systemtag_object_mapping;
Empty set (0.00 sec)
3

In your Nextcloud instance in core/Command/Db directory, open the file AddMissingIndices.php and edit lines 476 and 477 respectively in the if condition:
in both lines the table name includes oc prefix, which may not be there in your instance: oc_systemtag_object_mapping, change to systemtag_object_mapping
Save and re-run the occ db:add-missing-indices command and you should be fine.

4

Ahh, there is a hot-fix already for it here if you want to grab it directly from Github: