Still missing primary keys after using the occ command

ℹ️ Support
1

Nextcloud version: 20.0.2 - 20.0.2.2
Operating system: Linux 4.19.0-12-amd64 #1 SMP Debian 4.19.152-1 (2020-10-18) x86_64
Webserver: nginx/1.14.2 (fpm-fcgi)
Database: mysql 10.5.8
PHP version: 7.4.13

Modules loaded 
Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, standard, sodium, cgi-fcgi, mysqlnd, PDO, xml, bcmath, calendar, ctype, curl, dom, mbstring, FFI, fileinfo, ftp, gd, gettext, gmp, iconv, igbinary, imagick, imap, intl, json, exif, mysqli, pdo_mysql, Phar, posix, readline, redis, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tidy, tokenizer, xmlreader, xmlwriter, xsl, zip, Zend OPcache
apps 
 - accessibility: 1.6.0
 - activity: 2.13.3
 - admin_audit: 1.10.0
 - apporder: 0.11.0
 - calendar: 2.1.2
 - circles: 0.20.6
 - cloud_federation_api: 1.3.0
 - comments: 1.10.0
 - contacts: 3.4.2
 - contactsinteraction: 1.1.0
 - dashboard: 7.0.0
 - dav: 1.16.1
 - deck: 1.2.2
 - event_update_notification: 1.1.0
 - federatedfilesharing: 1.10.1
 - federation: 1.10.1
 - files: 1.15.0
 - files_downloadactivity: 1.9.0
 - files_linkeditor: 1.1.2
 - files_pdfviewer: 2.0.1
 - files_rightclick: 0.17.0
 - files_sharing: 1.12.0
 - files_trashbin: 1.10.1
 - files_versions: 1.13.0
 - files_videoplayer: 1.9.0
 - firstrunwizard: 2.9.0
 - forms: 2.0.4
 - groupfolders: 8.2.0
 - guests: 1.6.1
 - imageconverter: 1.2.1
 - integration_gitlab: 0.0.8
 - integration_mastodon: 0.0.10
 - integration_zammad: 0.0.14
 - issuetemplate: 0.7.0
 - logreader: 2.5.0
 - lookup_server_connector: 1.8.0
 - mail: 1.7.1
 - maps: 0.1.8
 - metadata: 0.12.0
 - nextcloud_announcements: 1.9.0
 - notifications: 2.8.0
 - oauth2: 1.8.0
 - onlyoffice: 6.1.0
 - password_policy: 1.10.1
 - photos: 1.2.0
 - polls: 1.6.2
 - previewgenerator: 3.0.1
 - privacy: 1.4.0
 - provisioning_api: 1.10.0
 - quota_warning: 1.9.1
 - ransomware_protection: 1.8.0
 - serverinfo: 1.10.0
 - settings: 1.2.0
 - sharebymail: 1.10.0
 - side_menu: 1.20.1
 - socialsharing_email: 2.1.0
 - spreed: 10.0.3
 - support: 1.3.0
 - survey_client: 1.8.0
 - systemtags: 1.10.0
 - talk_matterbridge: 1.20.0
 - tasks: 0.13.6
 - theming: 1.11.0
 - twofactor_admin: 3.0.0
 - twofactor_backupcodes: 1.9.0
 - twofactor_nextcloud_notification: 3.0.0
 - twofactor_totp: 5.0.0
 - twofactor_u2f: 6.0.0
 - updatenotification: 1.10.0
 - user_status: 1.0.1
 - viewer: 1.4.0
 - workflow_script: 1.5.0
 - workflowengine: 2.2.0
Disabled:
 - encryption
 - files_external
 - recommendations
 - text
 - user_ldap
 - weather_status

Hello,

I get the message in the Overview since the update to v20:

The database is missing some primary keys. Due to the fact that adding primary keys on big tables could take some time they were not added automatically. By running "occ db:add-missing-primary-keys" those missing primary keys could be added manually while the instance keeps running.

* Missing primary key on table "oc_federated_reshares".
* Missing primary key on table "oc_systemtag_object_mapping".
* Missing primary key on table "oc_comments_read_markers".
* Missing primary key on table "oc_collres_resources".
* Missing primary key on table "oc_collres_accesscache".
* Missing primary key on table "oc_filecache_extended".

When I execute “occ db:add-missing-primary-keys” but the message appears:
All tables already up to date!

What else can I do?

2

Have you double-checked if the primary keys are really missing? Use the following SQL command to check the status of the listed tables. Please list the output of the commands here:

mysql -D <database-name> -e "DESCRIBE oc_federated_reshares;"
...
3

Hi,

thank you for the quick answer, here the output:

DESCRIBE oc_federated_reshares;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| share_id  | int(11) | NO   | PRI | NULL    |       |
| remote_id | int(11) | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
2 rows in set (0.001 sec)

DESCRIBE oc_systemtag_object_mapping;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| objectid    | varchar(64)         | NO   | PRI |         |       |
| objecttype  | varchar(64)         | NO   | PRI |         |       |
| systemtagid | bigint(20) unsigned | NO   | PRI | 0       |       |
+-------------+---------------------+------+-----+---------+-------+
3 rows in set (0.000 sec)

DESCRIBE oc_comments_read_markers;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| user_id         | varchar(64) | NO   | PRI |         |       |
| marker_datetime | datetime    | YES  |     | NULL    |       |
| object_type     | varchar(64) | NO   | PRI |         |       |
| object_id       | varchar(64) | NO   | PRI |         |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.000 sec)

DESCRIBE oc_collres_resources;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| collection_id | bigint(20)  | NO   | PRI | NULL    |       |
| resource_type | varchar(64) | NO   | PRI | NULL    |       |
| resource_id   | varchar(64) | NO   | PRI | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.000 sec)

DESCRIBE oc_collres_accesscache;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| user_id       | varchar(64) | NO   | MUL | NULL    |       |
| collection_id | bigint(20)  | YES  |     | 0       |       |
| resource_type | varchar(64) | YES  |     |         |       |
| resource_id   | varchar(64) | YES  |     |         |       |
| access        | tinyint(1)  | YES  |     | 0       |       |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.000 sec)

DESCRIBE oc_filecache_extended;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| fileid        | bigint(20) unsigned | NO   | PRI | NULL    |       |
| metadata_etag | varchar(40)         | YES  |     | NULL    |       |
| creation_time | bigint(20)          | NO   | MUL | 0       |       |
| upload_time   | bigint(20)          | NO   | MUL | 0       |       |
+---------------+---------------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
4

I The only table where the primary keys are missing seems to be the following one.

# mysql -D nextcloud -e "DESCRIBE oc_collres_accesscache;"
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| user_id       | varchar(64) | NO   | PRI | NULL    |       |
| collection_id | bigint(20)  | NO   | PRI | 0       |       |
| resource_type | varchar(64) | NO   | PRI |         |       |
| resource_id   | varchar(64) | NO   | PRI |         |       |
| access        | tinyint(1)  | YES  |     | 0       |       |
+---------------+-------------+------+-----+---------+-------+
1 Like
5

now my previous answer should be better formatted.
Should all rows have a primary key?

6

I would start adding only the missing keys to the mentioned table.

7

sorry but how exactly do I do that?

i have not worked with sql for a long time, is this right?
ALTER TABLE oc_collres_accesscache ADD PRIMARY KEY (user_id, collection_id, resource_type, resource_id);

//Edit
ok now the first table is corrected, how do I find out which rows in the other tables need PRIMARY KEY?

8

On a first glance the other tables seem to be ok for me. Are you still getting the “missing primary key” message displayed?

9

yes for these 5 tables:

    * Missing primary key on table "oc_federated_reshares".
    * Missing primary key on table "oc_systemtag_object_mapping".
    * Missing primary key on table "oc_comments_read_markers".
    * Missing primary key on table "oc_collres_resources".
    * Missing primary key on table "oc_filecache_extended".
10 
# mysql -D nextcloud -e "DESCRIBE oc_federated_reshares"
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| share_id  | int(11) | NO   | PRI | NULL    |       |
| remote_id | int(11) | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

# mysql -D nextcloud -e "DESCRIBE oc_systemtag_object_mapping"
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| objectid    | varchar(64)         | NO   | PRI |         |       |
| objecttype  | varchar(64)         | NO   | PRI |         |       |
| systemtagid | bigint(20) unsigned | NO   | PRI | 0       |       |
+-------------+---------------------+------+-----+---------+-------+

# mysql -D nextcloud -e "DESCRIBE oc_comments_read_markers"
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| user_id         | varchar(64) | NO   | PRI |         |       |
| marker_datetime | datetime    | YES  |     | NULL    |       |
| object_type     | varchar(64) | NO   | PRI |         |       |
| object_id       | varchar(64) | NO   | PRI |         |       |
+-----------------+-------------+------+-----+---------+-------+

# mysql -D nextcloud -e "DESCRIBE oc_collres_resources"
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| collection_id | bigint(20)  | NO   | PRI | NULL    |       |
| resource_type | varchar(64) | NO   | PRI | NULL    |       |
| resource_id   | varchar(64) | NO   | PRI | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

# mysql -D nextcloud -e "DESCRIBE oc_filecache_extended"
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| fileid        | bigint(20) unsigned | NO   | PRI | NULL    |       |
| metadata_etag | varchar(40)         | YES  |     | NULL    |       |
| creation_time | bigint(20)          | NO   | MUL | 0       |       |
| upload_time   | bigint(20)          | NO   | MUL | 0       |       |
+---------------+---------------------+------+-----+---------+-------+
1 Like
11

I have no idea what the problem was, but I just reset PRIMARY KEY’s once and now the bug is gone

ALTER TABLE oc_federated_reshares ADD PRIMARY KEY (share_id);
ALTER TABLE oc_systemtag_object_mapping ADD PRIMARY KEY (objectid, objecttype, systemtagid);
ALTER TABLE oc_comments_read_markers ADD PRIMARY KEY (user_id, object_type, object_id);
ALTER TABLE oc_collres_resources ADD PRIMARY KEY (collection_id, resource_type, resource_id);
ALTER TABLE oc_collres_accesscache ADD PRIMARY KEY (user_id, collection_id, resource_type, resource_id);
ALTER TABLE oc_filecache_extended ADD PRIMARY KEY (fileid);

Thank you verry much @j-ed

1 Like